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

Thanks again!

Answer 2

I wrote:

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].

[....]

[3] [....] 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.

Forgive me for belaboring the subject, but this is another example of the inconsistent nature of the heuristic, IMHO.

If A1 is =1+2*2^-52 and A2 is =1+2^-52, =A1-A2 in A3 is exactly zero.  Note that =A1-A2-0 demonstrates that the true difference is 2^-52 as expected.

However, if A4 is =2*2^-52 and A5 is =2^-52, =A4-A5 in A6 is 2^-52, not exactly zero.

That is, the same 2^-52 result is considered "close to zero" in one case, but not "close to zero" in the other.

(This is what led to my definition of "close enough" that is too complicated to explain.  In a nutshell, it depends on the magnitude of the true result relative to the magnitude of the operands.)

Answer 3

I now understand what is causing the problem, and what I need to do to fix it in my worksheet.

Thank you so much for your replies.

Answer 4

I now understand what is causing the problem, and what I need to do to fix it in my worksheet.

Thank you so much for your replies.

Hello, i've a little question to do. (Excel 2010)

In A1 the value is 40,5500; (degrees,decimillesimal_of_degree)

A2 is =INT(A1) result 40;

A3 is =A1-INT(A1) result 0,5500

A4 is =A3*60 result 33,0000

A5 is =A4-INT(A4) AND THE RESULT IS 1!!!!!!! instead of 0

I must convert degrees in a appreciable way... any answer? Is that a bug?

I read your posts but... I need a routine errorless...

Many thanks

Andrea

Answer 5

Andrea wrote:

Hello, i've a little question to do. (Excel 2010)

In A1 the value is 40,5500; (degrees,decimillesimal_of_degree)

A2 is =INT(A1) result 40;

A3 is =A1-INT(A1) result 0,5500

A4 is =A3*60 result 33,0000

A5 is =A4-INT(A4) AND THE RESULT IS 1!!!!!!! instead of 0

I must convert degrees in a appreciable way... any answer? Is that a bug?

I read your posts but... I need a routine errorless...

The short answer is:  if you want the result to be accurate to 4 decimal places, explicitly round results to 4 decimal places.  For example:

A2:  =INT(A1)

A3:  =ROUND(A1-A2,4)

A4:  =ROUND(A3*60,4)

A5:  =ROUND(A4-INT(A4),4)

If you look at your original formulas formatted with 15 decimal places, you will see the problem:  A3 is not exactly 0.5500.  (Note:  I use period as the decimal separator, whereas you use comma.)

This is because of the way that Excel represents numbers:  64-bit binary floating-point.  Consequently, most non-integers (and integers larger than 2^53) cannot be represented exactly.  The small differences (numerical "errors", but not really defects) often accumulate and magnify with each arithmetic operation.

For overwhelming (and somewhat misstated) details, see http://support.microsoft.com/kb/78113.