Problem with the MOD function in excel?

Question

=MOD((15*(1,4-1));6)

returns 6,0 and

=MOD((15*(0,4));6)

returns 0,0

I would like the first MOD function to return 0 as well. Is there a reason why Excel behaves like this? Do you experience the same problem in excel 2010?

I could replace the function with a INT-test instead ( =(15*(1,4-1))-6*INT((15*(1,4-1))/6) ), but it would be nicer if MOD would work.

Office 2010 - I did not experience the same problem in office 2003

Thanks

Answer 1

=INT((6-2*2^-50)/6) returns 1 (exactly) in Excel 2010, and =6-2*2^-50 returns 6 (exactly).

Answer 2

In 07

 INT((6-2*2^-50)/6)

Returned a value of 1

Answer 3

HansV wrote:

=INT((6-2*2^-50)/6) returns 1 (exactly) in Excel 2010

Brad wrote:

> In 07 INT((6-2*2^-50)/6) Returned a value of 1

Thanks.  Too bad!

HansV wrote:

> =6-2*2^-50 returns 6 (exactly).

Are you sure?!  I would find that very surprising.

No doubt that the result appears to be 6 "exactly" even if formatted to display 15 significant digits.

But if A1 is =6-2*2^-50, what does =A1-6-0 in A2 return formatted as Scientific?

That is the most reliable way to "see" that it is not truly "6 exactly".

If A2 is truly exactly zero in that case(0.00E+00 !), what does A2 return if A1 is =6-2*2^-50-0?

Sigh, one of these days I need to get my own XL2010 so I don't have to burden people with these experiments.  Unfortunately, I don't believe I can get XL2007 anymore.

Answer 4

With =A1-6-0, we're back at -1.77636E-15. If must say that I find this behavior very confusing. What is the difference between =A1-6 and =A1-6-0?

Answer 5

HanV wrote:

With =A1-6-0, we're back at -1.77636E-15. If must say that I find this behavior very confusing. What is the difference between =A1-6 and =A1-6-0?

If you find it confusing, I'm sure my explanation did little to alleviate Jennie's confusion.  My apologies for getting too mired down in details.  To answer your question....

Refer to the poor description of the dubious heuristic under the misleading title "Example When a Value Reaches Zero" in http://support.microsoft.com/kb/78113 [3].

In short, sometimes Excel forces an arithmetic result to be exactly zero if it considers the result to be "close enough" to zero [1].

The operative word is "sometimes".  KB 78113 states that it is when "an addition or subtraction operation result in a value at or very close to zero".  But that is imprecise.

First, it applies to the last arithmetic operation of a formula, not to subexpressions [2].

Second, it applies when the last operation is subtraction of two operands with the same sign or addition of two operands with opposite signs.

Third, and this is the key:  it applies when the last operation changes the result.

Parentheses count as an "operation" in this context.  So =A1-6 is zero, but =(A1-6) is not zero, just as =A1-6-0 is not zero.  The last operation, right parenthesis or -0, does not change the result.

Don't try to make sense of that.  It does not, IMHO.  It is just the way it is.

Bottom line:  If you want to know the true value of an expression that might be close to zero, always append -0 to it.

---

Endnotes

[1] Let's not get into the definition of "close enough".  To my knowledge, Microsoft never documents the criteria.  I have deduced some of my own; but they are too complicated to explain, and they might be incomplete.

[2] The "close to zero" heuristic also applies to comparisons of the form x=y, which we can think of x-y compared to zero.

Generally, I don't believe this heuristic applies to Excel functions.  But recently, I discovered that SUM has its own heuristic for changing some "close to zero" results to exactly zero.  Too difficult to explain.  Suffice it to say:  IMHO, it is even more fickle than the addition/subtraction rule in KB 78113.

[3] It is a "poor" description because it is incomplete and imprecise.

It is a "dubious" heuristic because it is not applied consistently, leading to many seemingly inexplicable conditions.  For example IF(A1=6,TRUE) is TRUE and =A1-6 is exactly zero, but IF(A1-6=0,TRUE) is FALSE.

It is a "misleading" title because the heuristic is not limited to results that are "close to zero".  For example, if A1 is =2^104+2^52, =A1-2^104 results in exactly zero, but =A1-2^104-0 demonstrates that the result is 2^52, which is obviously not "close to zero".