# Request Assistance: Percentile w/ Multiple Conditions

#### Ebiru2387

##### New Member
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.

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,

 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

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### AhoyNC

##### Well-known Member
Like this?
This is an array formula so enter with CTRL-SHIFT-ENTER.
Book1
ABCD
1Company NameSite NameWidgets CreatedPercentile
2Company ASite 165.6
3Company ASite 125.6
4Company ASite 276.6
5Company ASite 236.6
6Company ASite 322
7Company BSite 187.6
8Company BSite 147.6
9Company BSite 222
10Company CSite 111
Sheet2
Cell Formulas
RangeFormula
D2:D10D2=PERCENTILE.INC(IF(\$A\$2:\$A\$10=\$A2,IF(\$B\$2:\$B\$10=\$B2,\$C\$2:\$C\$10)),0.9)

#### Ebiru2387

##### New Member
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

##### Well-known Member
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}

Replies
0
Views
141
Replies
8
Views
99
Replies
3
Views
87
Replies
2
Views
89
Replies
1
Views
67

### Forum statistics

1,130,214
Messages
5,640,910
Members
417,178
Latest member
HelpMeExcelExperts

### 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.

### Which adblocker are you using?

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

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