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.EXCwill return a #NUM! error.PERCENTILE.INCwill 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).
- Click an empty cell, say D2.
- Type
=PERCENTILE.INC(B2:B31, 0.75)and press Enter. - 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
- Excel Before Close Event: How to Run Code on Save
- SUBSTRING in Snowflake: Syntax, Examples, and Tips
- Excel Auto Loan Amortization Template: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Multiply Key in Excel: Use the Asterisk or FormulaIn Excel, multiplication uses the asterisk key (*) inside a formula, not the letter x or the ×…
- DATEDIF Formula Examples for Excel: Years, Months & DaysIn Excel, DATEDIF calculates the time between two dates in completed years, months, or days. For example, if…
- Excel Formulas to Find Matching Data in Your ListsTo check whether a value appears in another Excel list, use COUNTIF; to return information from the matching…
- Excel UNIQUE, SORT, and FILTER Spill Errors ExplainedIf a formula using UNIQUE, SORT, and FILTER returns #SPILL!, Excel cannot place its results in the cells…
- Excel Sum Between Dates: SUMIFS Formulas and ExamplesIf dates are in A2:A100, amounts are in B2:B100, and the start and end dates are in E2…
- Dynamic Range Formula in Excel: Methods and ExamplesA dynamic range in Excel expands or contracts as data is added or removed. For a contiguous list…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.