Auto Filter and Quartile/Percentile Functions
 

Howdy, I'm trying to calculate the 25th, 50th (ie median) and 75th percentile
of costs to maintain infrastructure using a dataset which has various (ie 11)
categories to analyse. I'm using the auto filter option to select each
category.

ie

Group Roads
1 $33,911
1 $27,500
1 $34,834
....
11 $11,782 etc

Any advice regarding an appropriate formula would be greatly appreciated.

Cheers
Pete

Auto Filter and Quartile/Percentile Functions
 

BTW the following are the commands I'm currently using. Obviously I can't
figure out how to calculate the quartiles and median for the Auto Filtered
data. Is the SUMPRODUCT function something that could assist? CP

CATEGORY (Selected via AutoFilter)
Subtotal =SUBTOTAL(109,L11:L198)
Average =SUBTOTAL(101,L11:L198)
Std Dev =SUBTOTAL(107,L11:L198)
Count =SUBTOTAL(103,L11:L189)

OVERALL
Total =SUM(L11:L198)
Mean =AVERAGE(L11:L198)
Std Dev =STDEVP(L11:L198)
Lwr Quartile=QUARTILE(L11:L198,1)
Median =MEDIAN(L25:L212)
Upr Quartile=QUARTILE(L25:L212,3)
Count =COUNTA(L25:L212)

"CP" wrote:

Howdy, I'm trying to calculate the 25th, 50th (ie median) and 75th percentile
of costs to maintain infrastructure using a dataset which has various (ie 11)
categories to analyse. I'm using the auto filter option to select each
category.

ie

Group Roads
1 $33,911
1 $27,500
1 $34,834
...
11 $11,782 etc

Any advice regarding an appropriate formula would be greatly appreciated.

Cheers
Pete