The PERCENTILE.INC function in Excel is a powerful tool used to determine the k-th percentile of a given data set. This function helps users to analyze data and understand the distribution of values by identifying the point below which a given percentage falls. It is particularly useful for statistical analysis, summary reporting, and data comparison.

Syntax

PERCENTILE.INC(array, k)

• array: The range of values from which you want to calculate the percentile.
• k: The percentile value you want to find, expressed as a decimal (0 to 1).

Example #1

=PERCENTILE.INC(A1:A10, 0.25)

In this example, the function calculates the 25th percentile of the values located in cells A1 through A10. If the dataset consists of the values 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, the result would be 3.25.

Example #2

=PERCENTILE.INC(B1:B20, 0.5)

This function call finds the median (50th percentile) of the data in cells B1 to B20. If the range includes the values 10, 20, 30, 40, 50, 60, the median would be 35.

Example #3

=PERCENTILE.INC(C1:C15, 0.9)

Here, the function calculates the 90th percentile from the values in cells C1 to C15. If the range contains values of 5, 10, 15, 20, and so on, the 90th percentile might result in a value of approximately 13.5 if the highest numbers were excluded.

Error handling

• NUM!: This error occurs if k is less than 0 or greater than 1, as those values fall outside the valid range for percentiles.
• VALUE!: This error appears if the array is non-numeric or if the function is incorrectly formatted, indicating invalid input.

Conclusion

In summary, the PERCENTILE.INC function is an essential tool for users looking to analyze and understand their data distributions in Excel. By effectively calculating specific percentiles, this function provides valuable insights for decision-making and statistical evaluations.