# How to Calculate Deciles in Excel?

Decile is a statistical method to split data into 10 equal parts. Each represents 1/10 of the Population or Sample data. It is denoted D1, D2, … D9. Fifth deciles D5 are equal to the median. It determines 10%,20%, … 90%.

**Deciles Formula: **

Here,

**n:**Number of data points in the population or sample (In the below eg. n = 25)

Find the position of Deciles

D1 = 1*((25+1)/10) = 1*(26/10) = 1*(2.6) = 2.6th value [data between 2 and 3 ] D2 = 2*((25+1)/10) = 2*(26/10) = 2*(2.6) = 5.2nd value [data between 5 and 6 ]

### Sample Data:

### Implementation:

Follow the below steps to calculate the Deciles in Excel:

**Step 1:** Sort the data in ascending order.

**Step 2:** Compute n (Number of data points); in the above example n is 25, which is placed in cell “F1”

**Step 3: **Write Headers for decile table as below in cells E3, F3 and G3.

**Step 4: **Type text D1 to D9 in cells “E4 to E12” respectively

**Step 5:** In cells “F4 to F12” fill decile formula for the respective deciles to find the positions

D1 =1*($F$1+1)/10 D2 =2*($F$1+1)/10 D3 =3*($F$1+1)/10 D4 =4*($F$1+1)/10 D5 =5*($F$1+1)/10 D6 =6*($F$1+1)/10 D7 =7*($F$1+1)/10 D8 =8*($F$1+1)/10 D9 =9*($F$1+1)/10

**Step 6:** Decile value calculation for the respective positions

For D1 2.6th Position find 2nd observation – 12 find 3rd observation – 13 2nd observation + (3rd observation-2nd observation) * 0.6 12 + (13-12) *0.6 = 12.6

Write below formula in cells G4 to G12 for D1 to D9

=B3+(B4-B3)*0.6 =B6+(B7-B6)*0.2 =B7+(B8-B7)*0.8 =B11+(B12-B11)*0.4 =B14+(B14-B14)*0 =B16+(B17-B16)*0.6 =B19+(B20-B19)*0.2 =B21+(B22-B21)*0.8 =B24+(B25-B24)*0.4

**Output:**