How do create a formula to evalute a # to return 1 of 4 conditions

L

Larry

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
 
P

Peo Sjoblom

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
 
L

Larry

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
 
P

paul

=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
 
P

paul

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