IF Function
 

I am trying to write an if function where it will return letter A if it is <
50, letter B if it is 50 and <75 and letter C if it is 75 and <100. Does
anyone know how to write an if function using ranges? Nothing I try seems to
work


Thanks!

The following works for me.
=IF(A1<50,"A",IF(AND(A150,A1<75),"B",IF(AND(A1
75,A1<100),"C","Undefined")))

I included "Undefined" as the last option since the formula would not
know what to do with the numbers 50, 75, 100 or anything greater than
100. Not sure of your application but you might want to use = signs
where appropriate.

Another option would be to use a vlookup formula. Create a small table
that includes the starting value for each range and the appropriate
marker. Let's assume that 100 = C, 75 = B and 50 would equal A. The As
would start on 0, the Bs would start on 51 and the Cs would start on
76.
Col E F
Row 1 0 A
Row 2 51 B
Row 3 76 C

The formula vlookup(A1, E1:F3,2) would return the proper letter. This
is an inexact lookup. When you can create a table like this, this would
be the preferred method since you can easily change the ranges and the
markers (A,B,C). When I use the first approach, I rarely would hard
code the markers or the ranges in the formula. Instead I'd use cell
references so that I can change the markers and ranges in a table and
all of the formulas would update.

Hope that helps.

John

Hi
Just to improve on waxwings example.
=IF(A1<50,"A",IF(A1<75,"B",IF(A1<100,"C","Undefine d")))
HTH
Michael Mitchelson


=IF(A1<50,"A",IF(AND(A150,A1<75),"B",IF(AND(A1
75,A1<100),"C","Undefined")))

I included "Undefined" as the last option since the formula would not
know what to do with the numbers 50, 75, 100 or anything greater than
100. Not sure of your application but you might want to use = signs
where appropriate.

Another option would be to use a vlookup formula. Create a small table
that includes the starting value for each range and the appropriate
marker. Let's assume that 100 = C, 75 = B and 50 would equal A. The As
would start on 0, the Bs would start on 51 and the Cs would start on
76.
Col E F
Row 1 0 A
Row 2 51 B
Row 3 76 C

The formula vlookup(A1, E1:F3,2) would return the proper letter. This
is an inexact lookup. When you can create a table like this, this would
be the preferred method since you can easily change the ranges and the
markers (A,B,C). When I use the first approach, I rarely would hard
code the markers or the ranges in the formula. Instead I'd use cell
references so that I can change the markers and ranges in a table and
all of the formulas would update.

Hope that helps.

John

Good point Michael. I choose the "And" approach because it wasn't
clear in the original message if all values below 100 were included.
And allows the user to disregard certain values such as "50" which was
how the request was written but probably not what the user intended.

- John