How does Excel treat numerics differently

[alexskinner]

I am testing one value as a multiple of another and suspect that they are stored differently.
I have a routine accumulating money values in various denominations.
There is a text box for each coin denomination ( eg 50 cent,20 cent, 10 cent etc) called tb10cents, tb20cents etc.
I wish to prevent users from entering 65 cents in the 20cent textbox (for example) .
My routine is:
Iftb20Cents.Value/0.2 <> Round(tb20cents.Value/2) then
Display an error message and return theuser to the input area
Else
Perform the required actions
Endif
This works well for some values but not for all. For example is the text box contains 0.60 (which is a multiple of 20 cents) the error message is displayed.
I suspect that it s because Excel treats numbers differentlybut don’t know how to overcome the problem.
I have tried If tb20Cents.Value Mod 2 <> 0 but thatproduces the same result.
Can anyone provide a solution and if possible the reasoningbehind the problem.

 

[MARK858]

You are possibly hitting a floating point precision issue. Please see the link below for explanation and some methods to alleviate the issue...

https://support.microsoft.com/en-us...ithmetic-may-give-inaccurate-results-in-excel

 

[alexskinner]

You are possibly hitting a floating point precision issue. Please see the link below for explanation and some methods to alleviate the issue...

https://support.microsoft.com/en-us...ithmetic-may-give-inaccurate-results-in-excel

Thank Mark,

This was as I expected. I thought the rounding and/or Mod would overcome the problem but I have discovered a solution.

I have forced the value in the textbox to 2 decimal places and tested against that.
My code now reads If Round(tb20cents.Value/0.2 ,2) <> Round(tb20Cents.Value/0.2 , 0) then etc
It works

Alex