Excel Formula for Calculating Percentile

Formulas & functions · Updated November 22, 2026

The Excel formula for calculating a percentile is =PERCENTILE.INC(array, k) for inclusive percentiles (0 to 1 inclusive) or =PERCENTILE.EXC(array, k) for exclusive percentiles (0 to 1 exclusive). For example, =PERCENTILE.INC(A2:A101, 0.9) returns the 90th percentile of the values in cells A2 through A101.

What Is a Percentile and Why Use It?

A percentile is a measure used in statistics indicating the value below which a given percentage of observations in a group of observations falls. For instance, the 50th percentile (the median) is the middle value: half the data points are below it, half above. Percentiles are widely used in education (test scores), finance (risk metrics like Value at Risk), and quality control (product measurements). In Excel, the PERCENTILE functions help you quickly find these thresholds without manually sorting and counting data.

How the PERCENTILE Functions Work

Excel offers two primary percentile functions: PERCENTILE.INC and PERCENTILE.EXC. Both take two arguments: array (the range of numeric values) and k (the percentile value between 0 and 1). The difference lies in how they interpolate between data points. PERCENTILE.INC includes the endpoints (0th and 100th percentiles are the minimum and maximum of the data). PERCENTILE.EXC excludes them, meaning k must be strictly between 0 and 1. For most statistical purposes, especially when you want a percentile that corresponds to a rank that is not exactly a data point, both functions interpolate linearly. For example, with data {1, 2, 3, 4}, the 25th percentile using INC is 1.75, while using EXC it is 1.5. The choice depends on your field’s convention; many statistical software packages default to the exclusive method.

How to Choose Between PERCENTILE.INC and PERCENTILE.EXC

Choosing the right function depends on your data and the definition you need:

  • Use PERCENTILE.INC when you want a percentile that includes the minimum and maximum values, or when you are working with a large dataset where the difference is negligible. It is also the default in many Excel versions when you use the older PERCENTILE function (which is still available for compatibility).
  • Use PERCENTILE.EXC when you are following a statistical standard that excludes the extremes, such as when calculating quartiles in some academic contexts. It is more conservative because it never returns the exact min or max as a percentile unless k is 0 or 1 (which is not allowed).
  • If your k is 0 or 1, PERCENTILE.EXC will return a #NUM! error. PERCENTILE.INC will return the minimum and maximum respectively.

Step-by-Step Example with a Real Dataset

Suppose you have test scores in cells B2:B31 (30 students). You want the 75th percentile (the score below which 75% of students fall).

  1. Click an empty cell, say D2.
  2. Type =PERCENTILE.INC(B2:B31, 0.75) and press Enter.
  3. Excel returns the 75th percentile. For instance, if the scores are 50, 55, 60, 65, 70, 75, 80, 85, 90, 95 (repeated three times each), the 75th percentile would be 85. (Check: 75% of 30 is 22.5, so the value at position 22.5 interpolates between the 22nd and 23rd values, both 85 in this case.)

You can also use the function with a cell reference for k, for example if you have 0.9 in cell C1, use =PERCENTILE.INC(B2:B31, C1).

Common Problems and Errors

Several issues can arise when using percentile formulas:

  • #NUM! error: Occurs if k is not between 0 and 1 (for INC) or not strictly between 0 and 1 (for EXC). Also occurs if the array is empty.
  • #VALUE! error: If k is non-numeric or the array contains text or logical values that are not directly numeric (though numbers stored as text are ignored).
  • Incorrect results due to hidden rows or filters: The PERCENTILE functions ignore hidden rows only if you use the SUBTOTAL function with percentile? Actually, there is no SUBTOTAL for percentile. To exclude hidden rows, you’d need to use an array formula with AGGREGATE, but that’s complex. Simpler: ensure your data range includes only the values you want.
  • Mixing data types: If your array contains blank cells, they are ignored, but if it contains text, that cell is ignored as well. Ensure all values are numeric.

Comparing PERCENTILE with QUARTILE and Other Functions

Excel also has QUARTILE.INC and QUARTILE.EXC functions, which are essentially percentiles at 0%, 25%, 50%, 75%, and 100% (INC) or 0% and 100% excluded (EXC). For example, =QUARTILE.INC(A2:A101, 1) is the same as =PERCENTILE.INC(A2:A101, 0.25). Additionally, the MEDIAN function is the 50th percentile. If you need to rank a specific value against the dataset, use PERCENTRANK.INC or PERCENTRANK.EXC, which return the percentile rank of a given value. These are inverse operations: PERCENTILE gives the value for a percentile, while PERCENTRANK gives the percentile for a value.

Alternatives and Advanced Methods

For large datasets or when you need to compute multiple percentiles at once, consider using the LET function to avoid repeating the range. For example: =LET(data, A2:A101, PERCENTILE.INC(data, 0.25)). If you need percentiles for grouped data, you may need to use array formulas or the AGGREGATE function, but that is more advanced. For dynamic arrays in Excel 365, you can use =PERCENTILE.INC(FILTER(A2:A101, B2:B101="Group1"), 0.9) to calculate a percentile for a subset. Remember that PERCENTILE (without .INC or .EXC) is still available for backward compatibility but is not recommended for new work because it defaults to the inclusive method and may be removed in future versions.

Frequently Asked Questions

How do I calculate the 90th percentile in Excel? Use =PERCENTILE.INC(range, 0.9) or =PERCENTILE.EXC(range, 0.9). Replace “range” with your data cells, e.g., A2:A100. The result is the value below which 90% of your data falls.

What is the difference between PERCENTILE.INC and PERCENTILE.EXC? The INC version includes the minimum and maximum values in the possible output (k can be 0 or 1), while EXC excludes them (k must be between 0 and 1). This changes the interpolation slightly, especially for small datasets. For example, with data {1,2,3,4}, the 25th percentile is 1.75 with INC and 1.5 with EXC.

Why does my formula return #NUM!? This error occurs if k is not between 0 and 1 (for INC) or not strictly between 0 and 1 (for EXC). Also, if the array is empty or contains no numeric values, you may get this error. Check your k value and ensure your data range has numbers.

Can I use PERCENTILE with filtered data? No, PERCENTILE ignores hidden rows only if you use the SUBTOTAL function, but SUBTOTAL does not have a percentile option. To exclude hidden rows, you would need to use an array formula with AGGREGATE or manually copy the visible data to a new range. For most cases, it’s simpler to ensure your range includes only the data you want.

How do I find the percentile rank of a specific value? Use =PERCENTRANK.INC(array, x) or =PERCENTRANK.EXC(array, x), where x is the value. This returns the percentile (as a decimal) of that value within the array. For example, =PERCENTRANK.INC(A2:A101, 85) returns the percentile rank of 85 in that range.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.