Calculating auto filtered data
 

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


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200508/1

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,
--
George Nicholson

Remove 'Junk' from return address.


"Patty via OfficeKB.com" wrote in message
...
how do I calculate the median of filtered data without counting the hidden
columns?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200508/1

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.

Patty via OfficeKB.com wrote:
how do I calculate the median of filtered data without counting the hidden
columns?




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html