Sum of cells don't equal

[ttanner]

I have a spreadsheet that has multiple columns that I use the formula Sum(b1:b14) the total of these values are not correct. If I add on a calcualtor they don't add up the the value in the formula. The values are copied from another worksheet and look correct in both worksheets. I have never seen anything like this before and have no idea how to fix.

Column one I entered the values by themselves. Column two is the one that is referenced from another spreadsheet. I even tried copying just the values from that worksheet and is still wrong. What is listed below is exactly how it is showing up in Excel. Column one total is correct.

4583 ---- 4583
1000 ---- 1000
1500 ---- 1500
500 ----- 500
3000 ---- 3000
3000 ---- 3000
3000 ---- 3000
588 ----- 588
936 ----- 936
295 ----- 295
3000 ---- 3000
204 ---- 204
206 ---- 206
3033 ---- 3033
165 ---- 165
208 ---- 208
1208 ---- 1208
133 ---- 133
1500 ---- 1500
191 ---- 191
561 ---- 561
67 ---- 67
506 ----- 506
125 ----- 125
160 ----- 160
100 ---- 100
458 ----- 458
153 ---- 153
30,380 ---- 40,380

[jasoncw]

Could you attach a copy of your workbook? I just copied and pasted these values into Excel, and both columns summed to 30,380.

Jason

[ttanner]

Jason:

About 1:00 in the morning I gave up trying to figure it out. Deleted the worksheet and started from the beginning. Works fine now. I have had this problem before, not often, but sometimes the cells just do not add up correctly and it is always in the SUM fuction. One time I was able to fix by doing B1+B2+B3+B4 rather than B1:B4, but this time that didn't even work.

There is obviously a bug somewhere in the program. Is anyone familar with this issue. This is Excel 2003.

Thanks

[cbjroms]

I have a spreadsheet with a list of totals (left hand column below is actually H in the spreadsheet) and am trying to put a cumulative total in the right hand column. But the right hand column shown below is what I get from the formula:

2,727.20 2727.20 (=H1)
1,845.00 4572.20 (=I1+H2)
1,800.00 4572.20 (=I2+H3)
1,710.00 4572.20 (=I3+H4)
1,575.00 4572.20 (=I4+H5)
1,485.00 4572.20 (=I5+H6)
1,350.00 4572.20 (=I6+H7)
1,350.00 4572.20 (=I7+H8)

How can 4572.20 + 1800.00 = 4572.20?

I have tried to look for hidden formatting or formulas and also copied/pasted to a new sheet. But the calculations continue to show the same answers.

What am I missong?

Thanks in anticipation.

Chris

[Dean England]

It appears that your formulas from I3 down were copied with values rather than formulas. I think if you look at I3 you will not see the formula but the value of I2.

2,727.20	2727.20	(=H1)
1,845.00	4572.20	(=I1+H2)
1,800.00	6372.20	(=I2+H3)
1,710.00	8082.20	(=I3+H4)
1,575.00	9657.20	(=I4+H5)
1,485.00	11142.20	(=I5+H6)
1,350.00	12492.20	(=I6+H7)
1,350.00	13842.20	(=I7+H8)

And you should really start your own thread rather than piggy backing on someone elses thread.

Dean

[lsmft]

FWIW:
I have had the same problem and found that all of the cells being used were not formatted the same. I tried to simply reformat but that didn't cut it either. Bottom line, I had to deleted the cells that were not formatted correctly, reformat, then replace the previous data and all worked well.

[arthurbr]

If you have doubts about the format of your cells, do the following:
Insert a "1" in any empty cell - Select - Copy
Select the entire range of numbers
Right-click
Select Paste Special
Check " Multiply"
Then OK