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.