The `PERCENTILE.EXC` function in Excel is used to calculate the k-th percentile of values in a range, where k is a number between 0 and 1 representing the desired percentile excluding 0 and 1. Here’s how you can use it:
Syntax
PERCENTILE.EXC(array, k)
- array: This is the range of data values from which you want to determine the percentile.
- k: This is the percentile value you wish to calculate, expressed as a decimal between 0 and 1.
Steps to Use `PERCENTILE.EXC`
- Select the Cell: Click on the cell where you want the result of the function to appear.
- Enter the Function:
- Type `=PERCENTILE.EXC(`.
- Select or enter the cell range that contains the data for which you’re calculating the percentile. For example, `A1:A10`.
- Enter a comma `,`.
- Specify the percentile `k` as a decimal. For example, if you want the 75th percentile, you would enter `0.75`.
- Complete the function with a closing parenthesis `)`.
- Press Enter: After typing the formula, press Enter to calculate and display the percentile.
Example
Suppose you have the following data in cells A1 to A10:
A1: 5
A2: 7
A3: 8
A4: 12
A5: 14
A6: 18
A7: 24
A8: 30
A9: 38
A10: 50
To calculate the 75th percentile:
=PERCENTILE.EXC(A1:A10, 0.75)
- Click on a cell (e.g., B1) and type:
- Press Enter, and the cell will display the result, which is 27.25 in this case.
Important Notes
- The `PERCENTILE.EXC` function interpolates if the specified percentile lies between two data points, thus providing a more precise result when data values are not perfectly aligned with the desired k-th percentile.
- The `PERCENTILE.EXC` function will return an error if `k` is less than or equal to 0, or greater than or equal to 1.
- Unlike `PERCENTILE.INC`, which includes the endpoints and allows `k` to be exactly 0 or 1, `PERCENTILE.EXC` strictly excludes both endpoints. This means `k` must be between 0 and 1, exclusive.
Unlock Your Potential
Excel
Access
Power BI
