Ranking Quantiles across multiple columns

Question

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

IDfield 1_rankfield 2_rankand so onfield 50_rank18011...300..............1000500500...500

How do I get to this?

IDfield 1_quintilefield 2_quintileand so onfield 50_quintile151...2..............100033...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?

unknown · Accepted Answer

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.