FIND() a specific rank based on rules!

[TheRiddler]

Hello everyone,

I'm trying to build a reporting spreadsheet so I can manage some of my team members grades (which can be from 1 to 10).
This will help me with an AVERAGE() formula formatted to show a percentage of satisfaction from clients/colleagues

Now, I have the following table:

The small table located on the right side (columns G&H) contains the rules for the ranking I'm interested and gaining.

What I want to do:

I want to be able to use a formula that will analyze the numbers in column C and based on the rules, will automatically insert in Column D the corresponding ranks.


I've researched the web before posting (including this forum), but I sill don't have any idea on how I can do it and what formula I can use

Any ideas ?

My best regards,
Michael (Riddler)

P.S.: In the picture, in column D I've placed the ranks manually (one by one) so I can use the AVERAGE() command (the 33.33% is the cell with that command).

 

[shg]

All of those grades appear to be for the same agent ...

You could use a lookup formula to retrieve the value for each grade, and then use a pivot table to see the ranks by summing the grades.

 

[TheRiddler]

Hello shg and thank you for the quick reply!

I'll give it a shot and see if it works!

I'm interested in a formula that will verify the number and based on those rules (for the grades), it will insert 1 of the 3 ranks

 

[shg]

=IF(MEDIAN(1, 10, C2)=C2, LOOKUP(C2, {1,7,9}, {-1,0,1}), "Oops!")

 

[TheRiddler]

@shg: thank you!

It works la charm, exactly like I desire

 

[shg]

You're welcome.