Excel Formula Error

That little green triangle in the upper left corner of your cell where the 0.00 is, that means it is text. Your formula is calling for a numeric zero. If you want it to match try putting the zero in your formula in quotes or change the formula that produces the zero so that it’s not text.

Originally Posted by kencoburn

As can be seen, the value is 0.00
[....]
I do not understand why the subtraction of 2 numbers that are exactly the same (to 20 decimal places) should produce a small difference when subtracted.

__We__ cannot see much of anything because you did not attach an Excel file, per instructions at the top of the page.

So we can only answer in generalities.

First, the solution.... In general, when you expect a calculation to be accurate to some number of decimal places, you should use ROUND to explicitly round to that number of decimal places -- and not to an arbitrary number like 10, as some people suggest.

For example, change E196-Summary!D37 to ROUND(E196-Summary!D37, 2) .

The operative word is "expect". Sometimes, it is prudent to delay rounding. Only you can make that decision.

Yes, it is tedious to explicitly round as needed. But I do not recommend setting the "Precision as displayed" option as an alternative, as someone might suggest. There are oh-so many reasons.

-----

Second, the reason.... In general, this is a side-effect of 64-bit binary floating-point, which is how Excel represents numbers internally. It is not a defect. It is a consequence of a design decision that is common to most applications.

In a nutshell, most decimal fractions cannot be represented exactly in binary. And the binary approximation of a particular decimal fraction might vary depending on the magnitude of the number.

Consequently, for example, 10.01 - 10 = 0.01 returns FALSE(!). You can see why by looking at =10.01 - 10 formatted to display 17 decimal places, but not by looking at 10.01 itself. The approximation of 0.01 in 10.01 is different from the approximation of 0.01 itself.

It is possible that there are other factors to consider, as well. For example, additional rounding might be prudent in the formulas(?) in E196 and Summary!D37.

I can provide more details if you attach an Excel file that demonstrates the problems.

-----
PS, possibly TMI....

I guess we can assume that E196 and Summary!D37 display 3538.29 -- but it would be prudent to format with 11 decimal places to be sure.

Presuably, one or both are calculated.

We can see the infinitesimal difference between displayed value (arbitrarily limited to 15 significant digits) and binary value with formulas of the following form, formatted as General or Scientific.

=SUM(E196, -ROUND(E196, 2))
=SUM(Summary!D37, -ROUND(Summary!D37, 2))

We must use SUM instead of =E196-ROUND(E196,2), for example, because for the latter form, Excel sometimes arbitrarily replaces the exact arithmetic difference with exactly zero in order to hide the infinitesimal differences, a dubious "feature" (sigh).