Nested IF Statement multiple condtions

[Excelwig]

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

 

[barjoman]

What is the expected output for your example above?

 

[Excelwig]

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]

What I meant is Agent 1 has 54% and 112.50%. What is the output of that? What about the other two agents?

 

[Excelwig]

I was hoping to get C, for agent 2 output C and agent 3 output.

 

[barjoman]

Unfortunately, your logic is still not clear to me. Why C? Please explain.

 

[Excelwig]

I was going Score 1 is a D and score 2 is a B so picking the one in-between the 2

 

[barjoman]

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[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Average Score
[/TD]
[TD]Rating
[/TD]
[/TR]
[TR]
[TD]100
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]90
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]80
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]70
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]60
[/TD]
[TD]E
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Agent Name
[/TD]
[TD]Score 1
[/TD]
[TD]Score 2
[/TD]
[TD]Average Score
[/TD]
[TD]Rating
[/TD]
[/TR]
[TR]
[TD]Agent 1
[/TD]
[TD]54%
[/TD]
[TD]112.50%
[/TD]
[TD]83.25%
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]Agent 2
[/TD]
[TD]79%
[/TD]
[TD]105.50%
[/TD]
[TD]92.25%
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]Agent 3
[/TD]
[TD]75%
[/TD]
[TD]130.50%
[/TD]
[TD]102.75%
[/TD]
[TD]A
[/TD]
[/TR]
</tbody>[/TABLE]

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