Calculating auto filtered data

  • Thread starter Patty via OfficeKB.com
  • Start date
P

Patty via OfficeKB.com

how do I calculate the median of filtered data without counting the hidden
columns?
 
G

George Nicholson

Take a look at the SUBTOTAL worksheet function.
= SUBTOTAL(1, A1:A500)
would give you the Average of all non-filtered data in the specified range.
(yeah, I know you asked for Median. Average is the best I can do.)

HTH,
 
D

Debra Dalgleish

Laurent Longre created a formula that lets you work with visible rows
after a filter. For information see Power Formula Technique in this
article at John Walkenbach's web site:

http://j-walk.com/ss/excel/eee/eee001.txt

For example, if your numbers are in cells E2:E200:

=MEDIAN(IF(SUBTOTAL(3,OFFSET(E2:E200,ROW(E2:E200)-MIN(ROW(E2:E200)),,1)),E2:E200,""))

It's an array formula, so after typing the formula, press
Ctrl+Shift+Enter to enter it.
 
Top