Does anyone have experience writing formulas to assign "deciles" to a range of values?

Walter Grubb

I have several series of different ranges of values that I am seeking to "decile" - i.e. rank order the values from largest to smallest value, take the Total Sum of the range, and for rows that sum through the first 10% of the Total Sum assign a "10", for those rows that sum to the next 10% of the Total Sum assign a "9", and so on. This is a weighted assignment such that there are usually a paucity of rows with a "10" (i.e. they have large values and quick sum-up to 10% of the Total), and lot of rows with a "1" assigned.

I have been doing this with manual counting, but would like to automate with formulas so that the sheet will recalculate/reassign deciding values if/when new rows are added.

Genevieve P.

Hi @Walter Grubb

I have to admit this type of formula is new to me! I used this article to visualize what it is you were looking to do, so let me know if I misunderstood.

Smartsheet has a Percentile Function that you can use. In my sheet, I made a tiny graph off to the side just so I could double check if the formula I made per-row matches the correct ranking:

The Percentile formula would be:

=PERCENTILE(Values:Values, 1/10)

or

=PERCENTILE(Values:Values, 0.1)

However, we don't need it in a little graph, we need it associated with each individual row. In order to do this, we can use a Nested IF statement to check the value and put it against the 10 possible percentile options.

Here's one IF statement:

=IF(Values@row >= PERCENTILE(Values:Values, 1), 10,

Then we need to check it against PERCENTILE(Values:Values, 0.9), so:

IF(Values@row >= PERCENTILE(Values:Values, 0.9), 9,

and so on, for a Full Formula:

=IF(Values@row >= PERCENTILE(Values:Values, 1), 10, IF(Values@row >= PERCENTILE(Values:Values, 0.9), 9, IF(Values@row >= PERCENTILE(Values:Values, 0.8), 8, IF(Values@row >= PERCENTILE(Values:Values, 0.7), 7, IF(Values@row >= PERCENTILE(Values:Values, 0.6), 6, IF(Values@row >= PERCENTILE(Values:Values, 0.5), 5, IF(Values@row >= PERCENTILE(Values:Values, 0.4), 4, IF(Values@row >= PERCENTILE(Values:Values, 0.3), 3, IF(Values@row >= PERCENTILE(Values:Values, 0.2), 2, 1)))))))))

You can make this a column formula and it will auto-update as new rows are added. (See: Set Formulas for All Rows with Column Formulas) Is this what you were looking to do?

Cheers,

Genevieve

Walter Grubb

Genevieve - sorry for taking so long to respond, but this formula did the trick - very helpful! Thank you so much.

Genevieve P.

Hi @Walter Grubb

I have to admit this type of formula is new to me! I used this article to visualize what it is you were looking to do, so let me know if I misunderstood.

Smartsheet has a Percentile Function that you can use. In my sheet, I made a tiny graph off to the side just so I could double check if the formula I made per-row matches the correct ranking:

The Percentile formula would be:

=PERCENTILE(Values:Values, 1/10)

or

=PERCENTILE(Values:Values, 0.1)

However, we don't need it in a little graph, we need it associated with each individual row. In order to do this, we can use a Nested IF statement to check the value and put it against the 10 possible percentile options.

Here's one IF statement:

=IF(Values@row >= PERCENTILE(Values:Values, 1), 10,

Then we need to check it against PERCENTILE(Values:Values, 0.9), so:

IF(Values@row >= PERCENTILE(Values:Values, 0.9), 9,

and so on, for a Full Formula:

=IF(Values@row >= PERCENTILE(Values:Values, 1), 10, IF(Values@row >= PERCENTILE(Values:Values, 0.9), 9, IF(Values@row >= PERCENTILE(Values:Values, 0.8), 8, IF(Values@row >= PERCENTILE(Values:Values, 0.7), 7, IF(Values@row >= PERCENTILE(Values:Values, 0.6), 6, IF(Values@row >= PERCENTILE(Values:Values, 0.5), 5, IF(Values@row >= PERCENTILE(Values:Values, 0.4), 4, IF(Values@row >= PERCENTILE(Values:Values, 0.3), 3, IF(Values@row >= PERCENTILE(Values:Values, 0.2), 2, 1)))))))))

You can make this a column formula and it will auto-update as new rows are added. (See: Set Formulas for All Rows with Column Formulas) Is this what you were looking to do?

Cheers,

Genevieve

Walter Grubb

Genevieve - sorry for taking so long to respond, but this formula did the trick - very helpful! Thank you so much.