IF(logical_test, may be a number or text

[workworkwork]

The calculated value in cell L20 may be a number or a customized error
string/text message.
In cell D20 I need to display the text in in L20 if that is the result, or
if a number is calculated and number calculated is greater than 500, display
500, of if a number is calulated and it is less than 150, display 150, or
display the calulated number (where it is 150 to 500).

When the result in L20 is the custom error message, and I use this in cell
D20,
=IF(L20>500,500,IF(L20<150,150,L20))
excel displays 500, not the error text/message result from cell L20

Thank you

 

[Mike H]

Hi,

=IF(ISTEXT(L20),L20,IF(L20>500,500,IF(L20<150,150,L20)))

Mike

 

[okey]

The calculated value in cell L20 may be a number or a customized error
string/text message.
In cell D20 I need to display the text in in L20 if that is the result, or
if a number is calculated and number calculated is greater than 500, display
500, of if a number is calulated and it is less than 150, display 150, or
display the calulated number (where it is 150 to 500).

When the result in L20 is the custom error message, and I use this in cell
D20,
=IF(L20>500,500,IF(L20<150,150,L20))
excel displays 500, not the error text/message result from cell L20

Thank you

I just sitting here waiting for an answer myself.
What about using ISNUMBER
see IS Functions

 

[barry houdini]

Any text is deemed to be greater than any number.....so when L20 is
text it satisfies the first condition and 500 is returned. Best to use
an IF to check if L20 is a number or not

=IF(ISNUMBER(L20),MEDIAN(0,500,L20),L20)

regards, barry

 

[workworkwork]

Thank you!

Hi,

=IF(ISTEXT(L20),L20,IF(L20>500,500,IF(L20<150,150,L20)))

Mike