Differences between PERCENTILE.EXC function and PERCENTILE.INC function in Microsoft Excel

When working with statistical data in Microsoft Excel, understanding how to effectively derive insights from datasets is crucial. Two commonly used functions for this purpose are PERCENTILE.EXC and PERCENTILE.INC. While both functions serve the purpose of calculating percentiles, they differ significantly in their methodology and application.

Key Takeaways

• PERCENTILE.EXC: Excludes the endpoints and calculates percentiles based on a more theoretical model.
• PERCENTILE.INC: Includes endpoints and provides a more straightforward calculation of percentiles.
• Understanding the differences between these functions enables users to make better data-driven decisions.

Purpose of Each Function

PERCENTILE.EXC is designed to return the k-th percentile of a dataset, where k is a value between 0 and 1, representing the percentage of data points below this threshold. This function is used when you want a percentile calculation that excludes the extremities of the dataset.

PERCENTILE.INC, on the other hand, also returns the k-th percentile but includes the endpoints in its calculation. This function is suited for scenarios where you want a more inclusive look at the dataset, encompassing all available data points.

Syntax and Arguments

PERCENTILE.EXC Syntax:
excel
PERCENTILE.EXC(array, k)

• array: The range of cells or array containing the data.
• k: The percentile value (between 0 and 1), indicating the position of the percentile within the dataset.

PERCENTILE.INC Syntax:
excel
PERCENTILE.INC(array, k)

• array: Same as above.
• k: Also the percentile value (between 0 and 1).

Despite their similar syntaxes, the principal difference lies in how each function treats the ends of the dataset.

Key Differences

1. Endpoint Inclusion:

   • PERCENTILE.EXC excludes the 0th and 100th percentiles.
   • PERCENTILE.INC includes the 0th and 100th percentiles.

2. Calculation Method:

   • PERCENTILE.EXC uses a method that is more reflective of theoretical distributions.
   • PERCENTILE.INC operates on the actual set of values in the dataset.

3. Use Cases:

   • Use PERCENTILE.EXC when you want a more refined analysis that avoids outliers.
   • Use PERCENTILE.INC for a full representation of how values in the dataset interact, especially when extremes are relevant.

Example Illustration

To clarify the differences further, let’s consider a simple dataset and see how both functions operate on it.

Sample Data:
| Values |
|————|
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |

Applying PERCENTILE Functions:

If we want to find the 40th percentile (k = 0.4):

• Using PERCENTILE.EXC:
  excel
  =PERCENTILE.EXC(A2:A6, 0.4)

  • Result: 22 (approximately)

• Using PERCENTILE.INC:
  excel
  =PERCENTILE.INC(A2:A6, 0.4)

  • Result: 30 (exactly)

The output clearly illustrates how excluding endpoints can yield lower results, which is important in in-depth data analysis.

Conclusion

In summary, both PERCENTILE.EXC and PERCENTILE.INC serve to calculate percentiles but yield different results based on their treatment of data endpoints.

• PERCENTILE.EXC is preferable when the focus is on avoiding the influence of extreme values. It’s especially useful in theoretical contexts or when working with simulated datasets.

• PERCENTILE.INC is ideal for a more complete view, encompassing all data points, including extremes. It’s a straightforward tool for those needing comprehensive data insights, such as in a practical business application.

Carefully consider your dataset and the context of your analysis when choosing between these two functions. Your choice will influence the insights you derive and the decisions based on them.