Ranking with multiple criteria

[Bithsa]

Hi,

I've seen a thread with a similar question but the solution didn't work in my situation. I have a table that looks similar to this:

Name Role Month Avg Rank
Joe A 1/1/2016 97.0%
Tim A 1/1/2016 94.1%
Ann B 1/1/2016 96.5%
Bob B 1/1/2016 94.0%
Sue C 1/1/2016 85.0%
Joe A 2/1/2016 100.0%
Tim A 2/1/2016 100.0%
Ann B 2/1/2016 99.0%
Bob B 2/1/2016 98.7%
Sue C 2/1/2016 100.0%


I am looking for a formula that will rank all of the employees within each role against eachother, and rankings that will restart when the next month comes. The rank is based on an average task completion rate. I am having trouble finding a formula that will rank on these criteria...one of my peers told me that there is a way to do it with the OFFSET() function, if anybody can find a way to do it that way.

If anything, please point me in the right direction! Thank you!

 

[63falcondude]

Can you include desired Rank numbers as well?

 

[Bithsa]

Rank numbers should correspond with the number of employees in the role. For example role A & B have 2 employees, therefore employees in role A will be ranked 1-2 based on their average percentile in column D. Highest percentage receives rank 1 and lowest receives rank n...

This is just a smaller example of a large spreadsheet I am working on. There are at least 8 employees in each role in the master spreadsheet...so they would be ranked 1-8 and so on.

does this make sense?

 

[63falcondude]

Try this in E2

=COUNTIFS(B$2:B2,B2,C$2:C2,C2,D$2:D2,">="&D2)

 

[Bithsa]

This formula returns a rank of "0" for most of the entries! There must be something that needs to be added to it...hmm

 

[63falcondude]

This formula returns a rank of "0" for most of the entries! There must be something that needs to be added to it...hmm

It works for your example where "Name" is in A1. Make sure to get it working for the example before trying to get it to work on your actual data.

[TABLE="width: 320"]
<tbody>[TR]
[TD="width: 64, align: center"]Name[/TD]
[TD="width: 64, align: center"]Role[/TD]
[TD="width: 64, align: center"] Month[/TD]
[TD="width: 64, align: center"] Avg[/TD]
[TD="width: 64, align: center"] Rank[/TD]
[/TR]
[TR]
[TD="align: center"]Joe[/TD]
[TD="align: center"]A[/TD]
[TD="class: xl63, align: center"]1/1/2016[/TD]
[TD="class: xl64, align: center"]97.00%[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Tim[/TD]
[TD="align: center"]A[/TD]
[TD="class: xl63, align: center"]1/1/2016[/TD]
[TD="class: xl64, align: center"]94.10%[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Ann[/TD]
[TD="align: center"]B[/TD]
[TD="class: xl63, align: center"]1/1/2016[/TD]
[TD="class: xl64, align: center"]96.50%[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Bob[/TD]
[TD="align: center"]B[/TD]
[TD="class: xl63, align: center"]1/1/2016[/TD]
[TD="class: xl64, align: center"]94.00%[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Sue[/TD]
[TD="align: center"]C[/TD]
[TD="class: xl63, align: center"]1/1/2016[/TD]
[TD="class: xl64, align: center"]85.00%[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Joe[/TD]
[TD="align: center"]A[/TD]
[TD="class: xl63, align: center"]2/1/2016[/TD]
[TD="class: xl64, align: center"]100.00%[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Tim[/TD]
[TD="align: center"]A[/TD]
[TD="class: xl63, align: center"]2/1/2016[/TD]
[TD="class: xl64, align: center"]100.00%[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Ann[/TD]
[TD="align: center"]B[/TD]
[TD="class: xl63, align: center"]2/1/2016[/TD]
[TD="class: xl64, align: center"]99.00%[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Bob[/TD]
[TD="align: center"]B[/TD]
[TD="class: xl63, align: center"]2/1/2016[/TD]
[TD="class: xl64, align: center"]98.70%[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Sue[/TD]
[TD="align: center"]C[/TD]
[TD="class: xl63, align: center"]2/1/2016[/TD]
[TD="class: xl64, align: center"]100.00%[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Bithsa]

Okay, it works for the sample that I gave and it works for the first subgroup in my actual data, but when I try to drag it down when it gets to the next role it does not start ranking at "1" and there are a lot of ties. Any suggestions?

 

[63falcondude]

Okay, it works for the sample that I gave and it works for the first subgroup in my actual data, but when I try to drag it down when it gets to the next role it does not start ranking at "1" and there are a lot of ties. Any suggestions?

Can you create a sample where it doesn't work and share it here?

 

[Bithsa]

This resembles the actual data more closely...some of the averages are filled in with blanks, and a lot of people have the same percentage in the actual table. I'm not sure if that may have an affect on the ranks. I used the exact formula you created.

[table="width: 500"]
[tr]
[td]Name[/td]
[td]Role[/td]
[td]Month[/td]
[td]Avg[/td]
[td]Rank[/td]
[/tr]
[tr]
[td]John[/td]
[td]A[/td]
[td]1/1/2016[/td]
[td]97.0%[/td]
[td]1[/td]
[/tr]
[tr]
[td]Sue[/td]
[td]A[/td]
[td]1/1/2016[/td]
[td]94.1%[/td]
[td]2[/td]
[/tr]
[tr]
[td]Bob[/td]
[td]A[/td]
[td]1/1/2016[/td]
[td] [/td]
[td]0[/td]
[/tr]
[tr]
[td]Carol[/td]
[td]A[/td]
[td]1/1/2016[/td]
[td]100.0%[/td]
[td]1[/td]
[/tr]
[tr]
[td]Sam[/td]
[td]A[/td]
[td]1/1/2016[/td]
[td]100.0%[/td]
[td]2[/td]
[/tr]
[tr]
[td]George[/td]
[td]B[/td]
[td]1/1/2016[/td]
[td]99.0%[/td]
[td]1[/td]
[/tr]
[tr]
[td]Alan[/td]
[td]B[/td]
[td]1/1/2016[/td]
[td]98.7%[/td]
[td]2[/td]
[/tr]
[tr]
[td]Cam[/td]
[td]B[/td]
[td]1/1/2016[/td]
[td]100.0%[/td]
[td]1[/td]
[/tr]
[/table]

 

[63falcondude]

This resembles the actual data more closely...some of the averages are filled in with blanks, and a lot of people have the same percentage in the actual table. I'm not sure if that may have an affect on the ranks. I used the exact formula you created.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Role[/TD]
[TD]Month[/TD]
[TD]Avg[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]A[/TD]
[TD]1/1/2016[/TD]
[TD]97.0%[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]A[/TD]
[TD]1/1/2016[/TD]
[TD]94.1%[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]A[/TD]
[TD]1/1/2016[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Carol[/TD]
[TD]A[/TD]
[TD]1/1/2016[/TD]
[TD]100.0%[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]A[/TD]
[TD]1/1/2016[/TD]
[TD]100.0%[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]B[/TD]
[TD]1/1/2016[/TD]
[TD]99.0%[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Alan[/TD]
[TD]B[/TD]
[TD]1/1/2016[/TD]
[TD]98.7%[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Cam[/TD]
[TD]B[/TD]
[TD]1/1/2016[/TD]
[TD]100.0%[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

What outcome(rank) do you expect for someone without an Avg? Also, what rank should Carol and Sam have since they both have 100%?