MOD function problem Excel 2013

[Scott R]

Any idea why =MOD(-40,60) would produce -40?

On some worksheets, I get the correct answer of 20. On others, I get -40.

A fresh Excel session gets me the correct answer so I suspect it's this workbook.
If I open a new worksheet in this workbook, I get the correct answer.

No decimals are involved.
It's a large (25 MB) workbook.
I don't see any CIRC errors.
Calculation set to Automatic.

Thanks!

 

[Joe4]

Any idea why =MOD(-40,60) would produce -40?

On some worksheets, I get the correct answer of 20. On others, I get -40.

That makes no sense, unless you have a corrupt workbook or something else going on.
Is that the exact formula you have, or are you pulling values from other cells/formulas?
Did you have any Custom Formats applied on these cells?

 

[Scott R]

That makes no sense, unless you have a corrupt workbook or something else going on.
Is that the exact formula you have, or are you pulling values from other cells/formulas?
Did you have any Custom Formats applied on these cells?

Nope, that's the formula. Pretty simple. Just can't figure out what else could be going on.
I can use the long version of the MOD function, I guess, for this particular workbook.

 

[Joe4]

I cannot reproduce the problem, though I do not have Excel 2013.

What happens if you re-enter the incorrect value?
That is, go the cell, hit F2 to bring into edit mode, and hit Enter?

 

[Scott R]

I still get incorrect value when re-entering or when picking a blank cell and entering =MOD(-40,60).
I entered this formula into various sheets in the workbook. Some worksheets, the answer is correctly 20, some worksheets the answer is -40. It's acting like there's a circular ref but I'm not getting that message in the status bar on any sheet.

 

[Joe4]

So it is only on certain sheets in one specific workbook?

And you verified the following?
- No VBA in workbook
- No custom format on cells

Where did this workbook come from originally?
Was it created in an earlier version of Excel?

Its odd that you cannot reproduce that behavior in any new workbook.
That tells me that the issue is probably with that particular workbook only, and not Excel in general.

 

[Jonmo1]

File - Options - Advanced
Scroll all the way down
UNcheck "Transition formula evaluation"

Don't even ask me 'Why' it does that, I have no clue.

 

[joeu2004]

File - Options - Advanced
Scroll all the way down
UNcheck "Transition formula evaluation"
Don't even ask me 'Why' it does that, I have no clue.

Great find!

The simple answer is: that is the way that Lotus 123 MOD must have worked.

Why did it work that way?

A look at how -40 Mod 60 works in VBA and the VBA Mod Operator help page might give you a clue.

The help page says: "Used to divide two numbers and return only the remainder".

Of course, if we do an integer divide of -40 \ 60, the result is zero. So the "remainder" of the division truly is -40.

Presumably, that was also the interpretation of the MOD function in Lotus 123.

Many decades ago when this debate was raging, most computer scientists wanted to define MOD(n,d) as n-d*INT(n/d).

As you observed, there is a difference between the arithmetic "remainder" and n-d*INT(n/d) when the signs of n and d differ.

 

[Joe4]

Of course, if we do an integer divide of -40 \ 60, the result is zero.

Actually, if you divide -40 by 60, the integer portion of that is -1, not 0.
(Because 0 is actually greater than -40/60).
Negative number division often trips people up. Sometimes, it helps to do a visual representation and use a number line.

 

[joeu2004]

Actually, if you divide -40 by 60, the integer portion of that is -1, not 0.

First, I wrote -40 \ 60 (back-slash operator). That is VBA syntax. It is an undisputable fact that in VBA, -40 \ 60 returns zero.

Second, I think you are confusing Excel INT(-40/60) with the quotient of the arithmetic operation of -40 divided by 60, which can be written QUOTIENT(-40,60).

INT(-40/60) returns -1 because INT is defined to return the largest integer less than or equal to -40/60. That is -1 because it meets the criteria.

QUOTIENT(-40,60) returns zero (really -0) because there are zero units of -60 in -40. That is elementary school arithmetic.

Also note that there are two ways to truncate to an integer value: the way that INT is defined; and truncating toward zero, which TRUNC(-40/60) does.

I am guessing that the Lotus 123 MOD returns -40 because it returns the remainder of the airthmetic operation (QUOTIENT and TRUNC).