Menu
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?
526
These are given as follows: Decile Formula for ungrouped data: D(x) = Value of the x(n+1)10 x ( n + 1 ) 10 th term in the data set. x is the value of the decile that needs to be calculated and ranges from 1 to 9. n is the total number of observations in that data set.
A decile is a quantitative method of splitting up a set of ranked data into 10 equally large subsections. This type of data ranking is performed as part of many academic and statistical studies in the finance and economics fields.
This may not be the most efficient solution, but you might try the following:
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).
Copy the formula in cell B1 to cells B2 through B12000.
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.
125
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
