Two different sum answers to identical numerical data

[navafolk]

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.

 

[arthurbr]

You are probably the victim of floating point arithmetic
See Floating-point arithmetic may give inaccurate result in Excel - Office on how to correct

 

[navafolk]

You are probably the victim of floating point arithmetic
See Floating-point arithmetic may give inaccurate result in Excel - Office on how to correct

That's it @arthurbr, great thanks.
But in my case, I apply their proposed solutions (neither ROUND function nor Precision as displayed).

 

[Alex Blakenburg]

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.

20211208 Floating Point Error.xlsx
	A	B	C	D	E
1	40,000,000.00		Running Net Figure	Rounded Net Figure	Difference
2	20,000,000.00
3	20,000,000.00
4		(6,666,666.67)	73,333,333.33	73,333,333.33	0.000000000000000
5			73,333,333.33	73,333,333.33	0.000000000000000
6		(6,666,666.67)	66,666,666.66	66,666,666.66	0.000000000000000
7			66,666,666.66	66,666,666.66	0.000000000000000
8		(6,666,666.67)	59,999,999.99	59,999,999.99	0.000000000000000
9			59,999,999.99	59,999,999.99	0.000000000000000
10		(6,666,666.67)	53,333,333.32	53,333,333.32	0.000000000000000
11			53,333,333.32	53,333,333.32	0.000000000000000
12		(6,666,666.67)	46,666,666.65	46,666,666.65	0.000000000000000
13			46,666,666.65	46,666,666.65	0.000000000000000
14		(6,666,666.67)	39,999,999.98	39,999,999.98	0.000000000000000
15			39,999,999.98	39,999,999.98	0.000000000000000
16		(6,666,666.67)	33,333,333.31	33,333,333.31	0.000000000000000
17			33,333,333.31	33,333,333.31	0.000000000000000
18		(6,666,666.67)	26,666,666.64	26,666,666.64	0.000000000000000
19			26,666,666.64	26,666,666.64	0.000000000000000
20		(6,666,666.67)	19,999,999.97	19,999,999.97	0.000000000000000
21			19,999,999.97	19,999,999.97	0.000000000000000
22		(6,666,666.67)	13,333,333.30	13,333,333.30	0.000000016763806
23			13,333,333.30	13,333,333.30	0.000000016763806
24		(6,666,666.67)	6,666,666.63	6,666,666.63	0.000000015832484
25			6,666,666.63	6,666,666.63	0.000000015832484
26		(6,666,666.63)	(0.00)	-	0.000000015832484
27
28	-	0.000000015832484
29	=SUM(SUM(A1:A26),SUM(B1:B26))	=SUM(A1:B27)
30	-	0.000000015832484
31	=SUM(A1:A26)+SUM(B1:B26)	=SUM(A1:A26,B1:B26)
Sheet1

Cell Formulas
Range	Formula
C4:C26	C4	=SUM($A$1:B4)
D4:D26	D4	=ROUND(SUM($A$1:B4),2)
E4:E26	E4	=C4-D4
A28	A28	=SUM(SUM(A1:A26),SUM(B1:B26))
B28	B28	=SUM(A1:B27)
A29:B29,A31:B31	A29	=FORMULATEXT(A28)
A30	A30	=SUM(A1:A26)+SUM(B1:B26)
B30	B30	=SUM(A1:A26,B1:B26)