HELP - Excel 2003 formulas don't work-as if cell values aren't num

[Guest]

This problem started when I was upgraded to Excel 2003.

When I receive an unprotected Excel spreadsheet from someone else, I am
unable to add a formula that works. For example, I will enter a sum
formula for a column of numbers, and the total will be 0.

I've tried:
* changing the the format of the input cells - back and forth - from text
to numerical, etc.
* turning on and off error correction, and sharing.
* copy, paste special, values.
* saving the worksheet under a new name.
* turning manual calculation on, and off, etc.
* Unchecking link external values under tools, options.

But the only thing that works is if I manually reenter each number in the
column, which defeats the purpose of a spreadsheet.

This is very frustrating.

 

[Guest]

Sounds like your column of numbers is formatted as text. On another column,
you can add 0 to each number to convert them to a real number. For example,
if your original numbers are in column A, you can enter the formula =A1+0
into the B1 cell and copy down. Then all the values in column should be
numbers and you can use the SUM function off of column B.

-Simon

 

[Guest]

Maybe the numbers were imported from the web and contain the nbsp character
(ASCII code 0160).

Try this:

Select the range of numbers

From the Excel main menu:
<edit><replace>
Find what: hold down the [alt] key....type 0160...release the [alt] key
Replace with: (leave this blank)
Click the [replace all] button

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

This sounds like a cell formatting issue. Your numbers are entered as text.
Simply changing the cell format won't change the contents. Thus, why it only
works when you manually re-enter the data.

Here's something you can try.

Enter the number 1 into any blank cell.
Copy that cell.
Highlight your range of data to convert.
Paste Special and select "Multiply"
Click OK
Delete the 1 that you entered originally.

This multiplies all of your data by 1. Anything Excel can recognize as a
number will be converted to a number without changing the value.

HTH,
Elkar

 

[Guest]

Thank you!