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.
Help Article Resources
Check out the Formula Handbook template!