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]

Try the following formula on the sample that you shared in post #9. This will allow ties and count any blanks as the worst (highest number) rank.

E2 =SUMPRODUCT(($B$2:$B$9=B2)*($C$2:$C$9=C2)*(D2<$D$2:$D$9))+1