Simple formula not working

[ato_zee]

=IF(F4=1,H4,-1)
Nearly all cells have numeric values, and for
those with a value of 1 it works correctly,
where the values are numeric it also works
correctly.

BUT - a few cells have 1 or 2 alphabetic
characters, which means they are not equal
to 1, so should yield a value of -1.

For example I want XY, as it is clearly not 1,
so condition F4=1 fails, to evaluate to -1

The referenced cells are formatted
number.

Is Excel flawed or am I missing something?

Excel 2002 SP3

 

[Erny]

I would use:
=IF(VALUE(F4)=1,H4,-1)

Kind regards,
Erny

 

[Niek Otten]

That will return an error if there are alphabetics in F4.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I would use:
| =IF(VALUE(F4)=1,H4,-1)
|
| Kind regards,
| Erny
|
| | >
| > =IF(F4=1,H4,-1)
| > Nearly all cells have numeric values, and for
| > those with a value of 1 it works correctly,
| > where the values are numeric it also works
| > correctly.
| >
| > BUT - a few cells have 1 or 2 alphabetic
| > characters, which means they are not equal
| > to 1, so should yield a value of -1.
| >
| > For example I want XY, as it is clearly not 1,
| > so condition F4=1 fails, to evaluate to -1
| >
| > The referenced cells are formatted
| > number.
| >
| > Is Excel flawed or am I missing something?
| >
| > Excel 2002 SP3
|
|

 

[Dave Peterson]

Any chance you have calculation set to manual
(on Tools|Options|calculation tab)?

 

[ato_zee]

Any chance you have calculation set to manual
(on Tools|Options|calculation tab)?

That's set to Auto.
But problem seems to occur if F4 is entered before
the formula.
If I retype the values in F4 formula works OK,
even though the values in the formula bar don't
change.
I'll try the VALUE function and dig a bit deeper,
if that fails to cure it.
Many thanks