IF statement

[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.

 

[Frank Kabel]

Hi
try
=IF(ISNUMBER(F4),0,MIN(F4,10))

 

[Harlan Grove]

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).

=MIN(10,N(F4))

 

[Domenic]

Hi Darrel,

try,

=IF(ISTEXT(F4),0,IF(F4>10,10,F4))

Hope this helps!

 

[Harlan Grove]

try
=IF(ISNUMBER(F4),0,MIN(F4,10))

....

I'd guess you meant

=IF(ISTEXT(F4),0,MIN(F4,10))

 

[Domenic]

Hi Frank,

I'm sure you meant ISTEXT and not ISNUMBER.

By the way, nice to see to back!

 

[Dave Peterson]

=min(10,f4)

=min() ignores cells with text.

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.

 

[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

 

[Frank Kabel]

-----Original Message-----

....

I'd guess you meant

=IF(ISTEXT(F4),0,MIN(F4,10))

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

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

 

[Harlan Grove]

=min(10,f4)

=min() ignores cells with text.

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

 

[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?

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

 

[Frank Kabel]

Hi
try
=IF(N(F4)>10,F4-10,0)

or try
MAX(N(F4)-10,0)

 

[Dave Peterson]

Oops. I misread the question.

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