• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Percentile Calculation

Yadavagiri

New Member
Hi All,

This is Yadav. I need a formula which calculates Performance Quintile based on Quintile 1, Quintile 2, Quintile 3, Quintile 4, Quintile 5.

I do have a formula but that doesnt give me accurate calculation :(

For Ex: The below is the 15 advisor OPI Score and i have sorted out from Highest to Lowest OPI Score.

Advisor Name OPI Score
a 100
b 100
c 100
d 100
e 100
f 100
g 75
h 60
i 55
j 45
k 30
l 25
m 20
n 15
o 10

If i want to know the quintile based on Top Performance to Lower Performance based on Quintile 1 being Top Performer & Quintile 5 being Low performer.

I have used formula given below:

=IF(ISBLANK(B2), " ", IF(B2<PERCENTILE($B$2:$B$16,20%)," 5",IF(AND(B2>=PERCENTILE($B$2:$B$16,20%),B2<PERCENTILE($B$2:$B$16,40%))," 4",IF(AND(B2>=PERCENTILE($B$2:$B$16,40%),B2<PERCENTILE($B$2:$B$16,60%))," 3",IF(AND(B2>=PERCENTILE($B$2:$B$16,60%),B2<PERCENTILE($B$2:$B$16,80%))," 2"," 1")))))

I am getting below results:

Advisor Name OPI Score Quintile
a 100 1
b 100 1
c 100 1
d 100 1
e 83 2
f 81 2
g 80 3
h 60 3
i 55 3
j 45 4
k 30 4
l 25 4
m 20 5
n 15 5
o 10 5

But i feel this is incorrect. As Quintile is being calculated in 5 divisions, 3 advisors should and are eligible for each and every quintile from Top Performance to Low performance i.e, Qunitile 1=3 Advisors, Quintile 2 = 3 Advisors, Qunitile 3=3 Advisors, Quintile 4 = 3 Advisors, Qunitile 5=3 Advisors but as per my formula above Quintile 1 is 4 advisors and Quintile 2 is 2 advisors.

If there any formula which calculates Percantile to rate the advisors in the range of Quintile 1 to Quintile 5 in equal proportion?

I would be pleased if you/anyone could help on this.

Thanks you in advance and i await your kind response on the same.

Regards,
Yadav
Email: XXXXXX (mail id removed for security reason.. Deb)
Bengaluru
 

Attachments

  • Quintile.xlsx
    9.5 KB · Views: 4
Last edited by a moderator:
Hi Yadav,

As the top 4 advisors all have an equal score, how should we/XL determine who gets puts into the next quintile? Along those lines, the quintiles are not thought of as always being equal. I'm thinking of a box-and-whisker plot as an example which shows that quartiles are not evenly divided. With your data, the ranges would be plotted as:
Q1: 100-100
Q2: 99-81
Q3: 80-55
Q4: 54-25
Q5: 24-0

Perhaps if you can elaborate on your overall goal, we can help you change the formulas. But as they sit now, the formulas are performing correctly.
 
Hi Yadav,

As the top 4 advisors all have an equal score, how should we/XL determine who gets puts into the next quintile? Along those lines, the quintiles are not thought of as always being equal. I'm thinking of a box-and-whisker plot as an example which shows that quartiles are not evenly divided. With your data, the ranges would be plotted as:
Q1: 100-100
Q2: 99-81
Q3: 80-55
Q4: 54-25
Q5: 24-0

Perhaps if you can elaborate on your overall goal, we can help you change the formulas. But as they sit now, the formulas are performing correctly.

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

Hi Luke,

Thanks for your reply on the same and I am sorry for the delayed response. I think your answer is quite perfect as quintiles are not evenly divided.
However, If i have one more category added which login hours delivered by the advisor, can we have the quintile for each advisor by considering the OPI Scores & Login hours?

OPI is ranked by Higher to lower and i want to give quintile ranking to advisors based on OPI (High to Low) considering login hours delivered. In the sense for example below:

Advisor "A" OPI Score is 100 and login hours delivered is 2.5.

Advisor "B" OPI Score is 100 and login hours delivered is 2.25.

In the above example, advisor "A" should be getting "Quintile 1 rank" as he has got 100 OPI Score and hours delivered is 2.5. Where as Advisor "B" will go to lower rank in this case would be "Quintile 2 rank" as he has delivered 2.25 hours although OPI Score is 100 which is less than hours delivered by Advisor "A".

Can you please let me know, if this is possible? If so, can you please let me know the formula on how to arrive at this.

Your earliest response on this would be appreciated.

Regards,
Yadav
<redacted>
Bengaluru
 

Attachments

  • Quintile.xlsx
    10.3 KB · Views: 4
Last edited by a moderator:
We can create a helper column the combines the two values (we'll divide login by 10000 so as not to distort original OPI number. See attached.

PS. You should probably not post your email address, as public forums like this are frequently scanned by spam bots.
 

Attachments

  • Quintile LM.xlsx
    10.5 KB · Views: 14
Hi
We can create a helper column the combines the two values (we'll divide login by 10000 so as not to distort original OPI number. See attached.

PS. You should probably not post your email address, as public forums like this are frequently scanned by spam bots.

Hi Luke,

Thanks for the speedy response :) I shall check on this tomorrow when I am back to the office. Hope this would sort my query. If any further help required, I shall contact you. Thanks again.!!

Sure, I shall not update my email address henceforth & I thank you for providing feedback on the same.

Regards,
Yadavagiri
 
We can create a helper column the combines the two values (we'll divide login by 10000 so as not to distort original OPI number. See attached.

PS. You should probably not post your email address, as public forums like this are frequently scanned by spam bots.

-----


Hi Luke,

Thanks for this. I would like to have a clarification as to how did you arrive using 10000 as constant to include in the formula.

Regards,
Yadavagiri
 
Glad it works.

The value of the constant is somewhat arbitrary. The main goal is that we need to combine the two values, while not distorting the OPI value so that they can still be compared correctly. We can do this by instead of adding a whole number to OPI, we have a fraction/decimal.

To convert Login # to a decimal, that is why we divide. Our divisor just needs to be "big enough" that it will reduce any number in the Login column to a value < 1. Looking over your data, 10000 seemed sufficient. :)

Now that we have a helper column with decimal values, the PERCENTILE function can detect the small difference between 100.0215 and 100.0100 (corresponding to records where OPI = 100 and Login = 2.15 and 1.00)
 
Glad it works.

The value of the constant is somewhat arbitrary. The main goal is that we need to combine the two values, while not distorting the OPI value so that they can still be compared correctly. We can do this by instead of adding a whole number to OPI, we have a fraction/decimal.

To convert Login # to a decimal, that is why we divide. Our divisor just needs to be "big enough" that it will reduce any number in the Login column to a value < 1. Looking over your data, 10000 seemed sufficient. :)

Now that we have a helper column with decimal values, the PERCENTILE function can detect the small difference between 100.0215 and 100.0100 (corresponding to records where OPI = 100 and Login = 2.15 and 1.00)

---

Hi Luke,

Thanks for the swift reply :)

I had just given approx login hours. However, if i use any login hours delivered within the range of 40-50 hrs, can i use 10000?

Just need to check on this.

Regards,
Yadavagiri
 
Hi Luke,

Need help!

I need to know how to create macros in excel. I am new to macro's. Do you have any document which gives steps to create Macro's? If so, will you be able to share with me? I would be grateful, if you could do so.

Regards,
Yadavagiri
 
Back
Top