Accuracy issue (presumably) with low numbers

[willow-mouse]

My colleague encountered a strange issue with a simple sum:

22.05
-7.05
661.00
-232.00
-20.75
6.20
-427.85
-1.60
Sum:
-3.41949E-14

<colgroup><col></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody>
</tbody>

If you reorder any of these it fixes it to zero sum:

-427.85	661.00	-1.60	22.05
-232.00	22.05	-7.05	-7.05
-20.75	6.20	661.00	661.00
-7.05	-1.60	-232.00	-232.00
-1.60	-7.05	-20.75	-20.75
6.20	-20.75	6.20	6.20
22.05	-232.00	-427.85	-1.60
661.00	-427.85	22.05	-427.85
Sum:	Sum:	Sum:	Sum:
0	0	0	0

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

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