Multiple If-Then answers/results

[Wendy]

Hi,

Sorry if this is a really basic question, but I could
really use some help on it.

I have a list of people that are given a rating of an A,
B, C or D, based on the numeric range given in a certain
field, and I need Excel to calculate what rating is
applicable.

For example:

If D2 >45 and D2 <60 = A
If D2 >30 and D2 <44 = B
If D2 >15 and D2 <15 = C
If D2 >0 and D2<14 = D


I know how to do a single calculation, based on a single
figure, but I'm not sure how to put it all together to
cover the above requirements.

All help gratefully received and appreciated.

Thanks,

Wendy

 

[Norman Harker]

Hi Wendy!

I have problems with errors in your data but get:

=VLOOKUP(D2,$H$1:$I$4,2)

In H1:I4 I have:

0 D
15 C
30 B
45 A


You may have to change the figures for the grade boundaries but this
is one joy of using a separate range for the table rather than putting
it in your formula.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[JE McGimpsey]

You could use:

=LOOKUP(D2,{0,15,30,45},{"D","C","B","A"})

Note that your specification has holes - i.e. what should a score
between 44 and 45 (inclusive) be? I assumed the following:

D2 >= 45 A
D2 >= 30, D2 < 45 B
D2 >= 15, D2 < 30 C
D2 >= 0, D2 < 15 D

 

[Andy B]

Wendy

There are a few ways to do this. Here is one option:
http://www.cpearson.com/excel/excelF.htm#Grades

Hope this helps.
Andy.

 

[Bob Phillips]

Wendy,

Your details don't allow for the exact values 15, 30, 45 etc. so I have made
an assumption

=IF(D2<15,"D",IF(D2<30,"C",IF(D2<45,"B","A")))

change if not quite aligned correctly

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Jason Morin]

You don't specify what the rating would be if the
numerical value were 45, 30, or 15. Try:

=LOOKUP(D2,{0,15,30,45},{"D","C","B","A"})

where...

=45 is A
=30 and <45 is B
=15 and <30 is C

<15 is D

HTH
Jason
Atlanta, GA

 

[Wendy]

Hi all,

Thanks for your help on this, Bob's solution below works
great, but I've also kept all the other solutions as well
to look at for a future project.

Cheers,

Wendy