Text Cell reference

  • Thread starter Thread starter Frick
  • Start date Start date
F

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
 
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
 
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)
 
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.
Click to expand...

tim
 
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 ...
Click to expand...
...

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

=--(N(C3)>0)
 
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
 
Cool,

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

tim
 
Back
Top