Jan 26 2023 05:41 PM
Jan 26 2023 05:41 PM
I can't figure this out to save my life.
I have the same number in A1 and A2, and a different number in B1 and B2.
As you can see from the screen cap A1=A2 and B1=B2.
However B1-A1 is not equal to B2-A2.
Any ideas why this may be?
Thank you kindly for your help
Jan 26 2023 06:05 PM - edited Jan 27 2023 03:11 AM
For a dispositive explanation, please attach an example Excel file that demonstrates the problem.
In a nutshell, it is an anomaly of the way that Excel represents numbers internally, namely 64-bit binary floating-point.
Most decimal fractions cannot be represented exactly in binary. So, they are approximated. Moreover, the binary approximation of a particular decimal fraction might vary depending on the magnitude of the number. That is why, for example, 10.1 - 10 = 0.1 returns FALSE (!).
In general, when a calculation involves or results in numbers with decimal fractions, and we expect or want the result to be accurate to some number of decimal places, explicitly round to that number of decimal places -- and not to an arbitrary number like 10 decimal places.
For your example, we might write ROUND(B1-A1, 1) and ROUND(B2-A2, 1) because your data appears to be accurate to 1 decimal place.
But perhaps they are accurate to more decimal places, and your examples just happen to have trailing zeros. So you might want to round to 2 decimal places, for example.
One or both of A1 and B1 are calculated, and their exact binary value is infinitesimally different from the binary approximation of the formatted decimal number, which Excel arbitrarily limits to 15 significant digits.
C1: 118.499999999989 (close!)
Although A1 appears to be 10697.7000000000, it is actually 10697.7 + 1.09E-11. We can see the residual with the formula =SUM(A1,-(A1&"")), formatted as General or Scientific.
The same might be true of A2 and B2 as well. But their difference (B2-A2) is close enough to the binary approximation of 118.5 that it is not apparent due to the Excel formatting limitation.