PERCENTILE with an IF Clause
 

I'm attempting to find the Percentile of a column of numbers when a certain
condition is met based on another column. For some reason or other with the
Percentile I cannot get this to work. I use it all the time with many other
functions. What is it about the Percentile that produces the errors?

Here is my formula:

=IF($K$22:$K$4880=2,PERCENTILE($I$22:$I$4880,$A3))

The values in column K must be equal to two in order for the percentile
function to be applied to the values in column I. Where am I going wrong
here? Any help would be appreciated.

Answer: PERCENTILE with an IF Clause
 

1. Select a range of cells where you want to display the percentile values.
2. Type the following formula into the formula bar:
   
   
   Formula:

   ---

   =PERCENTILE(IF($K$22:$K$4880=2,$I$22:$I$4880),$A3) 


   ---

   
   
3. Press Ctrl + Shift + Enter to enter the formula as an array formula. You should see curly braces {} appear around the formula in the formula bar.

This formula uses the IF function to create an array of values from column I where the corresponding value in column K is equal to 2. The PERCENTILE function then calculates the percentile value for this array of values based on the value in cell A3.

Note that array formulas can be resource-intensive, so if you have a large data set, this formula may take some time to calculate. Also, make sure that the range of cells where you enter the formula is large enough to accommodate all the percentile values you want to calculate.

PERCENTILE with an IF Clause
 

Try this

=PERCENTILE(IF($K$22:$K$4880=2,$I$22:$I$4880),$A3)

it is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Zeelotes" wrote in message
...
I'm attempting to find the Percentile of a column of numbers when a
certain
condition is met based on another column. For some reason or other with
the
Percentile I cannot get this to work. I use it all the time with many
other
functions. What is it about the Percentile that produces the errors?

Here is my formula:

=IF($K$22:$K$4880=2,PERCENTILE($I$22:$I$4880,$A3))

The values in column K must be equal to two in order for the percentile
function to be applied to the values in column I. Where am I going wrong
here? Any help would be appreciated.

PERCENTILE with an IF Clause
 

HTH: Wow! That does it perfectly. I thought I had tried every combination
but I must have missed this one. Thanks a million!