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

Hi Dana,

Thanks for the feedback but I don't get the same result.

Pasted your code in VBA and the result is:

66666666666600349344662390,6480

It is a lot shorter and different as from 239064.

I Did it on a Dell Latitude E6530.

Intel i5-3320M 2.6GHz

4 GB ram, 32 bit

Win7 Enterprise SP1

Excel 2010

Do you see any reason for this?

Answer 2

Hi Dana,

Thanks for the feedback but I don't get the same result.

Pasted your code in VBA and the result is:

66666666666600349344662390,6480

It is a lot shorter and different as from 239064.

I Did it on a Dell Latitude E6530.

Intel i5-3320M 2.6GHz

4 GB ram, 32 bit

Win7 Enterprise SP1

Excel 2010

Do you see any reason for this?

Hi.  I did the following:

Sub TestIt()

    Debug.Print BinToReal("1")

    Debug.Print BinToReal("11")

    Debug.Print BinToReal("111")

    Debug.Print BinToReal("1010101010101010101010101010101010101001111100")

End Sub

Returns:

5

75

875

6666666666660034934466239064931869506835937500

Your answer is 31 characters:

?LEN("66666666666600349344662390,6480")

 31

Which tells me your code is working at the standard double precision of VBA.

I'm not sure which one of your lines is triggering the conversion back to Double precision.

I'll take a look at it if you are interested.

Answer 3

Hi Dana,

Which tells me your code is working at the standard double precision of VBA.

I'm not sure which one of your lines is triggering the conversion back to Double precision.

I'll take a look at it if you are interested.  

I did some further testing and debugging and could fix the issue.

"AL" remained 0, so I had only Left$(CStr(AH) as result of the function. This because of

If Right(H, 2) = ".5" Then was never triggered.

Changing it to ",5" did it. So, I get now also

6666666666660034934466239064931869506835937500

as result.

Thanks again.

Answer 4

Second, in that case, I believe Koeiegeloei's formula is indeed more reliable, when corrected.  Assuming A1 and A2 are already rounded to the required precision, use:

IF(ROUND(MOD(A1,A2),2) = A2, 0, ROUND(MOD(A1,A2),2))

This is needed because even though MOD(A1,A2) - A2 < 0 is always true [1], ROUND(MOD(A1,A2),2) = A2 might become true due to rounding.

Hi, thanks for the valuable comments! Sorry, that I am not a quick responder, but I still got something to share on this topic.

I understand that joeu2004's formula is better in many cases. However, in my special case it does not always give the results I desire. I will show this again with a simplified example. Below the results with the formula I proposed (and which gives wrong results in many other cases).

Formula	Result
0	0
=IF(MOD(A2+1/3,1)=1,0,MOD(A2+1/3,1))	0.333333333
=IF(MOD(A3+1/3,1)=1,0,MOD(A3+1/3,1))	0.666666667
=IF(MOD(A4+1/3,1)=1,0,MOD(A4+1/3,1))	0

Using joeu2004's modification the result is:

Formula	Result
0	0
=IF(ROUND(MOD(A1+1/3,1),12)=1,0,ROUND(MOD(A1+1/3,1),12))	0.333333
=IF(ROUND(MOD(A2+1/3,1),12)=1,0,ROUND(MOD(A2+1/3,1),12))	0.666667
=IF(ROUND(MOD(A3+1/3,1),12)=1,0,ROUND(MOD(A3+1/3,1),12))	1

Of course I understand, that 1 can mean either 1 or 0.99999 (or whatever). In my calculations it is important to see when I enter a new phase, which is the case when Result = 0. So I want to discern between numbers that are exactly 1, or just a bit smaller than 1. Therefore I am looking for a formula that will give a zero in case a zero should be the true mathematical answer. This would be easy when it always happens in the same cell, but unfortunately I have a matrix which is filled with a more complex equation in the way that is presented in the tables above.

So my point is that I don't want to see a 1 or a 0.999999 or similar, when it should mathematically be a zero. I am now starting to understand that this is a very ambitious goal!

Nevertheless, I have the impression that my (actual) equation will never have the form of 1000.4-1000. So in this case my proposed formula seems to suit my needs best.

Please let me know if you are sure that I shouldn't use my proposed formula. I am not a mathematician, so please give an example in that case! Thanks in advance!

Answer 5

By the way, I now fill my matrix in a better way by calculating each phase starting from the origin instead of calculating it from the previous cell. I think I avoid a lot of errors by doing so...