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" <forum@OfficeKB.com> wrote in message
news:534776E33C7FB@OfficeKB.com...
> 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