@CodeBot wrote: ``Different results after 10th decimal place when multiplying same numbers in the same excel sheet``
First, none of the numbers are exactly as they appear to be. Excel formats only up to the first 15 significant digits in worksheets. But the values can -- and do -- have more precision that Excel does not show us.
Second, the numbers that you think are the same are actually infinitesimally different. That is the reason for the visibly different results.
Finally, they appear to differ in the 11th decimal place, not the 10th. And in fact, the difference is about 1.36E-12, not 1E-11 as it appears. The difference seems larger because of how each value is rounded to 15 significant digits when displayed.
Note: It is wrong to say that Excel "stores" only 15 significant digits, as demonstrated below.
If Excel formatted up to 17 significant digits (rounded) in worksheets, the reason for the differences would be clear.
Note: Even the 17-digit presentation is an approximation. But 17 significant digits is necessary and sufficient to convert between decimal and binary representations with no loss of binary precision. We can usually see the 17-digit presentation by looking at the worksheet XML file inside the "xlsx" zip file ("archive").
G27 appears to be 0.91851%, but it is actually 0.91851463062590216%
H26 is actually 153095.04486992653
H27 is actually 153095.04486992667
H26=H27 returns TRUE because they both round to 153095.044869927, which the comparison operators ("=", "<>", ">=", etc) do internally.
But note that H26-H27=0 returns FALSE because their binary values are not the same.
I26 is actually 1406.2003858935648
I27 is actually 1406.2003858935661
I26=I27 returns FALSE because they differ when rounded to 15 significant digits, again which the comparison operators ("=", "<>", ">=", etc) do internally.
