Why is it summing all of my number values as 0.00?

[tvwright]

Please see the following attachment:

Why.xlsx

I have summed the column =SUM(B2:B66) and it equals 0.00. This is inaccurate. The column is full of numbers that are formatted as "number" and there is no circular looping in the formula. The SUM formula is in entered into cell B67. I've read online and everyone says it is because the numbers are formatted as TEXT, but this is not true. The numbers are not formatted as text. They are formatted as number. If I change the whole column to currency, it still remains at 0.00.

What do I do?

[MrShorty]

What total do you expect, because the total seems correct to me. A quick "running total" [=sum(B$2:B2)] in the adjacent columns shows the sum decreasing to a minimum, then coming back to 0 at the end. It appears to me that the result of 0.00 is because the total of all of the positives and negatives "cancel" each other out.

[TMS]

It's a very small negative number formatted as "number".

Select all the cells in the column, including the total, and format them as General. You'll at least see what is in the cell.

Regards, TMS

	A	B	C	D
1		Variance
2		-177.2176795	-177.2176795
3		53.66048237	-123.5571972
4		1.618052835	-121.9391443
5		-88.19078306	-210.1299274
6		-284.6083964	-494.7383238
7		-176.7571227	-671.4954465
8		-40.4362215	-711.931668
9		-136.3268323	-848.2585004
10		46.74985256	-801.5086478
11		63.0341079	-738.4745399
12		-96.31342919	-834.7879691
13		-130.2576943	-965.0456634
14		-129.5362721	-1094.581936
15		-231.1945933	-1325.776529
16		-69.99876985	-1395.775299
17		-63.94280689	-1459.718106
18		-8.990938167	-1468.709044
19		47.74204507	-1420.966999
20		-12.34200698	-1433.309006
21		205.7328358	-1227.57617
22		80.59053637	-1146.985634
23		311.5220184	-835.4636151
24		5.786057111	-829.677558
25		-158.0562911	-987.7338491
26		-37.81534691	-1025.549196
27		-184.8034938	-1210.35269
28		-100.3757401	-1310.72843
29		-95.07364411	-1405.802074
30		-97.39529461	-1503.197369
31		-11.85839644	-1515.055765
32		-48.23284589	-1563.288611
33		60.32008334	-1502.968528
34		-84.03770015	-1587.006228
35		-168.0648257	-1755.071054
36		-132.3207397	-1887.391793
37		14.49918213	-1872.892611
38		209.349982	-1663.542629
39		35.57565479	-1627.966974
40		211.0335436	-1416.933431
41		98.63800551	-1318.295425
42		26.72208074	-1291.573344
43		-152.3927022	-1443.966047
44		-51.13245581	-1495.098502
45		-119.7544978	-1614.853
46		-39.45035379	-1654.303354
47		190.1944582	-1464.108896
48		279.1195607	-1184.989335
49		180.8939437	-1004.095392
50		77.45159623	-926.6437953
51		176.1218766	-750.5219187
52		77.97766049	-672.5442582
53		133.2082363	-539.3360219
54		162.9144607	-376.4215612
55		116.2988213	-260.12274
56		9.411474314	-250.7112656
57		20.64604736	-230.0652183
58		76.80727297	-153.2579453
59		21.44682246	-131.8111228
60		132.4222104	0.611087574
61		82.12421674	82.73530431
62		-193.9403869	-111.2050826
63		14.70732946	-96.49775315
64		-73.45438486	-169.952138
65		84.94127521	-85.0108628
66		85.0108628	-3.72893E-11
67	Total	-3.72893E-11	-7.45786E-11
68

[AlKey]

You get zero because your positive total equal to your negative

=SUMIF(B2:B66,">0") = 3394.272646
=SUMIF(B2:B66,"<0") =-3394.272646

[newdoverman]

The sum of all the positive numbers is 3394.2726
The sum of all the negative numbers is -3994.2726
The result is 0.00

oops, Alkey beat me to it

[tvwright]

I feel really blonde. Sorry... just seemed like it should not equal zero cuz there were so many numbers being added together. Thanks ya'll!

[TMS]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.