Multiple If-Then answers/results

  • Thread starter Thread starter Wendy
  • Start date Start date
W

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
 
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.
 
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
 
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)
 
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
Click to expand...
<15 is D

HTH
Jason
Atlanta, GA
 
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
 
Back
Top