How do I simply output Deciles in Excel?
 

I am trying to find deciles as well as P20 P30 ... P90 for a large dataset.
Doe Excel have a function or an easy way to perform this opperation?

Thanks,

Answer: How do I simply output Deciles in Excel?
 

Yes, Excel has a function to calculate deciles and percentiles. You can use the PERCENTILE function to find the value at a specific percentile in a dataset.

To find the deciles and percentiles for your dataset, follow these steps:

1. Enter your data into a column in Excel.
2. Sort the data in ascending order by selecting the column and clicking on the "Sort A to Z" button in the "Data" tab.
3. In a new column, enter the following formula: =PERCENTILE(A:A,0.1) for the first decile, =PERCENTILE(A:A,0.2) for the second decile, and so on. Replace "A:A" with the range of your data.
4. Copy the formula down to calculate the remaining deciles.
5. To calculate the percentiles, use the same formula but replace the decimal number with the desired percentile. For example, =PERCENTILE(A:A,0.3) for the 30th percentile.

You can also use the QUARTILE function to find the quartiles of your dataset. The formula is =QUARTILE(A:A,1) for the first quartile, =QUARTILE(A:A,2) for the second quartile (median), and =QUARTILE(A:A,3) for the third quartile.

Assume your range is A1:A1000, to get deciles:

=PERCENTILE(A1:A1000,x/10)

Where "x" is the decile number.

--
Regards,
Dave


"iceman30" wrote:

I am trying to find deciles as well as P20 P30 ... P90 for a large dataset.
Doe Excel have a function or an easy way to perform this opperation?

Thanks,