MOD Function and Negative numbers

[Jason Froese]

I have run across the problem that MOD rounds down
negative numbers to get the results due to the use of the
INT function
E.G. MOD ( -9,5 ) returns 1 ( -2 remainder 1 )

i was just wondering why the function used to calculate
mod in excel
"MOD (n, d) - n - d * INT ( n/d )"

doesn't just use TRUNC instead of INT
"MOD (n, d) - n - d * TRUNC ( n/d )"

I was taught in elementary school that 9 / 5 = 1.8 (1
remainder 4 )
and 9 / -5 = -1.8 ( -1 remainder 4 (9 - (-5*-1) )
not -2 remainder 1

 

[Harlan Grove]

I have run across the problem that MOD rounds down
negative numbers to get the results due to the use of the
INT function
E.G. MOD ( -9,5 ) returns 1 ( -2 remainder 1 )

i was just wondering why the function used to calculate
mod in excel
"MOD (n, d) - n - d * INT ( n/d )"

doesn't just use TRUNC instead of INT
"MOD (n, d) - n - d * TRUNC ( n/d )"

Just because it doesn't. Excel is inconsistent with both Lotus 123 and Quattro
Pro (in both, FWLIW, @MOD(9,5) and @MOD(9,-5) both return 4 and @MOD(-9,5) and
@MOD(-9,-5) both return -4, which is consistent with standard C's fmod(3)
function). Excel's MOD function gives the same answers as the modulus operators
or functions in APL, S and MatLab. None of these give IEEE remainders.

I was taught in elementary school that 9 / 5 = 1.8 (1 remainder 4 )
and 9 / -5 = -1.8 ( -1 remainder 4 (9 - (-5*-1) ) not -2 remainder 1

And -9 / 5 = -1.8 also, but it'd be -1 remainder -4 (-9 - (-1 * 5)). This is the
big problem with mixed sign quotients and remainders: in strict mathematical
terms they're not well defined. So specifying how to deal with them consistently
requires adopting a convention, but conventions are optional and all of the
alternatives are equally valid.

Given the variation in ways mixed sign moduli are handled in different
quantitative analysis packages, it's clear to me that the convention you cite is
no more binding than on which side of the road to drive.

If you want this functionality, then don't use MOD. Use your formula involving
TRUNC. You'll get an added benefit of finessing a quirk in Excel's MOD that
returns errors when the absolute value of the quotient is 2^27 or greater, e.g.,
2^30 and 3 are both valid long integers, but MOD(2^30,3) returns #NUM!.
Fortunately, 2^30-3*TRUNC(2^30/3) returns 1.

 

[Dana DeLouis]

Just for discussion...Excel's vba is a little different:

Sub Demo()
Debug.Print [MOD(-9,5)]
Debug.Print -9 Mod 5
End Sub

returns
1
-4

I'm not an expert, but the documentation in the program Mathematica seems to
suggest that the sign of the answer is "usually" taken as the sign of the
second number. If this is the case, it would appear that vba answer has the
wrong sign. It should be positive (...apparently)

The program gives +1 as the answer, which is the same as Excel's worksheet
Mod function: =MOD(-9,5)

Mod[-9, 5]
1

The program is "usually" pretty good at following standard mathematica
conventions. However, I could not find any documentation on how Mod should
really work.
However, another discussion in it's help talked about this...
"...For any integers a and b, it is always true that b*Quotient[a, b] +
Mod[a, b] is equal to a."
Here, Quotient is similar to the Floor function, which acts similar to
Excel's Int function. (Rounding down negative numbers)
For this example, it suggest that -2 is correct, and 5*-2 = -10. Then add
the correct answer of +1 to arrive at 'a', which is -9.
It would "appear" to me that the worksheet function is more correct then the
vba answer. But....

 

[Harlan Grove]

However, another discussion in it's help talked about this...
"...For any integers a and b, it is always true that b*Quotient[a, b] +
Mod[a, b] is equal to a."
Here, Quotient is similar to the Floor function, which acts similar to
Excel's Int function. (Rounding down negative numbers)
For this example, it suggest that -2 is correct, and 5*-2 = -10. Then add
the correct answer of +1 to arrive at 'a', which is -9.
It would "appear" to me that the worksheet function is more correct then the
vba answer. But....

....

I should have described IEEE remainders since we're going off on a tangent.
See http://hpcf.nersc.gov/vendor_docs/ibm/libs/basetrf1/drem.htm . So

drem(9, 5) = -1
drem(-9, 5) = 1
drem(9, -5) = -1
drem(-9, -5) = 1