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

Koeiegeloei wrote:

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.

It is impossible to tell.  You keep changing your story, so it is difficult for me to know what we are talking about.

Your first response in this thread seemed to be directed to Jennie.  Nearly 3 years after Jennie wrote, "I now understand what is causing the problem, and what I need to do to fix it in my worksheet", you wrote:  "I have another one, which you might like:  =IF(MOD((15*(1,4-1));6)=6;0;MOD((15*(1,4-1));6))".

That is what I have been discussing:  the conceptual errors in that response and your subsequent responses, which I assumed were intended to fine-tune your comments about Jennie's problem.

Now it appears that you have "needs" of your own, and your more recent comments are intended to justify a solution that meets your needs best.

Since you never described your needs, I cannot tell if your simplification of the "more complex" situation is truly representative of your needs.

If whatever you are doing works for you, so be it.  "Don't fix what ain't broke".

If and when it does not work for you, I suggest you start a new discussion about your needs instead of piggybacking a 3-year-old discussion of someone else's different needs by your own admission, especially a discussion that was already resolved adequately, according to the original poster.

If and when you start a new discussion about your needs, be sure to include examples that truly represent the "more complex" situation, not your simplification.  With arithmetic problems of this sort, the devil is in the details.

Even better:  upload an example Excel file to a file-sharing website, and post the URL of the "shared" file.  But I reiterate:  that is for another discussion, not this one.

Answer 2

Hi Joeu2004,

Sorry to upset you...

I thought the topic was about how to deal with the MOD function since it sometimes gives accurate results. I am sorry that I misunderstood that the topic was solely about solving Jenny's specific problem with the MOD function. I have understood it in a broader sense: solving comparable problems with the MOD function, and that is why I started to compare the presented solutions with my own case, showing that what works in one situation, does not work in the other. And so I slowly wandered off the subject...

So again, sorry for wandering off. I hope you don't get demotivated by this, because you're doing a great job in explaining difficult things.

Answer 3

> ..  how to deal with the MOD function since it sometimes gives accurate results

Hi.  Just 2 cents.  Excel's Mod function behaves just like advanced math programs.  It's pretty consistent.

It just sometimes doesn't display the full internal value due to the nature of the spreadsheet environment.

(For example, the example of 2.6-2.5, Excel will show this as exactly 0.1, when in fact most math programs will display the correct value from the math co-processor as a value slightly higher than this.)

But... that's all been discussed.

One of the problems I see with using 1/3 is that you are using an irrational number which compounds the problem of Mod, which is designed to use integer inputs.  Rounding to 12 can been seen by exaggerating the problem with 4 digits.

.6667 + 1/3   = 1.0000333333333333.

With 12, you still have trailing 333's. and will never match 1.000...

One solution for what you are doing is to have a custom function that returns true if 2 values are close in value within a tolerance that you specify.   I like to have a custom vba function, but the general idea would be something like this:

Abs(x-y) < 1E-14

Something like this would return true if the two values are close to each other within the tolerance you would specify.

Here's another example to 'know' about Excel which are not really a problem with Excel, as  Joeu2004 has already mentioned.

=2.6-2.5-0.1=0  returns FALSE. 

=2.6-2.5=0.1   returns TRUE    (similar to example Joeu2004 gave)

=2.6-2.5 < 0.1
=2.6-2.5 > 0.1

Both give FALSE which would indicate that the values really does equal .1

Then...

=DELTA(2.6-2.5,0.1)

returns FALSE (0) indicating that these are in fact NOT equal. 

=GESTEP(0.1, 2.6-2.5)

Since we 'guess these are equal, this should return true, but now returns FALSE because these are NOT Equal.

=GESTEP( 2.6-2.5,0.1)

This actually returns TRUE, which means it thinks 2.6-2.5 is >= .1

Again.  Excel is consistent with advanced math programs.  With known precision out to 2-4 digits (think financial numbers)  One would scale the problem to integers, and use VBA which has integer(long) variables).   For irrational numbers,  (1/3,  4/7...etc)  one idea is to compare values within a given tolerance.

HTH  :>)

Dana DeLouis

Answer 4

Koeiegeloei wrote:

So again, sorry for wandering off. I hope you don't get demotivated by this, because you're doing a great job in explaining difficult things.

We blijven altijd gemotiveerd om te helpen maar zoals Joeu2004 aangeeft, is het beter een nieuwe vraag te openen. Je kan dit ook op het Nederlands forum doen, zie onderaan "English" en selecteer uw taal.

En het gebeurt dat we soms in een dialoog gaan naar aanleiding van een vraag.

Ik ben ook vaak aanwezig op het Nederlands forum en weet dat er ook veel NL MVP's aanwezig zijn. Shoot!

To all,

Sorry for this reply in Dutch.

I said: how good you all are and that you stay motivated* but post a new question and that there is also a Dutch forum.

* I am serious!

Answer 5

Thanks Dana!