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 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.
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 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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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 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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Genevieve - sorry for taking so long to respond, but this formula did the trick - very helpful! Thank you so much.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!