Rank based on lowest score

[ashwithonline]

Hello All,


I'm trying to devise a formula which will give me a score. generally the score is directly proportional which means if a person has more production volume he has more score. but i want a formula which will give the score based on AHT which means lower the AHT highest is the score.

thanks to all the people....out here

 

[Ashhu]

Welcome to the forum
Sample sheet will be more helpfull if you attach.

 

[ashwithonline]

Here is the file..
one of the column has names and the other has AHT..now in the third column i need a score basis the AHT but in a inverse fashion...i mean the lowest AHT should have the highest score..

thanks in advance

 

[NARAYANK991]

Hi ,

Can you not use the RANK function ? This has two values for the third parameter , 0 implies ranking in descending order , while 1 implies ranking in ascending order.

=RANK(B2,$B$2:$B$15,1)

=RANK(B2,$B$2:$B$15,0)

Enter either of these in C2 and copy down.

Narayan

 

[ashwithonline]

@Narayan...

I do not need RANK..I need numerical scores....as in like performance scores.

 

[Ashhu]

Ashwith
Can you make it little bit more clear on what you mean by .

lowest AHT should have the highest score..

do you mean
Manoj D score (24) should be Kishores score (15917)
or better
update your sample sheet with what you want in C col.

 

[Ashhu]

Ashwith
i am not sure about your requirement, but check the attachment,whether this what you are looking for.!!

 

[ashwithonline]

@Ashhu...


Attached is the modified sheet...in which you can find the process volume score as well as the AHT (Average Handling Score)....
In the sample Kishor has the highest volume..so his process score is understood on being at the higher side..

But AHT is always less the good..So kishor has the lowest AHT ..so his score should be the highest..which it is not showing...

I guess that clears...
let me know if i am missing anything.

Thanks in advance...

 

[Ashhu]

Ashwith
I am sorry i am not clear with your need,
i hope other members will be able to get you what you want.

 

[Shteven]

Ashwith, we would need to know how you determine AHT in order to find the inverse. What is the formula used?

 

[Chihiro]

If you want to simply reverse the order of AHT score... you can do something like...
=1-PERCENTRANK.INC($C$4:$C$17,C4)

HOWEVER...
PERCENTRANK.INC is not ideal for score matrix as it will shift with available range of stats.

Any score matrix, you will want to set up defined range for each. Usually done through lookup table or named range. Actual score assigned to range will depend on number of factors.

AHT, I assume is short form for Average Handle Time.
In most cases, AHT should have Floor and Ceiling.

As arbitrary example, lets say AHT is expected to be about 360 per process.

Then I'd usually set up highest AHT where score can still be obtained at around 480. Any thing above and you get 0 score.
For lowest AHT, similar logic is used. Say, set lowest AHT that will get score at around 240. 240 gets maximum score, but anything lower will get 0.

I use this logic often, especially for AHT, as very low AHT often indicates some corners are cut during process.

 

[ashwithonline]

@Chihiro ...

Probably your formula solves the problem....thanks a lot...