Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do create a formula to evalute a # to return 1 of 4 conditions
I have to evaluate a single number in a single cell and return a value based
on that number. How do I build a formula to do the following? If the number is < 500, I need to return "Non-Target" If the number is = 500 but < 1500, I need to return "Low" If the number is = 1500 but < 3500, I need to return "Medium" If the number is = 3500, I need to return a value of "High" How do I build this formula |
#2
|
|||
|
|||
One way, assume the number in question is in cell A1
=IF(A1="","",VLOOKUP(A1,{0,"Non Target";500,"Low";1500,"Medium";3500,"High"},2)) I assumed that the number can't be less than zero -- Regards, Peo Sjoblom "Larry" wrote in message ... I have to evaluate a single number in a single cell and return a value based on that number. How do I build a formula to do the following? If the number is < 500, I need to return "Non-Target" If the number is = 500 but < 1500, I need to return "Low" If the number is = 1500 but < 3500, I need to return "Medium" If the number is = 3500, I need to return a value of "High" How do I build this formula |
#3
|
|||
|
|||
Peo, you are Fantastic!!!.
I understand some of what you did, but can you explain, in simple terms, what is occurring in this formula? Thanks LB "Peo Sjoblom" wrote: One way, assume the number in question is in cell A1 =IF(A1="","",VLOOKUP(A1,{0,"Non Target";500,"Low";1500,"Medium";3500,"High"},2)) I assumed that the number can't be less than zero -- Regards, Peo Sjoblom "Larry" wrote in message ... I have to evaluate a single number in a single cell and return a value based on that number. How do I build a formula to do the following? If the number is < 500, I need to return "Non-Target" If the number is = 500 but < 1500, I need to return "Low" If the number is = 1500 but < 3500, I need to return "Medium" If the number is = 3500, I need to return a value of "High" How do I build this formula |
#4
|
|||
|
|||
=IF(A1=0,0,IF(A1<500,"non
target",IF(A1<1500,"low",IF(A1<3500,"medium","high ")))) because your sequence is "logical"you can use a simple "if",because it moves to the next argument as soon as the previous one is not true. You may or may not need the initial if( a1 is o argument,it will display high if cell a1 is empty or 0 without it -- paul remove nospam for email addy! "Larry" wrote: I have to evaluate a single number in a single cell and return a value based on that number. How do I build a formula to do the following? If the number is < 500, I need to return "Non-Target" If the number is = 500 but < 1500, I need to return "Low" If the number is = 1500 but < 3500, I need to return "Medium" If the number is = 3500, I need to return a value of "High" How do I build this formula |
#5
|
|||
|
|||
vlookup is a function that you use to look at a column of information and
return a value from the same row a specified number of columns across to the right normally you would have a little table 0 non target 500 low 1500 medium 3500 high but because its so simple peo used an array constant to represent the table. so his formula says if a1 is blank return blank otherwise look in the first column for a value and return the value next to it (in the second column) -- paul remove nospam for email addy! "Larry" wrote: Peo, you are Fantastic!!!. I understand some of what you did, but can you explain, in simple terms, what is occurring in this formula? Thanks LB "Peo Sjoblom" wrote: One way, assume the number in question is in cell A1 =IF(A1="","",VLOOKUP(A1,{0,"Non Target";500,"Low";1500,"Medium";3500,"High"},2)) I assumed that the number can't be less than zero -- Regards, Peo Sjoblom "Larry" wrote in message ... I have to evaluate a single number in a single cell and return a value based on that number. How do I build a formula to do the following? If the number is < 500, I need to return "Non-Target" If the number is = 500 but < 1500, I need to return "Low" If the number is = 1500 but < 3500, I need to return "Medium" If the number is = 3500, I need to return a value of "High" How do I build this formula |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can create a formula to add data from 1 worksheet to another? | Excel Discussion (Misc queries) | |||
Why does my formula return zero? | Excel Discussion (Misc queries) | |||
how to create a multiple conditional formula | Excel Discussion (Misc queries) | |||
create a chart with a formula | Charts and Charting in Excel | |||
How do I create a formula in Excel that will countif or sumif bef. | Excel Worksheet Functions |