Two different sum answers to identical numerical data

navafolk

New Member
Joined
Jul 22, 2015
Messages
20
I have numerical data in two columns.
A column (A1:A3) includes positive numbers and B column (B4:B26) includes negative numbers; and they all exactly net-off to zero.
But excel comes out 2 different sum results if I try 2 SUM:
- SUM whole 2 ranges: =SUM(A1:B26); or =SUM(A1:A3,B4:B26) the answer is not exactly zero. It is very small, but it is not acceptable.
- SUM of 2 separate SUM: =SUM(SUM(A1:A27),SUM(B1:B27)); or =SUM(A1:A27)+SUM(B1:B27) the answer is exactly zero
In addition, the quick preview on bottom of excel shows the same result with SUM whole 2 ranges.
Capture2.JPG
For sure, I have checked all decimal position. It is driving me crazy. Is there any way to solve this, anyone please help. Thank you in advance.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
I actually found this article by John Cook in the Code Project to be more helpful.
Five Tips for Floating Point Programming

The problem seems to be mostly related to subtraction, and in the your 2 working examples you do all the addition first and 1 subtraction right at the end, while in the 2 that don't work you do subtraction much earlier in the piece.

A quote from the article is:-
Really the problem is subtraction; addition can only be a problem when the two numbers being added have opposite signs, so you can think of that as subtraction. Still, code might be written with a "+" that is really subtraction.

Subtraction is a problem when the two numbers being subtracted are nearly equal. The more nearly equal the numbers, the greater the potential for loss of precision. Specifically, if two numbers agree to n bits, n bits of precision may be lost in the subtraction.


Cell Formulas
RangeFormula
C4:C26C4=SUM($A$1:B4)
D4:D26D4=ROUND(SUM($A$1:B4),2)
E4:E26E4=C4-D4
A28A28=SUM(SUM(A1:A26),SUM(B1:B26))
B28B28=SUM(A1:B27)
A29:B29,A31:B31A29=FORMULATEXT(A28)
A30A30=SUM(A1:A26)+SUM(B1:B26)
B30B30=SUM(A1:A26,B1:B26)
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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