IF statement

  • Thread starter Darrell Edwards
  • Start date
D

Darrell Edwards

I am trying to do an IF statement: =IF(F4>10,10,F4), that
works fine unless the user puts text into F4 instead of a
number. How do I correct it to allow them to put both
text and numbers? When they do put text into F4 I want to
return (0).

TFTH.
 
H

Harlan Grove

Darrell Edwards said:
I am trying to do an IF statement: =IF(F4>10,10,F4), that
works fine unless the user puts text into F4 instead of a
number. How do I correct it to allow them to put both
text and numbers? When they do put text into F4 I want to
return (0).
Click to expand...

=MIN(10,N(F4))
 
D

Domenic

Hi Frank,

I'm sure you meant ISTEXT and not ISNUMBER.

By the way, nice to see to back!
 
D

Dave Peterson

=min(10,f4)

=min() ignores cells with text.

Darrell said:
I am trying to do an IF statement: =IF(F4>10,10,F4), that
works fine unless the user puts text into F4 instead of a
number. How do I correct it to allow them to put both
text and numbers? When they do put text into F4 I want to
return (0).

TFTH.
Click to expand...
 
G

Guest

Works perfect!
Now I have another formula =IF(F4>10,F4-10,0) that
references the formula that you just provide to me. =IF
(F4>10,F4-10,0) returns #value! how do I fix it?

Thanks
 
F

Frank Kabel

-----Original Message-----
....

I'd guess you meant

=IF(ISTEXT(F4),0,MIN(F4,10))
Click to expand...

Hi Harlan
meant
=IF(ISNUMBER(F4),MIN(F4,10),0)

But using the N function (as shown in your example9 is
definetly better :)
 
H

Harlan Grove

Dave Peterson said:
=min(10,f4)

=min() ignores cells with text.
Click to expand...

And the formula above returns 10 rather than 0 because it does ignore text.
See OP's specs.
 
H

Harlan Grove

Now I have another formula =IF(F4>10,F4-10,0) that
references the formula that you just provide to me. =IF
(F4>10,F4-10,0) returns #value! how do I fix it?
Click to expand...

=MAX(0,N(F4)-10)
 
Top