mathforum.org/library/drmath/view/52343.html
Math Mod Function and Negative Numbers Date: 04/28/2000 at 11:17:09 From: Anne Subject: Using the mod() function with negative numbers I work in IT - Technical Support. I am trying to sort out a problem for a user who is using the MOD() function in Excel 97 as follows: =MOD(-340,60) He thinks it should return a value of 40, but it returns a value of 20. It returns a value of 40 if we do the following: =MOD(340,60) Now I am wondering why the negative sign makes a difference. You can see from my age that it was years ago that I had math, and I was very good at it. But I cannot remember how negative numbers work in division. Anne Date: 04/28/2000 at 12:57:10 From: Doctor Peterson Subject: Re: Using the mod() function with negative numbers Hi, Anne. There are different ways of thinking about remainders when you deal with negative numbers, and he is probably confusing two of them. The mod function is defined as the amount by which a number exceeds the largest integer multiple of the divisor that is not greater than that number. In this case, -340 lies between -360 and -300, so -360 is the greatest multiple LESS than -340; we subtract 60 * -6 = -360 from -340 and get 20: -420 -360 -300 -240 -180 -120 -60 0 60 120 180 240 300 360 --+----+----+----+----+----+----+----+----+----+----+----+----+----+-- | | | | -360| |-340 300| |340 |=| |==| 20 40 Working with a positive number like 340, the multiple we subtract is smaller in absolute value, giving us 40; but with negative numbers, we subtract a number with a LARGER absolute value, so that the mod function returns a positive value. This is not always what people expect, but it is consistent. If you want the remainder, ignoring the sign, you have to take the absolute value before using the mod function.
math/ Date: 05/02/2000 at 04:58:50 From: Norwich CC Network Services Subject: Re: Using the mod() function with negative numbers Thanks very much for sorting this out. I had worked out how it was arriving at the result, but did not know whether it was correct. We had also tried it in Lotus 1-2-3 and the result given was -40, which was what the user expected. Anne Date: 05/02/2000 at 17:16:19 From: Doctor Peterson Subject: Re: Using the mod() function with negative numbers Hi, Anne. It occurs to me that I don't quite want to leave you trusting Excel too much. A comment I had considered making before was that a program may often use its own definitions for functions like this, and apparently Lotus proves my point that "mod" can be taken in different ways. You should always go by the manual, not by what a mathematician says. here's a copy of an answer I gave to someone who asked about its handling of -a^b: =========================================================== The proper rule is that negation has the same precedence as multiplication and division.
asp It doesn't claim that their rule agrees with the mathematical world, only that they make their own standards, and don't have to agree with anyone: This behavior is by design of Microsoft Excel. Microsoft Excel uses an order of calculation to evaluate operators in formulas. The order of evaluation of operators dictates that a minus sign used as a negation operator (such as -1) is evaluated before all other operators. Because of this order, the formula =-1^2 represents the value -1 squared, and returns the value 1, a positive value. Note that this has been the standard method for evaluating formulas since the first version of Microsoft Excel. NOTE: This order of operation is different from the order of operation in Lotus 1-2-3. What I suspect is that the programmers were accustomed to the C language, in which unary operators such as negation have higher precedence than any binary operator; there is no exponent operator in C When they added the exponent operator, they may simply have forgotten that it should have higher precedence, or they may have found it was easier to program this way. Once the rule became established, they couldn't change it and make customer's programs fail. There are many other instances where calculators or software make their own rules; sometimes this is because of limitations of their interface, sometimes because of a misguided desire to "improve" the rules. In any case, we can't use any piece of software as our guide to mathematical practice, and students should be taught not to confuse the rules of a particular program with those of math. We wouldn't want Microsoft to be making these decisions for us anyway, would we?
math/ Date: 05/03/2000 at 08:53:33 From: Norwich CC Network Services Subject: Re: using the mod() function with negative numbers Thanks for that! Anne Date: 07/05/2001 at 13:58:23 From: A H Banen Subject: Mod Function and Negative Numbers Dear Dr. Peterson, Concerning the topic "Mod Function and Negative Numbers," I found it strange you did not refer to the simple rule concerning integer division and modulo calculation: Given the integer numbers A and B (where B cannot be equal to zero), the following holds: A = ( A DIV B ) * B + A MOD B eg for A = 340 and B = 60 A = ( 340 DIV 60 ) * 60 + ( 340 MOD 60 ) => A = ( 5 ) * 60 + 40 => A = 340 Also, when A = -340 and B = 60 A = ( -340 DIV 60 ) * 60 + ( -340 MOD 60 ) A = ( -5 ) * 60 + ( -40 ) => A = -340 From this can be derived that MOD(-340, 60) should have been -40 Sincerely, Andre Banen Date: 07/05/2001 at 16:17:04 From: Doctor Peterson Subject: Re: Mod Function and Negative Numbers Hi, Andre. You're right that this relation is relevant to the question; But it only proves what MOD should do _if_ we know how DIV is defined; that is, it is a statement of consistency between the mod function and the direction of integer truncation. You're _assuming_ truncation toward zero, so that -340/60 gives -5. But in Excel, we see that the relation looks like this: A = ( -340 DIV 60 ) * 60 + ( -340 MOD 60 ) -340 = -6 * 60 + 20 This is perfectly consistent if their integer division truncates toward -infinity rather than toward zero, so that -340/60 is taken to be -6. And that's just what I said, using words rather than the formula: The mod function is defined as the amount by which a number exceeds the largest integer multiple of the divisor that is not greater than that number. In this case, -340 lies between -360 and -300, so -360 is the greatest multiple LESS than -340; Here's a more complete answer: Computer languages and libraries are notoriously inconsistent, or at least unmathematical, in their implementation of "mod" for negative numbers. There are several ways it can be interpreted in such cases, and the choice generally made is not what a mathematician would probably have made. The issue is what range of values the function should return. Mathematically, we define "modulo" not as a function, but as a relation: any two numbers a and b are congruent modulo m if (a - b) is a multiple of m If we want to make a function of this, we have to choose which number b, of all those that are congruent to a, should be returned.
html The value of b, where a=b (mod m), taken to be nonnegative and smaller than m Unfortunately, this statement about FORTRAN, and implicitly about the many languages that have inherited their mathematical libraries from FORTRAN, including C++, is not quite true where negative numbers are concerned. The problem is that people tend to think of modulus as the same as remainder, and they expect the remainder of, say, -5 divided by 3 to be the same as the remainder of 5 divided by 3, namely 2, but negated, giving -2. We naturally tend to remove the sign, do the work, and put the sign back on, because that's how we divide. In other words, we expect to truncate toward zero for both positive and negative numbers, and have the remainder be what's left "on the outside," away from zero. More particularly, computers at least since the origin of FORTRAN have done integer division by "truncating toward zero," so that 5/2 = 2 and -5/2 = -2, and they keep their definition of "mod" or "%" consistent with this by requiring that (a/b)*b + a%b = a so that "%" is really defined as the remainder of integer division as defined in the language. Because FORTRAN defined division and...
|
http://csua.com/feed/