Mysterious Sum anomaly

[normpam]

I have read something about a 'binary floating decimal point' issue that will sometimes cause simple, whole numbers to function as if the number is really different. Example: you can take 1,059, maybe and subtract 1,030, but the result might be something like 29.000001110010000287870.

I have a current spreadsheet where some of the numbers work like this - they are all just constants - no formulas, and what's more - here's the 'kicker'....

If I select the cells one by one going down and watch the autosum at the bottom of the screen, I will see all zeroes up to 30 decimal places up to row 60. BUT, if I use PageDN two times to select those same cells, then I start seeing the autosum at the bottom showing weird decimals, not zeroes!

Any ideas?
ps: I've tried using the Round function to see if it might fix the original issue, but no luck.

 

[Toadstool]

Hi Normpam,

The challenge is described in this Microsoft document: Floating-point arithmetic may give inaccurate results in Excel.

I'm not sure why you find ROUND is not resolving your challenge unless the ROUND is taking place after the floating point challenge has occurred.

 

[SpillerBD]

"29.000001110010000287870" Well we know that's an exaggeration since Excel only store 15 digits of base-10.
However, seems like I remember a strange solution just being to use a currency format.
You might also try adding a double negative before your round function.

 

[Mickets]

Just came across the same here: the sum should be zero (values were deducted from an initial value until zero). But then I found an anomaly:

	105,000.000000000000000000000000000000000000000000000000000000000000​
	-35,000.000000000000000000000000000000000000000000000000000000000000​
	-20,000.000000000000000000000000000000000000000000000000000000000000​
	-25,000.000000000000000000000000000000000000000000000000000000000000​
	-229.020000000000000000000000000000000000000000000000000000000000​
	-10,000.000000000000000000000000000000000000000000000000000000000000​
	-1,000.000000000000000000000000000000000000000000000000000000000000​
	-847.760000000000000000000000000000000000000000000000000000000000​
	-12,875.950000000000000000000000000000000000000000000000000000000000​
	-47.270000000000000000000000000000000000000000000000000000000000​
Total:​	-0.000000000001385558334732200000000000000000000000000000000000​

Rounding each number to two digits (or whatever) before summing doesn't help. According to the linked Microsoft article, the solution is to round the result of the sum. In my case, =ROUND(SUM(H228:H237),2)