subtotal question

ghardy

New Member
Joined
Nov 21, 2005
Messages
1
I have a list of times could be either in [h]:mm format or in minutes formatted as a number. These times represent the length of stay in a service process. With subtotal function I can determine “average, stddev, min and max instantly as I filter the data set based upon selection criteria. It is important to also know the 95th percentile of the filtered-selected data. Is there some way to build a calculation of subtotal functions or another way to display the answer each time I change the selection criteria so that the formula only works against the filtered data the way the subtotal function works?.

additionally I need to put this into a macro where the range for the subtotal will change dynamically. each day the column count can be a different lenght and I need to have the cell range expand or contract similar to the <control-shift-arrow down> method to capture the entire column reguardless of the changing size. this way the macro can be run and it wont be necessary to rekey the formula each day.

Thank you

George Hardy
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
18,920
Try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=PERCENTILE(IF(SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),0,1)),Range),K)

...where K is the percentile value.

Hope this helps!
 

Forum statistics

Threads
1,077,829
Messages
5,336,647
Members
399,094
Latest member
Learner2019

Some videos you may like

This Week's Hot Topics

Top