SUM function not working correctly (not a rounding issue)

Question

Level 1

10 points

SUM function not working correctly (not a rounding issue)

OK.

So in the E-column, I have a list of statistics going from E4 through E11. E12 is supposed to give the sum of the preceding numbers within that range.

E

4 34000

5 7000

6 7000

7 30500

8 7000

9 3000

10 0

11 1500

The sum of these numbers should appear in the next row, E12, where I have the formula: "SUM E4:E11". However, whereas E12 should be giving me $91,000, it's instead giving me $57000. There's nothing that I can see that is wrong with my formula. If I modify one of the cell amounts, the 57000 will change by the amount I changed the cell. But there's no reason on earth it should be giving me 57000. I thought maybe there is an issue with how large a cell number can be, but if I change the 0 to a 1 in E10, it outputs 57001 to E12.

iBook, OS X Mavericks (10.9.1)

Posted on Feb 6, 2014 3:43 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,432 points

Feb 6, 2014 4:10 PM in response to milkandgin

I suspect one of your cells is formatted as text. While text cells will add up using the "+" operator, they do not sum using the SUM function. The very first number on your list is not aligned with the rest and it is the exact amount you are off so I suspect it is the culprit.

Reply

Answer 2

milkandgin Author

Level 1

10 points

Feb 6, 2014 4:19 PM in response to milkandgin

Badunit, you sir, get a gold star.

E4 was "text," and the rest were automatic.

It now properly adds up to 91,000.

The third 7,000 in the list was a transcription error. It was supposed to be 8,000.

But thanks everyone!

Reply

Answer 3

Feb 6, 2014 5:48 PM in response to milkandgin

Had you posted a screenshout we all would have seen that one of the cells was justified as text and NOT a number:

Reply

Answer 4

Badunit

Level 7

30,432 points

Feb 6, 2014 6:18 PM in response to Wayne Contello

Wayne Contello wrote:

Had you posted a screenshout we all would have seen that one of the cells was justified as text and NOT a number:

Except that someone might see that misaligned number in their table and right justify it so it looks like the rest of them but will neglect to mention this (because they don't know it is important)

Reply

Answer 5

Feb 6, 2014 4:06 PM in response to milkandgin

I suspect a simple error of some sort on your part. Your numbers add up to 90,000, not 91,000. Your expression: "SUM E4:E11" is not a proper Numbers expression format. If you show us a screen shot with the sum formula cell selected and the bar at the bottom of the screen showing, we may be able to help.

Jerry

Reply

Answer 6

Feb 6, 2014 4:04 PM in response to milkandgin

I cannot confirm your problem.

When I add: "34000+7000+7000+30500+7000+3000+1500" in the spotlight (top right of the menu bar), the total of is 90,000

Using Numbers, I also get 90,000

=SUM(E1:E8)

There is something wrong with your data.

Please post a screenshot

Reply