# Two different sum answers to identical numerical data

#### navafolk

##### New Member
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.

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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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)

Replies
4
Views
173
Replies
2
Views
242
Replies
7
Views
160
Replies
0
Views
237
Replies
5
Views
278

1,196,210
Messages
6,014,026
Members
441,801
Latest member
Aneurysm

### 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.

### Which adblocker are you using?

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

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