Request Assistance: Percentile w/ Multiple Conditions

Ebiru2387

New Member
Joined
Sep 2, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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,


- Brad



Company NameSite NameWidgets CreatedPercentile
Company ASite 165.6
Company ASite 125.6
Company ASite 276.6
Company ASite 236.6
Company ASite 322
Company BSite 187.6
Company BSite 147.6
Company BSite 222
Company CSite 111
 

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
Joined
Oct 10, 2011
Messages
4,760
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 2, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Oct 10, 2011
Messages
4,760
Office Version
  1. 365
Platform
  1. Windows
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}
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top