Request Assistance: Percentile w/ Multiple Conditions

[Ebiru2387]

Hi all,

I am racking my brain with trying to figure out how to calculate percentiles based on multiple conditions. I have scoured the internet and consistently come up against a formula similar to: =PERCENTILE(IF(A:A=A2,IF(B:B=B2)).9) which apparently will give me the percentile of the widgets for the company and site below. However i am confused by this formula because it doesn't seem to account for the array looking into the values for the widgets. This is one of the posts i was referencing and i tried using with CSE and without.

Percentile with multiple conditions

How can you use the percentile function with two conditions? I need to find let say the 90th percentile of test scores for Male students from the state of New York.

www.mrexcel.com

Anyway, I am sure i am missing some key aspect. Anyone who can assist would make my week! There are 4 columns below, with the first 3 what i am looking to analyze, and the 4th column where i want my percentile formula to go (they were manually calculated for this post to give you an idea of what results i am looking for). I am looking for the 90th percentile for each row and in the percentile column i want it to find the percentile for all rows where Company Name and Site Name match. For example we see the first two rows have the same percentile because they are looking at the widgets created for Company A, Site 1. The same applies for the next 2 rows as both rows have Company A Site 2. Company A Site 3 only has 1 row with those criteria, so that is the only row taken into consideration for the percentile formula.

I hope i am clear. If i have failed in explaining my issue properly kindly let me know and i will provide clarification.

Kind Regards,


- Brad

Company Name	Site Name	Widgets Created	Percentile
Company A	Site 1	6	5.6
Company A	Site 1	2	5.6
Company A	Site 2	7	6.6
Company A	Site 2	3	6.6
Company A	Site 3	2	2
Company B	Site 1	8	7.6
Company B	Site 1	4	7.6
Company B	Site 2	2	2
Company C	Site 1	1	1

 

[AhoyNC]

Like this?
This is an array formula so enter with CTRL-SHIFT-ENTER.

Book1
	A	B	C	D
1	Company Name	Site Name	Widgets Created	Percentile
2	Company A	Site 1	6	5.6
3	Company A	Site 1	2	5.6
4	Company A	Site 2	7	6.6
5	Company A	Site 2	3	6.6
6	Company A	Site 3	2	2
7	Company B	Site 1	8	7.6
8	Company B	Site 1	4	7.6
9	Company B	Site 2	2	2
10	Company C	Site 1	1	1
Sheet2

Cell Formulas
Range	Formula
D2:D10	D2	=PERCENTILE.INC(IF($A$2:$A$10=$A2,IF($B$2:$B$10=$B2,$C$2:$C$10)),0.9)

 

[Ebiru2387]

This! Yes thank you! Question though, how come we are able to use two IFs and require CSE to get this to work? One solution i also tried was using an AND in place of two IFs, but that didn't work for me either and am curious why not, and why this solution works instead?

 

[AhoyNC]

You need to return an array of values to the PERCENTILE function. The AND function will only return either a TRUE or FALSE.

IF(AND($A$2:$A$10=$A2,$B$2:$B$10=$B2),$C$2:$C$10) returns FALSE IF($A$2:$A$10=$A2,IF($B$2:$B$10=$B2,$C$2:$C$10)) returns an array {6;2;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}