Nested IF Statement multiple condtions

Excelwig

New Member
Joined
Oct 20, 2014
Messages
47
Hi can anyone help with this one

I working on a stats sheet and I have people with 2 different scores and a table for reference that gives them the ranking value. I'm not sure nested if are the way to go so any suggestions are welcome

A1 A2 A3 A4
Agent Name Score 1 Score 2 Rating
Agent 1 54% 112.50%
Agent 2 79% 105.50%
Agent 3 75% 130.50%

There reference table looks like

L1 L2 L3
Score 1 Score 2 Rating
85% 120% A
70% 110% B
60% 100% C
50% 90% D
40% 80% E


It needs to look at if there score 1 is 85% and score 2 is 120% then rating is A, else if score 1 is 70% and score 2 is 110% rating is B etc
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Excelwig

New Member
Joined
Oct 20, 2014
Messages
47
well if score 1 = 85% and score 2 = 120% then (rating) cell A4 shows A, and if score 1 = 70% and score 2 = 110% then (rating) cell A4 shows B
 

barjoman

Board Regular
Joined
Oct 29, 2014
Messages
99
What I meant is Agent 1 has 54% and 112.50%. What is the output of that? What about the other two agents?
 

Excelwig

New Member
Joined
Oct 20, 2014
Messages
47
I was hoping to get C, for agent 2 output C and agent 3 output.
 

barjoman

Board Regular
Joined
Oct 29, 2014
Messages
99
Unfortunately, your logic is still not clear to me. Why C? Please explain.
 

Excelwig

New Member
Joined
Oct 20, 2014
Messages
47
I was going Score 1 is a D and score 2 is a B so picking the one in-between the 2
 

barjoman

Board Regular
Joined
Oct 29, 2014
Messages
99
What do you do if Score 1 is A and Score 2 is B?

Have you considered simplifying this (if possible, of course) by first calculating an Average of Score 1 and Score 2, and then assigning a Rating based on that Average?

For example:

Ratings table
Average Score
Rating
100
A
90
B
80
C
70
D
60
E

<tbody>
</tbody>

Agent Name
Score 1
Score 2
Average Score
Rating
Agent 1
54%
112.50%
83.25%
C
Agent 2
79%
105.50%
92.25%
B
Agent 3
75%
130.50%
102.75%
A

<tbody>
</tbody>

If this works for you, then you can use a vlookup to achieve the result. Will it work?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,323
Messages
5,443,799
Members
405,251
Latest member
shanezer

This Week's Hot Topics

Top