median calculation after auto filter

[hinchah]

i have 100 rows of data. when i use the autofilter my data changes to 15 rows. how can i write the median formula in cell B16 to find the median of the 15 rows of filtered data?? i am currently using this formula to find the average, =subtotal(1,B1:B100), but i cant figure out a way to do it for the median.

thanks

 

[Yogi Anand]

....
in the first formula i am using such a large range due to the fact that my data takes up rows 1 thru 10275, but i am really confused because the subtotal count function should allow me to put in the large range?? correct? such as my average calculation =subtotal(1,$C$1:$C$10275)??
....

Hi hinchah:

Are you using AUTOFILTER -- if you are then there is a limit of 1000 records that it can operate on.

You can switch over to Advanced Filter -- there you will not have this restriction.

I hope this helps.

 

[hinchah]

tom,

thanks for the code. i was wondering if the following is possible. here is my code:

Sub median()

Dim MedianCells As Range
Set MedianCells = Range("C2:C10274")
Range("c10315:c10353").Value = Application.WorksheetFunction.median(MedianCells.SpecialCells(xlCellTypeVisible))

Dim AverageCells As Range
Set AverageCells = Range("C2:C10274")
Range("c10276:c10314").Value = Application.WorksheetFunction.Average(AverageCells.SpecialCells(xlCellTypeVisible))
End Sub

these are used to calculate my mean and median. how do i make the macro run instead of pressing alt+F8 every time i change my autofiltered data??

thanks

 

[Tarnold]

And yet another option using VBA:

Sub MedianFiltered()
Dim MedianCells As Range
Set MedianCells = Range("B1:B100")
Range("B101").Value = Application.WorksheetFunction.Median(MedianCells.SpecialCells(xlCellTypeVisible))
End Sub

This solution worked perfectly for an issue I was having. Thanks.

 

[Tom Urtis]

Wow, thank you for the nice message Tarnold. I totally forgot about this one - - 6 years to the day + 3 and it resurfaces, great example of seeing users get their answer by searching for it. I neglected to put this answer in my library back in 2003 but it's definitely in there now, thanks again for the follow-up.

Edit, also made a note of Paddy's formula in #8 to also accomplish that, thanks.

 

[haughian]

The easiest way to do this (in my mind) is to click on the tab (from the little numbered ones on the left of the window that appear after you calculate a subtotal) that shows only the range of subtotals you've already calculated, and use the 'visible cells only' option (Edit > Go To > Special) when specifying your dataset for median calculation.