Do you need to calculate deciles in excel? This post explains how to calculate deciles in excel for ungrouped data.
Decile is one of the statistical methods, which split your data into 10 equal parts and denoted D1, D2,…D9. D5 fifth decile is equal to the median of your data. You have to use two different formula to calculate the deciles for ungrouped and grouped data.
Decile formula for ungrouped data
You need two parameters to compute a decile.
Suppose your data has 25 entries and you have to compute D5.
Assign i = 5 since you have to find D5 decile, and n = 25.
Please follow the below steps to compute decile D5 and D7
- Count the number of entries in your data, type in cell G1
- Sort your data in an ascending order
- Type headers in cell F3, G3, and H3. Format the cells.
- Refer the formula in cell G4, G5, H4 and H5 to compute position and deciles.
Compute D5
- Find the position of your decile D5 for your data set.
- Substitute i = 5 and n = 25 in the above formula
=5*(25+1)/10; 13th is the position for decile D5 - Decile D5 is 54. check the 13 th position values from column B.
- The decile D5 will be the median for the dataset.
Compute D7
- Find the position of your decile D7 for your data set.
- Substitute i = 7 and n = 25 in the above formula
=7*(25+1)/10; 18.2 is the position for decile D7 - Position value has decimal number, So have to apply below formula to compute D7
- 18th position Value +(19th position Value -18th position Value ) * decimal part of the position
- Decile D7 is 69.2. 69 +( 70-69 )*.2
Decile formula for grouped data
Conclusions
As you learned a decile splits the data into 10 equal parts, same like the quartiles divide data into four equal parts and percentile divide data into 100 equal parts. Each decile represents a 10 percentage of points and denote D1, D2, D3, D4, D5, D6, D7, D8, and D9.
