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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,403
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,509
Messages
5,572,582
Members
412,472
Latest member
stgermainr
Top