SUM and SUMPRODUCT returning different values

[Selig]

I've found an interesting situation where suming a list of values with SUM and SUMPRODUCT is returning me slightly different values. The SUM is correct (0), while SUMPRODUCT is giving non-zero (9.09495E-13).
The values in my column are C2:C5:

28090.46

-8427.14

-16854.28

-2809.04

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

With formulae:
=SUM(C2:C5)
=SUMPRODUCT((C2:C5)*1) [This is a much simplified version of my formula as a result of my issue hunting]

I've been able to recreate it with different numbers, and by manually typing the numbers to remove any strange copy/paste issue. But equally I've used some numbers which don't have the issue (100.44, -50.22, -50.22)

I'm running Excel 2016 MSO (16.0.8201.2209) 32-bit, if that helps...

Anyone got any ideas why it's happening?

 

[Joe4]

I think you may have the famous "floating arithemetic error" issue going on.
See: https://support.microsoft.com/en-us...ithmetic-may-give-inaccurate-results-in-excel

One way around it is to use the ROUND function, i.e.

[COLOR=#333333]=ROUND(SUMPRODUCT((C2:C5)*1),2)[/COLOR]

 

[Legacy 143009]

I don' know why exactly but if you want no effect on your sum-multiply action you should use 0 instead of 1 in SUMPRODUCT function.

 

[Selig]

I think you may have the famous "floating arithemetic error" issue going on.
See: https://support.microsoft.com/en-us...ithmetic-may-give-inaccurate-results-in-excel

One way around it is to use the ROUND function, i.e.

[COLOR=#333333]=ROUND(SUMPRODUCT((C2:C5)*1),2)[/COLOR]

Ah, my Google-fu failed me it seems. The article suggests that since 97 Excel is meant to try and correct for sums to zero, but that evidently doesn't work for SUMPRODUCT in this case. I can live with putting the SUMPRODUCT in a ROUND.

Thanks for the pointer in the right direction