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

1. Enter the following formula in cell B1: =PERCENTRANK($A$1:$A$12000,A1,1), assuming your numbers are in cells A1 through A12000. The percent rank of the value in cell A1, reduced down to one decimal place, is calculated using the set of values in cells $A$1:$A$12000 (which is all you need to identify the decile).

2. Cells B2 through B12000 should all have the same formula as cell B1.

3. Determine the decile for the relevant value in column A using the values in column B. 0 designates values that are more than or equal to the 0th percentile but less than the 10th percentile, 0.1 designates values that are greater than or equal to the 10th percentile but less than the 20th percentile, and so on. There may or may not be a value that is assigned a PERCENTRANK of exactly 1 depending on the size of your set and whether or not there are duplicates.

If you're using Excel 2010, you might want to consider using the new PERCENTRANK.INC and PERCENTRANK.EXC functions, which are designed to replace PERCENTRANK.

I hope this information is useful.