Why does this Excel error happen?

Question

I am trying to help my son with a project for his school. He told me he needed help to calculate the percentage using the function 'PERCENTILE.EXC'. When I press enter after putting the array and then some operations, I get this error:

How am I supposed to fix this error? Oh and I forgot to show the formula:- =PERCENTILE.EXC(D4:H4*I4/1000)

Am I doing this correctly? Is there something wrong?

Answer 1

Hi Special A!

I am Shakiru, an independent advisor and a user like you, and I am glad to be helping you out today.

The error you're encountering is likely because the syntax of your formula is incorrect.

The PERCENTILE. EXC function in Excel requires two arguments: the array of data and the k-th percentile you want to calculate.

Here's is how it works:

=PERCENTILE. EXC(array, k)

If you want to calculate, for example, the 60th percentile of the array D4:H4*I4/1000, your formula should look like this:

=PERCENTILE. EXC(D4:H4*I4/1000, 0.6)

This formula will calculate the 60th percentile of the values obtained by multiplying the range D4:H4 by I4 and then dividing the result by 1000.

Adjust the percentile value (0.6 in this case) according to your requirements.

I hope this helps! Please let me know how it goes and if you need any other assistance and I will be more than happy to help further.

Thank you

Best Regards, Shakiru

Answer 2

The PERCENTILE functions return a specific percentile, not the percentage of something (e.g. what value is the 25th percentile of these values). Can you elaborate - preferably with some data - what you are actually trying to calculate?