Numers don't add up correctly with Autosum and some give a VALUE reply

[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?

I also don't know why Excel is doing this - a funny setting or should I remove it and reinstall it - maybe got all screwed up for some reason?

Any assistance would be greatly appreciated.

 

[CyberTaz]

The first thing I'd suspect is that the content being omitted or causing
errors in the calculations may have been pasted in or entered in some other
way as TEXT rather than as values. Have you checked the cell formatting?

 

[Clive]

The first thing I'd suspect is that the content being omitted or causing

errors in the calculations may have been pasted in or entered in some other
way as TEXT rather than as values. Have you checked the cell formatting?

--
HTH |:>)
Bob Jones
Office:Mac MVP

wrote in message


The funny thing Bob is that this happens on any new sheet I care to open and start putting data into cells - which implies to me its a 'global' setting somewhere. I have cleared all the formatting in every cell and it doesn't seem to make any difference.

What is strtange is that I have never encountered this before in working wih Excel for many years - at the moment Excel is unusable1

 

[Bob Greenblatt]

What is strtange is that I have never encountered this before in working wih
Excel for many years - at the moment Excel is unusable1

Clearly, Excel "thinks" some of the cells contain text. If you pasted in
values form another source, this may happen. In the case where the
subtraction gives a value error try selecting the cell, putting the cursor
in the formula bar and pressing enter for each cell. This should fix the
problem. If it is occurring in a large area of the sheet, do this:
Enter a 1 in an empty cell. Copy it. Select the range where you are having
the problem. Then in the Edit menu, select paste special, and tick multiply.
Then things will all be values and you can delete the 1.

 

[Shane Devenshire]

Hi,

If you type the values into the cells, in a new spreadsheet, does this happen?

You didn't mention how the data was entered, although others asked if you
were pasting it in.

You can check to see what type of cell Excel thing you have by entering the
formula =ISTEXT(A1)
=ISNUMBER(A1)
If the first returns TRUE and the second FALSE you have text.

You can convert numbers which are being stored as text by selecting an empty
cell and choosing Copy, then select all the cells that are text number and
choose Edit, Paste Special, Add.

Note - if a number has been entered as text changing the cell format has no
effect on the already entered data.

It is possible to set the format of the entire workbook to text but if that
has been done it was intentional and to affect all new workbooks it would
have to be done in the default workbook, which is very unlikely.

 

[CyberTaz]

Are you creating new workbooks from a template, or have you created your own
custom workbook or sheet? If not & this is happening from the start in new
files/sheets I'd suspect a preferences or account corruption.

As a diagnostic step try launching Excel while holding the Shift key -- does
the problem occur in the new file? Secondly...

Launch Excel in a new User Account & see if the problem follows you there.

Reply with complete details of your results as well as exact Office & OSX
update levels.

Also, a friendly request: Regardless of personal preference re top/bottom
posting it's best to follow the lead set in the thread. Having to bounce
from one extreme end to another is not only frustrating but also makes it
terribly difficult to follow the conversation. Thanks

Regards |:>)
Bob Jones
[MVP] Office:Mac