Issue with MOD-formula

[zweiunddreissig]

Dear community,

I need your expert advice!

I am working on a sheet to calculate fees.

For the amount field, I have used the following formula in "Data Validation": =MOD(E23*100;2)=0
-> The rule should avoid users to enter more than two decimal places.

Now I have noticed that odd decimal places get an error message, however, even decimal places are ok.

Examples:
100.12 -> ok
100.13 ->nok

What is wrong with my formula? The cell should allow even and odd decimal places as long there are not more than 2 decimal places.

Many thanks for your help!!!

Best regards,
B.

 

[Joe4]

Welcome to the Board!

Your MOD formula does NOT check the number of decimal places.
What it does is check the remainder of division when you mulitply the value by 100 and then divide that by 2.

So, if you have 100.13, then multiplying 100 will give you 10013.
If you divide that by 2, you get 5006, with a remainder of 1. So the MOD formula would return 1, not 0 for that example.

If you want to limit to two decimals, try this formula instead:

=(E23*100)-INT(E23*100)=0

 

[Fluff]

To use the MOD function you should 1 not 2 like

=MOD(E23*100,1)=0

 

[Joe4]

To use the MOD function you should 1 not 2 like

=MOD(E23*100,1)=0

Well, that certainly is easier.
Don't know why I never considered just dividing by 1 in the MOD formula!


I guess that is a sign for me to log off and get away from the comptuer for a while!

 

[zweiunddreissig]

Welcome to the Board!

Your MOD formula does NOT check the number of decimal places.
What it does is check the remainder of division when you mulitply the value by 100 and then divide that by 2.

So, if you have 100.13, then multiplying 100 will give you 10013.
If you divide that by 2, you get 5006, with a remainder of 1. So the MOD formula would return 1, not 0 for that example.

If you want to limit to two decimals, try this formula instead:

=(E23*100)-INT(E23*100)=0

Thank you so much for your help!!!!

 

[zweiunddreissig]

To use the MOD function you should 1 not 2 like

=MOD(E23*100,1)=0

Wow, what a simple change in the formula. Thanks a lot!!!!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Rick Rothstein]

Don't know why I never considered just dividing by 1 in the MOD formula!

Probably because it is not a "natural" thing to do. Using 1 as the modulus to return the fractional part of a floating point number in a modulo calculation is strictly an Excel worksheet formula implementation. The mathematical definition of MOD works only with whole numbers. VBA follows that rule with its Mod operator deliberately rounding any floating point numbers to integer values (using Bankers Rounding) before performing the modulo operation. While Excel's formula implementation of the MOD function is useful, it is not "standard" in any way.

 

[Toadstool]

It is very possible I'm mistaken, but wouldn't

=MOD(E23*100,1)=0

have the problem with the way Excel handles floating point such that some results would not be zero (0.07 or 144.73)

Would this be a safer route?

=MID(TEXT(E23,"0000000.0000000"),11,5)="00000"

 

[Joe4]

Probably because it is not a "natural" thing to do. Using 1 as the modulus to return the fractional part of a floating point number in a modulo calculation is strictly an Excel worksheet formula implementation. The mathematical definition of MOD works only with whole numbers. VBA follows that rule with its Mod operator deliberately rounding any floating point numbers to integer values (using Bankers Rounding) before performing the modulo operation. While Excel's formula implementation of the MOD function is useful, it is not "standard" in any way.

I like that explanation. After all, I was a Math major in college, not Computer Science. So maybe my brain naturally went there.
(That's my story and I am sticking with it!)