Need to compute Percentile column when those items in another column match a value

DaveKey

New Member
Joined
Jun 14, 2015
Messages
2
Essentially I need a =SUMIF type method for a different function, in this case the PERCENTILE function.

ab
15yellow
27green
311yellow
415red
519yellow
621green

<tbody>
</tbody>












If I wanted to SUM the yellow numbers (which I don't want to do, but in similar),
I'd use the function =SUMIF(b1:b6,"yellow",a1:a6).

In my case, I want to compute the PERCENTILE for each of the unique values in column B. So I want to create a formula for each of the unique values I have in the table: this would be the name of my imaginary function: =PERCENTILEIF(b1:b6,40%,"yellow",a1:a6).

This would return the value of the 40% percentile of the array (though obviously not enough data to compute that percentile).

If there was a function that returned an array derived based on matching values of another column in the same array, that would be ideal. I would then put that array in the =PERCENTILE function.
 
Last edited:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Use the array function to determine the 40% percentile of those rows with the label of "red".

={PERCENTILE(IF(b1:b6="red",a1:b6),40%)}

Enter the equation (without brackets {}) and hit SHIFT + CTRL + ENTER to tell Excel it is an array function.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top