How to tag values in a column as Hi/Mid/Lo

Question

I need to tag values in several columns as:

those with "0" will be tagged as N/A

top 30% = hi

bottom 30% = lo

everything else = mid

In the sample file, i added two test columns, but the tagging will also be applied to several similar columns with the same logic, just the values will be changing.

I was thinking of using the Sample tool, but i can only see "First n% of rows". Thank you

Revenue Test - Copy.xlsx

FinnCharlton · Answer

Hi @gizmoholic, here are a couple of ways to accomplish this. The first one normalises the values to percentiles based on VALUE, whereas the second chooses percentiles based on RANK. Choose whichever is relevant based on whether you want to look at value or count.

Hope this helps!

Percentiles.yxmd