Excel is adding numbers differently depending on their order

[Meow]

Here is a weird thing that is happening. I want to know why it is happening and, if possible, how to correct it.

I have a list of negative numbers that is supposed to equal a negative of a positive number so, when they are added together, they cancel each other out and equal 0.

When I have the list of negative numbers on the bottom of the list and the positive number on top of the list, the answer is an absurdly large number. When I have the list of negative numbers on top and the positive number on the bottom, it equals 0, as it should.

I'm using the =sum() formula to add it up.

The list of negative numbers was originally a list of positive numbers, but I multiplied them all by -1 in an adjacent column, and then copied and pasted values in that column.

What is going on??

Thank you.

 

[Meow]

Sorry, to clarify my second sentence, it should read "I have a list of negative numbers that is supposed to equal a negative of a positive number so, when they are added together with the positive number, they cancel each other out and equal 0."

 

[jamiemarie]

How are your cells formatted? When I sum I get $0.00 for both columns, even multiplying the positive number by -1 and copying and pasting raw value. I have not been able to reproduce your answer. Could you put your data in a comment using the XL2BB tool? This will give all formulas and formatting that you have for your range.

 

[Fluff]

You are not getting "an absurdly large number", you are actually getting a very very small number, namely 0.00000000000003064
This is down to something called floating point precision.

Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

Discusses that floating-point arithmetic may give inaccurate results in Excel.

docs.microsoft.com

 

[Meow]

That is interesting, thank you.

 

[jamiemarie]

@Fluff Thanks for the read, I have encountered the floating-point problem before and never knew what caused it. I still don't quite understand why Meow's first column was fine but the second wasn't, and also why I couldn't reproduce his sum.

 

[Meow]

How are your cells formatted? When I sum I get $0.00 for both columns, even multiplying the positive number by -1 and copying and pasting raw value. I have not been able to reproduce your answer. Could you put your data in a comment using the XL2BB tool? This will give all formulas and formatting that you have for your range.

I actually just tried doing it again and I also got 0.00 whether the positive number was on the top of bottom of the list. So who knows. Thank you for your response!

 

[Fluff]

Glad it's sorted & thanks for the feedback.

 

[joeu2004]

I still don't quite understand why Meow's first column was fine but the second wasn't, and also why I couldn't reproduce his sum.

I actually just tried doing it again and I also got 0.00 whether the positive number was on the top of bottom of the list. So who knows.

I suspect that you are both making the mistake of looking at the formatted results, not the actual cell value.

Be sure that B21 (SUM formula in the second column) is formatted at General (or Scientific) after entering the formula.

In Meow's second attempt, B21 appears to be formatted as Number with 2 decimal places (0.00 sic).

Generally, when looking at anomalies of floating-point arithmetic, I suggest that you temporarily format the cells in question as Scientific (with 2 decimal places, the default).

That is the only way to be sure that what appears to be zero is (or is not) truly exactly zero (0.00E+00).

Caveat: We must reapply the Scientific format each time after entering the formula, in order to override Excel's autoformatting heuristics (sigh). Alternatively, format all of A1:A20 as Number with 2 decimal places.

Although Fluff is correct that non-integer arithmetic can have different results due to order because of 64-bit binary floating-point anomalies, the exact zero in A21 (SUM of the first column) is an illusion created by arbitrary and dubious tricks that Excel applies inconstently in a poorly-conceived attempt to hide (some) floating-point anomalies.

These tricks are alluded to in the section titled (misleadingly) "Example when a value reaches zero" in the webpage that Fluff cites.

