SUM and SUMPRODUCT returning different values

Selig

New Member
Joined
Dec 15, 2017
Messages
3
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:
[TABLE="width: 74"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]28090.46[/TD]
[/TR]
[TR]
[TD="align: right"]-8427.14[/TD]
[/TR]
[TR]
[TD="align: right"]-16854.28[/TD]
[/TR]
[TR]
[TD="align: right"]-2809.04[/TD]
[/TR]
</tbody>[/TABLE]

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?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
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.
Code:
[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 :)
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top