Percentile on Aggregate data with multiple criteria

C

Chris Burgh

Hi All,

I'm currently trying to calcultate the percentile for individuals
within departments and then teams, all the data is within one dataset
and is in aggregate form. I currently have a formula which works on a
dummy set of data of say 20 lines, however as soon as I move this into
my main workbook I get a - response in each cell (when entered as an
array), if normally entered I get a figure however that is incorrect.

The formula is as follows:

=PERCENTILE(IF(($D$5:$D$30298=D5)*($H$5:$H$30298 =H5)*($I$5:$I$30298
=I5)*($P$5:$P$30298 =P5),IF(COLUMN(INDIRECT("1:"&MAX($M$5:$M$30298)))<=
$M$5:$M$30298,$O$5:$O$30298,""),""),$T$3)

I can't condense the dataset down as the other columns are needed for
other analysis. I'm hoping it is a simple fix but cannot see the wood
through the trees. Also this formula has to go against each individual
record due to the number of combinations and that the data is reported
by individual.

Any help would be greatly appreciated!!!

Chris
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top