To demonstrate, change the simple formula in A21 (presumably =SUM(A1:A20) to =SUM(SUM(A1:A19),--A20) , then (afterward) format A21 as Scientific.

The result is about 5.68E-14. Still different from about -3.06E-14 for =SUM(B1:B20) presumably in B21.

But not exactly zero (0.00E+00).

However, note that all of the following formulas do result in exactly zero (!): =SUM(A1:A20), =SUM(A1:A19,A20), =SUM(A1:A19,--A20) and =SUM(SUM(A1:A19),A20) .

Of course, there is no good reason for the difference, based on the 64-bit binary floating-point standard.

Instead, the explanation is that the SUM function is doing "funny things" based on the nature of the parameters.

To demonstrate that this is an anomaly of Excel SUM, contrast with "vbsum" functions in the example below.

The general "remedy" (workaround to avoid such differences) is: whenever you expect a calculation that involves non-integers to be accurate to some precision, explicitly round to that precision -- and not to arbitrary number of decimal places like 10, as some people suggest.

Your formulas should be =ROUND(SUM(A1:A20),2) and =ROUND(SUM(B1:B20,2) .

-----

Excel v. VBA calculations

To demonstrate that the different results of the various SUM formulas are due to anomalies of the Excel SUM implementation and not 64-bit binary floating-point standard, the following compares the Excel SUM results in columns A and B with the results of similar VBA functions in columns C and D.

flt pt order2.xlsm
	A	B	C	D	E
21	0.00E+00	-3.06E-14	5.68E-14	-3.06E-14
22	0.00E+00	-3.06E-14	5.68E-14	-3.06E-14
23	0.00E+00	-3.06E-14	5.68E-14	-3.06E-14
24	0.00E+00	-3.06E-14	5.68E-14	-3.06E-14
25	5.68E-14	-3.06E-14	5.68E-14	-3.06E-14
26			3.11E-15	3.11E-15	80-bit arithmetic
27	Excel formulas		VB calculations
Sheet1

Columns A and B use Excel to calculation the sums of A1:A20 and B1:B20 in different ways.

Columns C and D use VBA functions to do similar calculations.

Note that the VBA results are consistent in each column, regardless of method of summing.

For the most part, the difference between the results in columns C and D is the order of the operands, which is an anomaly of 64-bit binary floating-point.

However, the difference in row 26 is due to the fact that, for Intel-compatible CPUs, VBA uses the intermediate 80-bit floating-point internal results whenever possible for a series of type Double calculations. (And not for calculaltions with type Variant operands, even if they contain type Double values.)

 

[joeu2004]

Addendum....

To demonstrate that the different results of the various SUM formulas are due to anomalies of the Excel SUM implementation and not [the] 64-bit binary floating-point standard, the following compares the Excel SUM results in columns A and B with the results of similar VBA functions in columns C and D.
[....]
However, the difference in row 26 is due to the fact that, for Intel-compatible CPUs, VBA uses the intermediate 80-bit floating-point internal results whenever possible for a series of type Double calculations. (And not for calculaltions with type Variant operands, even if they contain type Double values.)

(Sorry, I didn't submit these changes within the 10min edit limit for this forum.)

Note there there is no difference between C26 and D26. That is somewhat of a coincidence, although for simple addition and subtraction, there is less chance of order-dependent differences, due to the higher precision of the calculations.

The VBA code is:

Function vbsumR(r As Range) As Double
Dim v As Variant, s As Double, i As Long
v = r.Value2
' VBA rounds intermediate results to 64-bit in this context
For i = 1 To UBound(v): s = s + v(i, 1): Next
vbsumR = s
End Function

Function vbsumRV(r As Range, x As Double) As Double
Dim v As Variant, s As Double, i As Long
v = r.Value2
' VBA rounds intermediate results to 64-bit in this context
For i = 1 To UBound(v): s = s + v(i, 1): Next
vbsumRV = s + x
End Function

Function vbsumVV(x1 As Double, x2 As Double) As Double
vbsumVV = x1 + x2
End Function

Function vbsumr80(r As Range) As Double
Dim i As Long, v(1 To 20) As Double
If r.Count <> 20 Then vbsumr80 = 1 / 0  ' some error
For i = 1 To 20: v(i) = r(i): Next
' VBA does 80-bit arithmetic in this context
vbsumr80 = v(1) + v(2) + v(3) + v(4) + v(5) + v(6) + v(7) _
     + v(8) + v(9) + v(10) + v(11) + v(12) + v(13) + v(14) _
     + v(15) + v(16) + v(17) + v(18) + v(19) + v(20)
End Function

Yes, I could have consolidated the functions. I keep them separate so that their implementations are more transparent. The differences are very subtle.