Accuracy issue (presumably) with low numbers

[willow-mouse]

My colleague encountered a strange issue with a simple sum:
[TABLE="width: 173"]
<colgroup><col></colgroup><tbody>[TR]
[TD]22.05[/TD]
[/TR]
[TR]
[TD]-7.05[/TD]
[/TR]
[TR]
[TD]661.00[/TD]
[/TR]
[TR]
[TD]-232.00[/TD]
[/TR]
[TR]
[TD]-20.75[/TD]
[/TR]
[TR]
[TD]6.20[/TD]
[/TR]
[TR]
[TD]-427.85[/TD]
[/TR]
[TR]
[TD]-1.60[/TD]
[/TR]
[TR]
[TD]Sum:[/TD]
[/TR]
[TR]
[TD]-3.41949E-14[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 136"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
If you reorder any of these it fixes it to zero sum:

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 64"]-427.85[/TD]
[TD="class: xl67, width: 64"]661.00[/TD]
[TD="class: xl67, width: 64"]-1.60[/TD]
[TD="class: xl67, width: 64"]22.05[/TD]
[/TR]
[TR]
[TD="class: xl67"]-232.00[/TD]
[TD="class: xl67"]22.05[/TD]
[TD="class: xl67"]-7.05[/TD]
[TD="class: xl67"]-7.05[/TD]
[/TR]
[TR]
[TD="class: xl67"]-20.75[/TD]
[TD="class: xl67"]6.20[/TD]
[TD="class: xl67"]661.00[/TD]
[TD="class: xl67"]661.00[/TD]
[/TR]
[TR]
[TD="class: xl67"]-7.05[/TD]
[TD="class: xl67"]-1.60[/TD]
[TD="class: xl67"]-232.00[/TD]
[TD="class: xl67"]-232.00[/TD]
[/TR]
[TR]
[TD="class: xl67"]-1.60[/TD]
[TD="class: xl67"]-7.05[/TD]
[TD="class: xl67"]-20.75[/TD]
[TD="class: xl67"]-20.75[/TD]
[/TR]
[TR]
[TD="class: xl67"]6.20[/TD]
[TD="class: xl67"]-20.75[/TD]
[TD="class: xl67"]6.20[/TD]
[TD="class: xl67"]6.20[/TD]
[/TR]
[TR]
[TD="class: xl67"]22.05[/TD]
[TD="class: xl67"]-232.00[/TD]
[TD="class: xl67"]-427.85[/TD]
[TD="class: xl67"]-1.60[/TD]
[/TR]
[TR]
[TD="class: xl67"]661.00[/TD]
[TD="class: xl67"]-427.85[/TD]
[TD="class: xl67"]22.05[/TD]
[TD="class: xl67"]-427.85[/TD]
[/TR]
[TR]
[TD="class: xl66"]Sum:[/TD]
[TD="class: xl66"]Sum:[/TD]
[TD="class: xl66"]Sum:[/TD]
[TD="class: xl66"]Sum:[/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]0
[/TD]
[/TR]
</tbody>[/TABLE]


How is this happening with such simple maths?

 

[oldbrewer]

are the numbers text by any chance ? widen the columns and see if they are left aligned

 

[oldbrewer]

i deleted all the numbers and re-entered them and all was fine - no idea why !!!

 

[Fluff]

I suspect it's a "Floating point error" https://support.microsoft.com/en-gb...ithmetic-may-give-inaccurate-results-in-excel

If you do this, you can see where it goes wrong.

Excel 2013/2016
	A	C
1	22.05
2	-7.05	15.00000000000000000000000
3	661	676.00000000000000000000000
4	-232	444.00000000000000000000000
5	-20.75	423.25000000000000000000000
6	6.2	429.45000000000000000000000
7	-427.85	1.59999999999997000000000
8	-1.6	-0.00000000000003419486916
Smith

Cell Formulas
Range	Formula
C2	=SUM(A$1:A2)

 

[willow-mouse]

Hi all (and apologies for the duplicate post - couldn't see this one after I posted so assumed it had not worked).

I'd assumed a floating point issue, but the reasons that were given on the MS help site wer for very small/large numbers, which these are not. They're all static numbers, so threw up an interesting question.

Thanks for the help!

 

[Fluff]

You're welcome & thanks for the feedback