SUM doesn't work and other cells show a VALUE!

[Clive]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Firstly I have used Excel for many years without this problem ever surfacing!
The problem is that when I SUM a column of numbers the results aren't correct so clearly all the numbers aren't being added up - even though the formula is correct.
Additionally on some cells where I am doing a very simple 'take one number from another number' type of calculation - all that is returned in the cell is VALUE! and not the correct result. Clearly a setting isn't correct - but which one?
Any assistance would be greatly appreciated.

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Firstly I have used Excel for many years without this problem ever surfacing!
The problem is that when I SUM a column of numbers the results aren't correct
so clearly all the numbers aren't being added up - even though the formula is
correct.
Additionally on some cells where I am doing a very simple 'take one number
from another number' type of calculation - all that is returned in the cell is
VALUE! and not the correct result. Clearly a setting isn't correct - but which
one?
Any assistance would be greatly appreciated.

It's not a setting, it's your data! Some of the data is text, even though it
looks like numbers and may be right justified. Fixing it is easy:
- in an empty cell, type a 1 and press enter.
- Select the cell and copy it.
- Select the range of numbers that gives the incorrect sum.
- Go to Edit-Paste special, select multiply.
_ Now all the text numbers are indeed numeric values. Delete the 1 from the
spare cell.

 

[Shane Devenshire]

Hi,

Things to check:
1. Is the data entered as Text or Numbers. You can check this by
=ISTEXT(A1) for example. Numbers can be text if they are entered with an '
(apostrophy) at the beginning or if the cell is formatted to text before the
values are entered.

2. Are any of the cells in the sum range formulas? Do any of those formulas
return #VALUE?

If a cell contains text numbers =A1+A2 will calculate but =SUM(A1:A2) will
return 0.