# Simple persentile formula not working

Copper Contributor

# Simple persentile formula not working

Why is the formula not working?

4 Replies

# Re: Simple persentile formula not working

@LFM123 The quality of the screen shot it not good enough to read what it says. What formula did

you use? What was the error message and how does the underlying data look like?

# Re: Simple persentile formula not working

I used this formula: =PERCENTILE.INC(D3:D31,1-L2) for a certain numerical rows. The error message is that there is a problem with this formula.

# Re: Simple persentile formula not working

@LFM123 Judging by your name, you are from a Nordic country and perhaps need to use the semi-colon to separate arguments withing a formula.

# Re: Simple persentile formula not working

The error message you're encountering in the formula =PERCENTILE.INC(D3:D31,1-L2) is likely due to a combination of two factors:

1. Incorrect Argument for PERCENTILE.INC:

• The PERCENTILE.INC function requires the second argument (k) to be a value between 0 (exclusive) and 1 (inclusive).
• In your formula, 1-L2 might result in a value outside this range, causing the error.
2. Data Array Size and k Value:

• The PERCENTILE.INC function calculates the k-th percentile of a data set.
• If you have a small number of data points (e.g., less than 100) and a specific k value close to the edges (0 or 1), the function might not be able to find a valid percentile within the data set, leading to an error.

Here are two ways to fix the formula:

• Instead of dynamically calculating k using 1-L2, try entering a fixed value between 0 and 1.
• For example, to calculate the 75th percentile, you can use =PERCENTILE.INC(D3:D31,0.75).

2. Use PERCENTILE Function (if applicable):

• If you need more flexibility in calculating percentiles based on dynamic criteria, consider using the PERCENTILE function instead.
• The PERCENTILE function takes two arguments: the data array and the percentile rank (between 0 and 100).
• For example, to calculate the 75th percentile, you can use =PERCENTILE(D3:D31,75/100).