There appears to be a problem with the ">" operator returning a wrong result

[TomCon]

I'll paste an image of my trials to try to narrow this down. Sorry I cannot use xl2bb...unable to install it on my pc (see my post Disaster after trying to install xl2bb -- Please help/rescue!)

I wonder if anybody can reproduce the results i've highlighted in yellow with those specific numbers, which appear to be incorrect to me.

Or, do you have an explanation for why, what i'm seeing, is actually correct/ok?

It only seems to happen with some specific numbers. I was trying other numbers to see how i could narrow it down. I originally discovered it with the numbers in row 1,2, so i tried other pairs. Some give what appears to me to be a correct result and others do not. I tried this in a brand new workbook (Book1 at its instantiation) to make sure nothing else in the workbook could affect this.

• All values in cols A,B are formatted "General"
• All values in Col A were entered as numbers, they are not the result of a formula calculation.
• What is shown in Cols D,E is the result of the FORMULATEXT function, so it correctly shows what the formula is in Cols B, C.

Comments? (Especially can you reproduce?).
Thanks!

 

[StephenCrump]

B2 is actually 2.40000000000001

Here's why: https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result

You'll need to apply ROUND() when doing these sorts of tests.

 

[TomCon]

OK thank you for posting that article. It is definitely the explanation for what i have observed.

But i must say it is very disconcerting! I did understand that values like 1/3 may not be able to be stored exactly, and so on. But seeing that i cannot replay on a compare operator for a simple result like 2.4...it really calls into question...does every formula you do that has a comparison operator (<,>,=) need to be "protected" with a ROUND() function?? Wow!! Is it amazing that i never ran into this earlier, with very extensive Excel use for years? How would one know which numbers are "special" and produce this type of result. As you see, i tested many others, and they were ok.

I did expand out the decimal and see the following.

So, apparently those two "very nice looking numbers" (no repeating decimal or anything like that) produce a "very unintuitive" result. I will probably not try to absolutely fully understand why those two numbers cannot produce an exact result when subtracted. Even if i did, would i be able to avoid this seemingly "wrong" result without putting ROUND() around every comparison i want to make? It seems like it would be very difficult to consider every comparison analyzing "is this a case that will not produce an exact result" (2.4) or be just fine (2.3, 2.5)?? Wow, hard!!

I originally noticed this as this comparison was in a conditional format....I wanted to highlight every difference > 2.4...and noticed that one was highlighted that should not have been.

Thanks for the discussion/edification!