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.

 

[Rick Rothstein]

After all, I was a Math major in college, not Computer Science.

Hey, I was a Math major in college too! Of course, I did not pursue math as an occupation... I practiced Civil Engineering for some 32.5 years before my retirement (which was quite a number of years ago now).

 

[zweiunddreissig]

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

To use the MOD function you should 1 not 2 like

=MOD(E23*100,1)=0

Just came across another issue with this new formula: =MOD(E23*100,1)=0
a) if I insert the amount 555.33 I receive an error message -> there are not more than 2 decimal places!?
b) if I insert the amount 666.33 it is fine


In the end, I would like to have an formula, that only shows an error message if the inserted amount has more than 2 decimal places:
i) 555.33 -> ok
ii)555.333 -> nok

I tried also this formula ( =(E23*100)-INT(E23*100)=0 ) but it seems the same issue appears here.

Any other solution???

 

[Fluff]

That's down to floating point errors, as pointed out by Toadstool.
How about

=MOD(ROUND(E23*100,5),1)

 

[zweiunddreissig]

Might this be the right formula?

=INT(E23*100)=(E23*100)

Since I'm not an expert, I need your help. Only with try an error I found out that suddenly there was an issue with the number 555.33.

Thanks a lot!!!

 

[zweiunddreissig]

That's down to floating point errors, as pointed out by Toadstool.
How about

=MOD(ROUND(E23*100,5),1)

Unfortunately it does not work. Even more error messages appear.

 

[Fluff]

Have you tried what Toadstool suggested?

 

[Toadstool]

It should work (within the 15 digit precision challenge):

=RIGHT(TEXT(E23,"00000000.0000000"),5)="00000"

 

[zweiunddreissig]

It should work (within the 15 digit precision challenge):

=RIGHT(TEXT(E23,"00000000.0000000"),5)="00000"

I think that's the solution I was looking for. Many thanks!

 

[Toadstool]

I think that's the solution I was looking for. Many thanks!

You're welcome!
Glad to help.