Rank

[d0hb0y]

Using the data in the table below, I would like to determine the rank of the top performers. What is the best way to Rank them or can it be done? All this data is being pulled from a larger dataset.

 

[SanjayGMusafir]

What is the best way to Rank them or can it be done?

You didn't mention the criteria on the basis of which you want them to rank. Once the criteria is clear we can find different ways to rank.

Also it is important how you are pulling this data from a large data set? Do throw some light on that too. that shall effect the possible solution.

 

[d0hb0y]

You didn't mention the criteria on the basis of which you want them to rank. Once the criteria is clear we can find different ways to rank.

Also it is important how you are pulling this data from a large data set? Do throw some light on that too. that shall effect the possible solution.

All criteria mentioned. Apologies.

Cell C7 has a month to make the below dynamic

Response Time Column - IFERROR(AVERAGEIFS(DataTble[Response Time],DataTble[Officer],$B10, DataTble[FY],$C$7),0)
Call Taken - SUMPRODUCT(--(DataTble[Officer]&DataTble[FY]=$B10&$C$7))
Call Volunteering - SUMIFS(DataTble[Call Volunteer],DataTble[FY],$C$7,DataTble[Officer],$B10)
Self Initiated Calls - SUMIFS(DataTble[Found Occurrence],DataTble[FY],$C$7,DataTble[Officer],$B10)
Quickest Response - MINIFS(DataTble[Response Time],DataTble[FY],$C$7,DataTble[Officer],$B10)
Slowest Response - MAXIFS(DataTble[Response Time],DataTble[FY],$C$7,DataTble[Officer],$B10)
Incident Reports - SUMIFS(DataTble[Incident Reports],DataTble[FY],$C$7,DataTble[Officer],$B10)

 

[SanjayGMusafir]

All criteria mentioned. Apologies.

Since it's your business or organization you have to define a way to Rank people. We can only help you with the formulae (tools) needed to get the desired results out of your data.

Cell C7 has a month to make the below dynamic

Can't find any cell address(es) in the screenshot...

Response Time Column - IFERROR(AVERAGEIFS(DataTble[Response Time],DataTble[Officer],$B10, DataTble[FY],$C$7),0)
Call Taken - SUMPRODUCT(--(DataTble[Officer]&DataTble[FY]=$B10&$C$7))
Call Volunteering - SUMIFS(DataTble[Call Volunteer],DataTble[FY],$C$7,DataTble[Officer],$B10)
Self Initiated Calls - SUMIFS(DataTble[Found Occurrence],DataTble[FY],$C$7,DataTble[Officer],$B10)
Quickest Response - MINIFS(DataTble[Response Time],DataTble[FY],$C$7,DataTble[Officer],$B10)
Slowest Response - MAXIFS(DataTble[Response Time],DataTble[FY],$C$7,DataTble[Officer],$B10)
Incident Reports - SUMIFS(DataTble[Incident Reports],DataTble[FY],$C$7,DataTble[Officer],$B10)

It is clear now that the data you have shared is a summary data of a master data. No apparent challenge to work with that.

 

[d0hb0y]

Since it's your business or organization you have to define a way to Rank people. We can only help you with the formulae (tools) needed to get the desired results out of your data.

Can't find any cell address(es) in the screenshot...

It is clear now that the data you have shared is a summary data of a master data. No apparent challenge to work with that.

Thanks.

 

[Skrej]

Do you wish to consider all the criteria in your ranking system? If so, is does each criteria carry equal weight, or is one particular criteria more important than others?

I think you need to come up with some base mathematical (not Excel) formula first, then write the Excel formula for that.

For example, maybe a response time under 30 seconds is worth X points, under a minute is worth a little less, and over a minute is worth the least. Then perhaps add the number of calls taken, plus the average response time. Maybe the response time value is worth twice the weight of the number of calls taken, etc. That gives a base number for each candidate, which you can then rank.

Of course, my example is just to illustrate the point - you'd have to decide what to include in your ranking points and how you want to weigh them.

 

[d0hb0y]

Do you wish to consider all the criteria in your ranking system? If so, is does each criteria carry equal weight, or is one particular criteria more important than others?

I think you need to come up with some base mathematical (not Excel) formula first, then write the Excel formula for that.

For example, maybe a response time under 30 seconds is worth X points, under a minute is worth a little less, and over a minute is worth the least. Then perhaps add the number of calls taken, plus the average response time. Maybe the response time value is worth twice the weight of the number of calls taken, etc. That gives a base number for each candidate, which you can then rank.

Of course, my example is just to illustrate the point - you'd have to decide what to include in your ranking points and how you want to weigh them.

Skrej,
That was something I was contemplating doing as I found it difficult to just rank this criteria as it stands.