#VALUE returned in otherwise proper formula

[immyjimmy]

Greeting EXCELent members;

I have a co-worker running Excel XP and she's getting a #VALUE message in a cell that has the formula =G5*1.25. The value of G5 is 62.5, so there should be no error. But the message "wrong data type" is displayed. The cells are formatted for 'general'. One other thing that seems odd: When the period key is used, a comma is displayed. Any ideas?

Thanks in advance,
Jim

 

[DRJ]

Maybe some of the cells are text and not numbers. Put 0 in a cell and copy it. Select all the cells that have numbers. Goto Edit | PasteSpecial | Add

That should make them all into numbers

 

[PaddyD]

As DRJ has pointed out, sometimes you can get problems when values that look like numbers ( & are even formatted as such) are actually held as text. (a quick isnumber() check on the cell in question would tell you). The method he described is one effective way of fixing this. However, the mathematical operation in the calculation should be sufficient to perform the coercion on its own. (Indeed, it should work even if there were blanks in the cell as well...).

So - problem is indeed odd! How was the value entered originally? Is it just this cell / calculation?

 

[Mark W.]

In fact, in many circumstances Excel's arithmetic operators will coerce a text representation of a numeric value and return a result other than #VALUE!. For example...

="62.5"*1.25 results in 78.125

This even works if there are leading or trailing spaces (ANSI code 32)...

=" 62.5 "*1.25 results in 78.125

The trouble arises when there are ANSI characters other than spaces which appear to be spaces (e.g., ANSI code 160)...

=("62.5"&CHAR(160))*1.25 results in #VALUE!

This can arise if you pasted your values from an html page.

Is this the case? If so, you'll need to do a Find/Replace to rid yourself of these ANSI characters.

 

[Mark W.]

...One other thing that seems odd: When the period key is used, a comma is displayed. Any ideas?...

Check her Regional Settings (Options). Make sure that it's set for English (United States) or that the Decimal symbol on the Numbers tab hasn't been changed to ",".

In some European countries (eg., Finland) commas are used for decimals.