Ranking Quantiles across multiple columns

[jack13davis13]

Hi all,

I have ~50 fields of different performance metrics, and my goal is to rank them all by quintiles.  I've generated additional columns for the rank within each field.  Is there a simple way to generate the next set of 50 columns which would translate the rank into which quintile each row falls into?  

So from this

ID	field 1_rank	field 2_rank	and so on	field 50_rank
1	801	1	...	300
...	...	...	...	..
1000	500	500	...	500

How do I get to this?

ID	field 1_quintile	field 2_quintile	and so on	field 50_quintile
1	5	1	...	2
...	...	...	...	..
1000	3	3	...3

Frustrating wrinkle is that not every row has a rank so the total rank number for each column is different.  EG field one might only have ranks 1-10 and field 50 might have ranks 1-1000, so the total used to calculate the quintile needs to be dynamic.

Have tried summarize and append to get the dyanmic total in order to calculate quintile, but how do you repeat this for 50 different columns without writing the formula every time?

[Inactive User · Accepted solution]

1) Summarize your initial table to take the max value for each field, excluding ID. Ensure the word "Max" is not prefixed to each column name.

2) Transpose that initial table and group by ID.

3) Insert a record ID for step 1 and transpose, grouping on Record ID.

4) Join table sets 2 and 3 together on the "Name" field, which will be your field names. You will now have a record for each field name by ID with the max value in each column.

5) Create a formula that divides value by max value for each record. For example 300/500 will give you 0.6. Then within the same formula take that 0.6 value and multiple it by 5. And finally, round up to 1. The formula would look like this:

             Round(((Value/Max Value)*5),1)

Now you have your quintiles from 1-5.