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
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
Last edited by a moderator: