Search code examples
excelexcel-formulapercentileexcel-indirect

Calculating 95 Percentile without ControlShiftEnter (CSE, Array formulas) in Excel

I have a set of data like this

enter image description here

Which I am exporting from a tool. This tool can output excel formulas, however this means CSE formulas are not an option. Similarly, the tool has no options for percentile calculations

I need to calculate Percentile (by org unit) of the people counts. This must be a real number. Using array formulas this looks like this:

{=LARGE(IF(D10:D27="MyOrg",F10:F27),(ROUND((COUNTIF(D10:D27, (INDIRECT("RC[-5]",0))))-(J3*COUNTIF(D10:D27, (INDIRECT("RC[-5]",0)))),0)+1))}

enter image description here

This is producing the correct result, though without the {}, it gets the large of the entire set.

As I understand it, Index can be used to mimic CSE functions (I had a look at this https://excelxor.com/2014/09/01/index-an-alternative-to-array-cse-formulas/ ) but I am struggling to see exactly how to (or if I even can) apply that here to solve this problem.

Thanks for any help you can provide. Happy to provide the faux sheet I am using to work off of.

Edits:

Cell J3 is enter image description here

INDIRECT("RC[-5]",0) returns MyOrg (in the working Large formula)

Position formula is =ROUND((COUNTIF(D10:D27, (INDIRECT("RC[-4]",0))))-(J3*COUNTIF(D10:D27, (INDIRECT("RC[-4]",0)))),0)+1

It was a stepping stone to properly get the large formula to produce the correct. We need to add 1 as Large ranks from 1 to N+1 not 0 to N

Solution

  • Two examples of non CSE-entered formulas:

    enter image description here

    Formula in E1:

    =AGGREGATE(14,3,(A2:A19="MyOrg")*C2:C19,1)

    Change the 1 for nth largest value.