ONeed help with quintile in Excel or Power Query

[bsrujan022]

Hi team, 

Hope you're doing great. So, I have a question. I'm trying to automate a similar dataset, where I try to categorize the skillset based on their scores while making sure that I only calculate for the rows with the same skill name.

Let's just say, I have a dataset with 3 columns, 'Skill', 'Name' & 'Score'. here, what I'm trying to do is, create a new column that categorizes their quintile and assigns A, B, C, or D only based on what their skill is showing. 

Let's take the 'Draw' skill, we've 5 rows for that, so I want scores categorized into a, b, c, and d categories just for that. And then the next skill 'Paint'and so on. 

I'm trying to automate this, so Power Query would also be good, let me know what would be a good way to do so.

[v-jingzhang · Accepted solution]

Hi @bsrujan022 

For quintile I think you may need to categorize the scores into 5 categories (A, B, C, D, E). For quartiles, you can categorize them into 4 categories. But nevermind the calculation logic is similar. Below is my result for quintiles. You can download the sample file attached at bottom to see detailed Excel formulas. 

References:

How to Perform a Percentile IF Function in Excel (statology.org)

How to Calculate Quintiles in Excel (With Examples) (statology.org)

IFS function - Microsoft Support

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.