IF(logical_test, may be a number or text

W

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
 
O

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

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Display number only if entered in a cell 4
Excel does not accept text format! 0
Payment calculation 1
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
LISTNUM + Mail Merge + INCLUDETEXT : Referencing Paragraph # Can't get to work 0
Multiple IF conditions 3
Identifying when all values in a range are the same. 7
Link a text box based on drop down box with four options MS Word 365 0

Top