Categorize Results by Decile

[DME]

Hello all,

I've read various articles about this but haven't been successful in creating something that works, so hoping you can help. I'm using Excel 2010...

I'm attempting to bucket scores for a data set - based on 1 criteria ("Level") - into deciles. The challenge is that I have 20+ columns of scores that will each need to be bucketed into deciles separately. I'm familiar with deciles but don't want to create another data table showing each deciles values and then use a lookup to place each score into the appropriate decile - I want to simply use 1 calculation to do this. Is this possible?

I've attached a sample file that contains a data set for 1 of the scores. Inside is also a more detailed overview of the desired results I'm looking for from the formula.

Any ideas would be greatly appreciated!

p.s. I'm open to using VBA if that is the easiest approach

 

[bosco_yip]

Maybe,

D2, copy down :

=MATCH(C2,{0;10;20;30;40;50;60;70;80;90})

Regards

 

[DME]

Hi @bosco_yip , thanks so much for the quick reply. To account for the Level criteria - I was going to simply add and "IF" statement to the front of your MATCH formula. Would you agree that is the best way to do it?

Thanks!

 

[DME]

I'm looking at the solution in more detail @bosco_yip and I don't think this gets me what I need. I'm trying to now back into these categories by:

1) Determining where each Score ranks within a Level
2) Determining the number of Scores within a Level
3) Dividing rank by number of Scores
4) Looking up that % in a table to get the corresponding number

Any other ideas on how possibly to do this? If I get my solution to work, I'll be sure to post it. Thanks.

 

[DME]

I changed the heading for Column D to "Percentile" and here's my final formula:

=(1-(IF(ISNUMBER(C2),SUMPRODUCT(--([Level]=B2),--([Score]>C2),--ISNUMBER([Score]))+1,"")/COUNTIFS([Level],B2,[Score],"<>"&"",[Score],">"&-1)))*100

Would love to hear if anyone has a different approach!