=(IF .. ) formulas always returning a "False" result

I was going absolutely mad!!!. Even a simple IF statements (e.g. =IF(G4=0, “OK”, “Out of whack” would always return the false value.

Turns out the apparent “zero” value in G4 wasn’t really a zero!. Even changing the number format to 15 places didn’t reveal a problem. But at the 100th (or whatever) decimal point there was a number. So, the logic concluded “False” returned “Out of whack”.

For reasons known only to Google, Tiller, or the Cyber Gods, this little lack of precision crept in that cell’s displayed “Value.” This raised hell with “IF” statements impacting both formulas and conditional formats.

SOLUTION You can use the ROUND function to “Clean up” such niggling crap. For instance, the above formula was changed to =IF(Round(G4,4)=0, “OK”, “Out of whack”. That fixes the problem (FYI, the " ,4" in the Round function is the desires decimal accuracy)

Who knew? Crazy, huh?

1 Like

yep welcome to the fun of using the spreadsheet for money. the value of the cell is not tied to how many digits is being displayed. Youou need to be carefull because many of the times are numbers are formatted as a currency with keeps the value at 2 digits for change. but the actual value is still there and being hidden from you.