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
19,000
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,081,513
Messages
5,359,226
Members
400,521
Latest member
smarty1995

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top