How to calculate deciles in Excel (with examples)?

The Statistical Significance of Deciles

In the realm of statistics, deciles serve as critical markers for data distribution analysis. They are the nine data points that separate the entire ordered data set into ten distinct groups, with each group containing 10% of the observations. This segmentation is invaluable for comparative analysis, allowing analysts to quickly identify relative standing within a population, such as comparing income groups or performance metrics.

The utility of deciles stems from their direct interpretation relative to percentile ranks. The first decile (D1) marks the boundary below which 10% of the data values fall. The second decile (D2) marks the point below which 20% of the values lie, and this pattern continues up to the ninth decile (D9), which separates the top 10% of values from the bottom 90%. Understanding this relationship is key to applying the appropriate functions within Excel, as the software typically calculates deciles using generalized percentile functions.

Deciles provide a more granular view of data distribution compared to quartiles (which divide data into four parts). This enhanced detail is particularly useful when analyzing distributions that are skewed or when precise cutoff points are required for identifying top or bottom performers. For instance, determining the eligibility criteria for a scholarship might involve setting the cutoff at the eighth decile (D8), ensuring only the top 20% of applicants qualify.

Utilizing the PERCENTILE.INC Function in Excel

To calculate the numerical value corresponding to a specific decile boundary in Excel, we rely on the PERCENTILE family of functions. While older spreadsheet applications might use a simple `PERCENTILE` function, modern Microsoft Excel encourages the use of PERCENTILE.INC (Inclusive). This function is designed to calculate the k-th percentile of values in a range, where k is inclusive of the 0 and 1 endpoints. This is generally preferred for statistical analysis as it incorporates the full range of the observed data set.

The syntax for the PERCENTILE.INC function requires two critical arguments: the data range and the percentile multiplier. It is essential to input these arguments correctly to ensure accurate results that reflect the true decile boundaries of the data.

=PERCENTILE.INC(ARRAY, K)

The ARRAY argument refers specifically to the range of cells containing the numerical data you are analyzing. This is the entire population or sample from which the deciles will be derived. The K argument represents the decile rank expressed as a decimal between 0 and 1. To find the decile D1 (10th percentile), K must be 0.1; for D5 (50th percentile or median), K must be 0.5. Ensuring K is a multiple of 0.1 guarantees the result is a true decile boundary.

Example 1: Preparing and Viewing the Dataset

To illustrate the practical application of the decile calculation, let us work with a sample data set of 20 values, representing, for instance, the processing times for 20 different transactions. Our objective is to determine the nine boundary values that segment these processing times into ten equal 10% groups.

The initial step involves organizing the raw data efficiently within an Excel spreadsheet. For our demonstration, the 20 values are listed sequentially in a single column, ready for the application of the PERCENTILE.INC function. Consistency in the array selection is vital for reliable results.

Once the data is correctly entered into the worksheet (e.g., in cells A1 through A20), we should set up a secondary calculation table. This table should list the desired decile ranks (D1 through D9) and the corresponding K values (0.1 through 0.9). This preparation minimizes errors and facilitates the interpretation of the final output.

Calculating the Decile Boundary Values

We will now execute the calculations for each of the nine decile points using the prepared data array. For the first decile (D1), we use the K value of 0.1. Assuming our data is in range A1:A20, the formula will be =PERCENTILE.INC(A1:A20, 0.1). This formula instructs Excel to find the value at which 10% of the data points fall below it.

To calculate the subsequent deciles (D2 through D9), we simply drag the formula down, ensuring the array reference remains constant (using absolute references like $A$1:$A$20) and incrementing the K value by 0.1 for each step. This systematic approach allows for the rapid generation of all nine boundary values, providing a complete statistical picture of the data distribution.

The resulting calculated boundary values shown in the image above clearly define the cutoffs for each 10% group. For example, the value calculated for the ninth decile (D9) determines the threshold that separates the highest performing 10% of observations from the rest of the sample. This information is vital for performance benchmarking.

Interpreting the Calculated Decile Boundaries

Interpreting the numerical output of the PERCENTILE.INC function is straightforward, as it directly corresponds to the percentage of data lying below that specific value. This interpretation is fundamental to utilizing deciles for actionable insights, such as identifying outliers or determining performance tiers.

Based on the computed boundary values illustrated in our example, we can make the following statistical observations:

• The second decile (D2) yields a value of 67.8. This signifies that 20% of all data values recorded in the array fall below this score, positioning it significantly lower than the average or median performance.
• The third decile (D3) is identified by the value 76.5. This means that exactly 30% of all observed data values are less than or equal to 76.5, defining the upper limit of the bottom three decile groups.
• The fourth decile (D4) cutoff is 83.6. Consequently, 40% of the scores fall below this specific boundary, which is a key reference point for analyzing the lower half of the data distribution.

Determining Decile Rank using PERCENTRANK.EXC

In contrast to finding the value for a known decile boundary, analysts often need to determine the percentile and subsequent decile group that an existing raw score falls into. This requires using the inverse function, the PERCENTRANK family of functions in Excel.

The preferred method for this reverse calculation is PERCENTRANK.EXC (Exclusive). This function calculates the rank of a specific value (X) within a data set as a percentile (a decimal between 0 and 1). The output directly tells you what percentage of the data points in the array are less than the value X. This resultant percentile rank is then easily mapped to its decile classification.

The PERCENTRANK.EXC function uses the following structure, which requires specifying the array, the specific value to rank (X), and optionally, the desired number of significant digits for the calculation:

=PERCENTRANK.EXC(ARRAY, X, SIGNIFICANCE)

Example 2: Categorizing Values with PERCENTRANK.EXC

We can apply the PERCENTRANK.EXC function to our existing data set to assign a decile category to every single observation. By referencing the fixed array and iterating through each raw data point, we can instantly generate its corresponding percentile rank.

For instance, if we calculate the rank for the raw score 58, the function returns a decimal value (k) close to 0.05. Since this value falls between 0 (0%) and 0.1 (10%), we confidently place this score into the first decile group. This process is repeated for the entire dataset, creating a comprehensive decile classification for every observation.

The interpretation of the percentile ranks (the output) into their corresponding decile groups relies on mapping the decimal range:

• The data value 58 returns a percentile rank between 0 and 0.1. Consequently, it is classified into the first decile.
• The data value 64 generates a percentile rank between 0.1 and 0.2. This placement confirms it belongs to the second decile.
• The data value 67 also yields a rank between 0.1 and 0.2, illustrating that despite being a different score than 64, it falls within the same 10% decile band—the second decile.
• A score of 68 produces a rank between 0.2 and 0.3. This signifies that the score surpasses the 20th percentile cutoff but remains below the 30th percentile, placing it in the third decile.