MOD with negative numbers

[YasserKhalil]

Hello everybody

I tried this formula

Please Login or Register  to view this content.

The result was 2 not -1

How did it be done?

[daddylonglegs]

This type of setup would give you -1 but still give you expected results for positive numbers

=SIGN(-4)*MOD(ABS(-4),3)

....or better using zbor's advice....

=MOD(-4,3*SIGN(-4))

[zbor]

The sign is the same as divisor (+).
Result is remainder of -4/3 but in other direction (towards next multiplier of 3).

To get negative divisor should be negative
=MOD(-4,-3) will return -1.

[YasserKhalil]

I dpn't want the result to be -1 .. I just want to understand how the process done in maths?

[martindwilson]

try a maths forum?
http://mathforum.org/library/drmath/view/52343.html

[romperstomper]

Excel's Mod function:
MOD(n,d)=n-d*INT(n/d)

Which here evaluates to (remembering that INT always rounds down) :
-4-3*-2
which equals - 4--6 =2

[YasserKhalil]

Thanks a lot Mr. romperstomper
you are very helpful

[YasserKhalil]

Excel's Mod function:
MOD(n,d)=n-d*INT(n/d)

Which here evaluates to (remembering that INT always rounds down) :
-4-3*-2
which equals - 4--6 =2

But -4/3= -1.333333333
So INT this number should be -1 !!!

[mikerickson]

Now the twist.
As noted, the worksheet function MOD(-4,3) returns 2
But, in VBA,

Please Login or Register  to view this content.

Given any integer X and a positive B, if one want the positive version of X modulo B, one must use an expression like

Please Login or Register  to view this content.

Another difference is that the worksheet function MOD will return non-integer values
=MOD(4.6, 3) returns 1.6, while VBA rounds the result to an integer.

Please Login or Register  to view this content.

[romperstomper]

But -4/3= -1.333333333
So INT this number should be -1 !!!

Nope - as I said:

remembering that INT always rounds down

For negative numbers, rounding down is not the same as rounding towards 0, so -4/3 rounds to -2 not -1.

[joeu2004]

But -4/3= -1.333333333
So INT this number should be -1 !!!

Mathematically, no. INT is the "largest integer less than or equal to" its argument. Note that -1 is greater than -1.33333333333333. For example, =(-1 > -1.33333333333333) returns TRUE.

[zbor]

Less than -1.333 is -2

Edit: Oh, yes, sorry. You point that out.

[YasserKhalil]

thanks for these valuable information.. you are awesome guys!

[YasserKhalil]

I'm confused now with mod as a function in a worksheet and mod in vba
I tested

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

and the results are different why?

Another point the result of the following formula results in -2 (the result is negative)

Please Login or Register  to view this content.

[mikerickson]

I'm confused now with mod as a function in a worksheet and mod in vba ... the results are different why?

In a sense, the results are the same. in that (-2 mod 3) = (1 mod 3).
By that I mean that (restricted to integers) X mod 3 has only 3 possible results. Each of these results is equivalent to either 0, 1 or 2.
(Yes, I know that by this "definition" X mod N = X is true). For doing arithmetic, they are both consistent.

Another point the result of the following formula results in -2 (the result is negative)]=mod(4,-3)

This is true, since 4 = (-2*-3) + (-2). It is also the result from romper stomper's formula.

[YasserKhalil]

Do you mean that :
n-d*INT(n/d)
is equivalent to the the function MOD(n,d)?

[joeu2004]

I'm confused now with mod as a function in a worksheet and mod in vba[.] I tested
=Mod(-4,3)
and
Msgbox -4 Mod 3
and the results are different why?

Another point the result of the following formula results in -2 (the result is negative)
=mod(4,-3)

Because in Excel, =MOD(a,b) is calculated by a - b*INT(a/b).

But in VBA, a Mod b is calculated by a - b*(a\b).

Note the backslash division operator, not the forward-slash division operator.

In VBA, the backslash division operator returns the integer part of the division.

In contrast, in both VBA and Excel, forward-slash division returns the real-number result (e.g. 1 + 1/3 for 4/3), and INT(a/b) returns the "largest integer less than or equal to" the parameter.

That is not the same as the "integer part" of the division. In effect, the "integer part" is rounded toward zero, whereas the "largest integer less than or equal to" is rounded to the left (toward zero for positive values; away from zero for negative values).

PS.... When comparing expressions like MOD(-4,3) and -4 Mod 3, it is important to explicitly parenthesize the latter like (-4) Mod 3 to ensure that you get equivalent interpretations. In this case, that is exactly the way that VBA interprets the unparenthesize expression. But for example, compare =-4^2 in Excel and -4^2 VBA.

[EDIT] Excel interprets that as (-4)^2, whereas VBA interprets it as -(4^2).

[joeu2004]

PS....

Because in Excel, =MOD(a,b) is calculated by a - b*INT(a/b).
But in VBA, a Mod b is calculated by a - b*(a\b).

Note that in VBA, a - b*INT(a/b) returns the same thing as MOD(a,b) in Excel,
which again is different from a - b*(a\b).

[EDIT] Beating a dead horse ;-) .... Also note that in Excel, a - b*TRUNC(a/b) returns the same thing as a Mod b in VBA because Excel TRUNC rounds toward zero just like the VBA backlash division operator.