IF/THEN Statement is resulting False, but Should be True

[kelli84]

Ok y'all I'm new to using forums, so please forgive me if I do not follow current forum codes of conduct.
I have an if/then statement that is resulting "awe shucks" in cell F47.
F47 is dependent on sum(F39:F42) and H40 - H38

In F49 I subtracted H40 from H38. This should be a two digit decimal, I took it to the 13th decimal place and found a "1". It should be a "0".

I took H40 to the 15th decimal place and they are all "0"s. Same with H38.

I know an easy fix is to just use the round function and go to 2 decimal places. But danget Excel shouldn't be this difficult!!

[ChemistB]

Excel is only accurate to 15 digits. Anything after that will show as 0's. I suggest you round to a desirable level, especially as all computers have issues converting back and forth from binary (which is how they do all their math) and decimal (which is how we do our math). So you might expect a sum of 0.5000000000000000 but get 0.4999999999999999

[kelli84]

The problem is that when the data is 2 decimal places how does a difference occur in the 13th place?

[JohnTopley]

It is DISPLAYED as 2 decimal places but as post #2 pointed out , internally it may not be so (as per your file)

An option ...

=IF(ROUND(SUM(F38:F43),6)=ROUND((H40-H38),6),ROUND(SUM(F38:F43),6),"AWE SHUCKS")

[kelli84]

John I entered data to the 2nd decimal place in F2:G23 - which is where the totals that are the source for the formulas.
That's what is puzzling to me is that these are simple addition formulas.

[joeu2004]

I entered data to the 2nd decimal place in F2:G23 [....] That's what is puzzling to me

I can understand your puzzlement. If Excel were "only accurate to 15 digits" and your data is accurate to "6 digits", how could there still be a problem?

The fact is: Excel is __not__ "accurate to 15 digits". As ChemistB and John hinted, the problem is with the internal binary representation of values, namely 64-bit binary floating-point.

Without getting into the "bits and bytes" details, the simple explanation is: most decimal fractions cannot be represented exactly in 64BFP; and the approximation of a particular decimal fraction might vary depending on the magnitude of a value.

That is why, for example, IF(10.01 - 10 = 0.01, TRUE) returns FALSE(!).

In that example, we can see the difference by formatting 10.01-10 to display 17 decimal places (15 significant digits).

But often, we cannot see the difference because Excel formats only the first 15 significant digits (rounded).

For example, the following shows the exact decimal representation on the right of the binary approximation of the constants on the left (H40:H43).

(I use comma to demarcate the first 15 significant digits.)

As you already know, in general, the best work-around (IMHO) is: whenever we expect a calculation to be accurate to some number of decimal places, explicitly round to that number of decimal places -- and __not__ to an arbitrary number (like 6 or 10). For your example, round to 2 decimal places.

Someone might suggest setting the "Precision as displayed" option to avoid the need for (most) explicit rounding.

I do not recommend that option, for many reasons. But if you experiment with it, it is important to make a backup copy of the Excel file before setting the option.

Merely setting the option can cause irreversible changes to constants that are displayed with less precision.

And even setting the PAD option might not remedy some problematic calculations. PAD only affects the final value in a cell. It does not affect calculations within a formula.

So, for example, =H40-H41-H42-H43 might display zero in H47, and =H47=0 might return TRUE. But IF(H40-H41-H42-H43 = 0, TRUE) might still return FALSE(!).