How can I calculate deciles with a range of 12,000 cells in excel?

Question

I have a column of 12,000+ numbers, both positive and negative, sorted from highest to lowest in an Excel spreadsheet.

Is there an easy way to go about dividing this range into deciles?

Answer 1

This may not be the most efficient solution, but you might try the following:

1. Assuming your numbers are in cells A1 through A12000, enter the following formula in cell B1 =PERCENTRANK($A$1:$A$12000,A1,1). This calculates the percent rank, with the set of values in cells $A$1:$A$12000, of the value in cell A1, rounded down to 1 decimal place (which is all you need to identify the decile).

2. Copy the formula in cell B1 to cells B2 through B12000.

3. Use the values in column B to identify the decile for the corresponding value in column A. 0 identifies values greater than or equal to the 0th percentile and less than the 10th percentile, 0.1 identifies values greater than or equal to the 10th percentile and less than the 20th percentile, and so on. Depending on the size of your set and whether or not there are duplicates, there may or may not be a value that gets assigned a PERCENTRANK of exactly 1.

If you are using Excel 2010, you might, depending on your needs, consider using the new functions PERCENTRANK.INC and PERCENTRANK.EXC that are supposed to supercede PERCENTRANK.

Hope this helps.