Text Cell reference

[Frick]

in cell c3 I have a formula that returns texts like ABC if one
criteria is met or a number if another criteria is met.

In cel c4 I set a simple formula If(c3>0,1,0). My problem is that for
some reason c4 is returning a value of 1, when it really should be
returning a 0 as there is no number in c3, only ABC.

What am I missing here???

SEF

 

[Tim Otero]

For the purposes of the IF statement, text is >0. You can use the ISNUMBER
statement along with the IF:

=IF(AND(ISNUMBER(A1),A1>0), 1,0)
tim

 

[Myrna Larson]

What you are missing is that when comparing text and a number, Excel uses the same rules that it
does when sorting: numbers come first, then text. Any text is "greater than" any number.

You need to change your formula to check for a number in C3, i.e.

=IF(ISNUMBER(C3),1,0)

 

[Aladin Akyurek]

A bit fancy, but not unreasoble...

=--(N(C3)<>0)

 

[Tim Otero]

Hi Frick,

The solutions by Aladin and Myrna will return 1 if the cell is a number. I
believe what you wanted is for the formula to return 1 only if the number is

0. That's the reason for the extra verbiage in my solution.

tim

 

[Harlan Grove]

The solutions by Aladin and Myrna will return 1 if the cell is a number. I
believe what you wanted is for the formula to return 1 only if the number is ...

...

If Tim's correct, Aladin's formula could be shortened to

=--(N(C3)>0)

 

[Frick]

The formula does not seem to work. I believe because I was not
totally clear in what it should do.

Across a number of col's in row 3 the col's have either negative
numbers, text, and positve numbers.

What I need is a formula that returns 0 for all negative number and
text cells and if the cell is a positve number, returns a 1

Thanks for all the help so far.

Frick

 

[Tim Otero]

Damn Harlan,

You're just too good!

tim

...
..

If Tim's correct, Aladin's formula could be shortened to

=--(N(C3)>0)

 

[Tim Otero]

ok, now I have to add, "funny"

...
..
..

Clever, not good. Definitely not good.

 

[Frick]

Tim,

Your formula worked because of the negative numbers.

Frick

 

[Tim Otero]

Cool,

glad to hear that...I believe Harlan's solution works, as well because he
took off the "<"

tim

 

[Aladin Akyurek]

Tim,

Your formula worked because of the negative numbers.

[...]

Yes, it should. If >0 values are of interest,

=--(N(C3)>0)

will be faster.