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

  • Genevieve P.
    Genevieve P. Employee
    edited 01/18/22 Answer ✓

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Walter Grubb
    Walter Grubb ✭✭✭
    Answer ✓

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

Answers

  • Genevieve P.
    Genevieve P. Employee
    edited 01/18/22 Answer ✓

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Walter Grubb
    Walter Grubb ✭✭✭
    Answer ✓

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!