isnumber won't work

  • Thread starter Thread starter papa
  • Start date Start date
P

papa

I have a simple function
=isnumber(g53)
The result is consistently "FALSE"
G53 contains "5". I have tried formatting the cell to
text, number, general. None of them seem to matter.

Is there some generic setting for the sheet that would be
causeing this to be returning a result of "False"?

I thought that the method of getting "5" in the cell was
the problem so I have replaced the function that
originated the "5" with a just the value. That did not
affect it.

Any help would be greatly appreciated.
TIA
Papa
 
papa said:
I have a simple function
=isnumber(g53)
The result is consistently "FALSE"
G53 contains "5". I have tried formatting the cell to
text, number, general. None of them seem to matter.
Click to expand...
....

Number formatting doesn't affect the contents of the cell, only how it's
displayed. It's likely this 'number' is actually text. You could convert it
to a number by copying a blank cell, then selecting this cell and using Edit
Paste Special, Values *and* Add. Alternatively, you could use the formula
=ISNUMBER(-G53)

Is there some generic setting for the sheet that would be
causeing this to be returning a result of "False"?
No.

I thought that the method of getting "5" in the cell was
the problem so I have replaced the function that
originated the "5" with a just the value. That did not
affect it.
Click to expand...

If all you did was replace a formula returning text with its value, then the
cell would still evaluate as text. There may be a way to fix the formula,
but you'd have to show us what it looks like.
 
I have a simple function
=isnumber(g53)
The result is consistently "FALSE"
G53 contains "5". I have tried formatting the cell to
text, number, general. None of them seem to matter.

Is there some generic setting for the sheet that would be
causeing this to be returning a result of "False"?

I thought that the method of getting "5" in the cell was
the problem so I have replaced the function that
originated the "5" with a just the value. That did not
affect it.

Any help would be greatly appreciated.
TIA
Papa
Click to expand...

I don't know if this is your problem, but the sequence of changing things is
important. The entry will take on the characteristic of the cell format AT THE
TIME the entry was made. Subsequent changes in cell format will not
necessarily change the type of value.

So if the cell was formatted as TEXT, and then the <5> entered, changing the
format will not change the <5> to a NUMBER.

However, if at the time the entry was made, the cell was formatted as GENERAL,
then changing the format back forth TEXT-GENERAL will change the <5> from
number to text.


--ron
 
Ron Rosenfeld said:
I don't know if this is your problem, but the sequence of changing things
is important. The entry will take on the characteristic of the cell
format AT THE TIME the entry was made. Subsequent changes in cell format
will not necessarily change the type of value.
Click to expand...
....

I'd restate this: only if the cell were formatted as TEXT at the time of
entry would typing 5 and pressing [Enter] generate text equal to "5" in the
cell. With any other number format, the entry would be the number 5.

As for the last sentence, strenghten it: no amount of format changing will
alter the contents of a cell or its type.
However, if at the time the entry was made, the cell was formatted as
GENERAL, then changing the format back forth TEXT-GENERAL will change
the <5> from number to text.
Click to expand...

Maybe this works differently in more recent versions, but in Excel 97, if
cell A1 is formatted as General, type 5 and press [Enter]. In B1 enter
=ISNUMBER(A1). It returns TRUE. In C1 enter =N(A1). It returns 5. Now format
A1 as TEXT. Press [Ctrl]+[Alt]+[F9] to force recalculation. What do B1 and
C1 show? Now, with A1 still formatted as TEXT, type 5 and press [Enter]. Now
what do B1 and C1 show?
 
Ron Rosenfeld said:
I don't know if this is your problem, but the sequence of changing things
is important. The entry will take on the characteristic of the cell
format AT THE TIME the entry was made. Subsequent changes in cell format
will not necessarily change the type of value.
Click to expand...
...

I'd restate this: only if the cell were formatted as TEXT at the time of
entry would typing 5 and pressing [Enter] generate text equal to "5" in the
cell. With any other number format, the entry would be the number 5.

As for the last sentence, strenghten it: no amount of format changing will
alter the contents of a cell or its type.
However, if at the time the entry was made, the cell was formatted as
GENERAL, then changing the format back forth TEXT-GENERAL will change
the <5> from number to text.
Click to expand...

Maybe this works differently in more recent versions, but in Excel 97, if
cell A1 is formatted as General, type 5 and press [Enter]. In B1 enter
=ISNUMBER(A1). It returns TRUE. In C1 enter =N(A1). It returns 5. Now format
A1 as TEXT. Press [Ctrl]+[Alt]+[F9] to force recalculation. What do B1 and
C1 show? Now, with A1 still formatted as TEXT, type 5 and press [Enter]. Now
what do B1 and C1 show?
Click to expand...

There must have been something not clean about my initial trials as, with a new
instance of Excel and a clean sheet, the 5 ISNUMBER(A1) remains TRUE (although
the 5 does jump back and forth between being left and right justified).


--ron
 
Back
Top