Does anyone have experience writing formulas to assign "deciles" to a range of values?
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 sumup 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.
Best Answers

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 perrow 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 autoupdate as new rows are added. (See: Set Formulas for All Rows with Column Formulas) Is this what you were looking to do?
Cheers,
Genevieve

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

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 perrow 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 autoupdate as new rows are added. (See: Set Formulas for All Rows with Column Formulas) Is this what you were looking to do?
Cheers,
Genevieve

Genevieve  sorry for taking so long to respond, but this formula did the trick  very helpful! Thank you so much.
Help Article Resources
Categories
Check out the Formula Handbook template!