excel spreadsheet does not compare numbers properly

Question

Load the spreadsheet from Google Drive

https://drive.google.com/file/d/1WkDA9wTLJSsbt2TwM984mddIWhf-4fzf/view?usp=sharing

The formula that won't behave properly is at "THIS IS THE FORMULA THAT WON'T BEHAVE"

The formula is simply comparing a calculated  value of 0.01 to a CONSTANT defined as 0.01.

Both cells are defined as NUMBER with 2 decimal places.

It misbehaves whether I use the CONSTANT or just hard-code the 0.01 value within the formula.

This is pretty basic stuff. Perhaps I inadvertantly changed some option?

Thanks in advance for your help.

Answer 1

Right click on H34 > Format Cells > Increase the Decimals to 20

Now you will see that the value is not 0.01 but 0.00999999.....

That is the reason why it is not matching with 0.01

This is not your issue. This is called Floating Point Error (happens with all IT Systems)

Refer to following. The solution as described in second link is to use ROUND function.

http://support.microsoft.com/kb/78113/en-us

http://blogs.office.com/2008/04/10/understandin...

Hence, you will need to use following formula

=IF(ROUND(H34,2)=J26,"OK","?")

Answer 2

Yours and Jason's answers were both helpful. The article you referred to was quite informative. Since one is not able to mark more than one post as the answer, I've marked both as helpful and marked yours as the answer.

Thanks for you help

Answer 3

Yours and E for Excel's answers were both helpful. The article E for Excel referred to was quite informative. Since one is not able to mark more than one post as the answer, I've marked both as helpful and marked E for Excel's as the answer.

Thanks for you help.

Answer 4

The answer provided here should help you:

https://answers.microsoft.com/en-us/msoffice/fo...