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