Hi,

I have a large data set to which i am calculating the 85th percentile using the formula below.

=PERCENTILE(IF('April-October 14'!$AJ$1:$AJ$59692=Comparison!B65,'April-October 14'!$AG$2:$AG$59693),0.85)

Note: entered using 'CTRL SHIFT ENTER'.

Note Comparison!B65 is a value of 2 corresponding to a weekday which is ofcourse a monday. I have a filter on which states whether day is weekday, weekend or bank holiday. I need to calculate the above formula IF 'April-October 14'!$AY$2:$AY$59693="WEEKDAY".

Ive tried various methods i.e. AND, but it is not correct E.G.

=PERCENTILE(IF(AND('April-October 14'!$AJ$1:$AJ$59692=Comparison!B65,'April-October 14'!$AY$2:$AY$59693="WEEKDAY".
),'April-October 14'!$AG$2:$AG$59693),0.85)



Effectively i am looking at consultation times on a weekday, on a day by day basis. The bank holiday data is treated as a weekend so i need to only look at data on non bank holiday mondays. I have created the pivots with desired constraints, drilled down and worked out the desired values but was wondering if this can be calculated using a formula, as it would be useful going forward with our work and save some time in the long run.

Any help would be greatly appreciated - this is my first POST!