Rank multiple factors skipping if below criteria

[j1987]

Hi,

I have a league table that I use to measure performance of staff.

Column A contains the Rank formula for 22 people ( 1-22 top to bottom)

Column B contains a varying sum of total amount of money collected (with most money collected going at top of the rank least at the bottom)

Column C contains a percentage figure for scoring KPI's

-----------------

I want to be able to Rank people in Column A based on most amount of money collected figure in column B

However if they score below 85% on their Column C percentage, I want the rank to skip that person and person below would take their rank.

e.g.

Rank Total Collected %
1 £1000 94.03%
2 £946 85.07%
£933 78.03%
3 £807 92.01%
£788 83.04%
4 £733 86.05%


Any help would be appreciated.

Thanks

 

[svendiamond]

You could use:

=IFERROR(RANK(IF(C2>=85,B2),$B$2:$B$7,0),"")

But that will return 1, 2, blank, 4, blank, 6... so 1 2 4 6 instead of 1 2 3 4... to deal with that I would just insert a column between A and B to "rank the rank" in column A, such as:

=IFERROR(RANK(A2,$A$2:$A$7,1),"")