Median calculation by groups

[freefighter]

My question is about median calculation. If there is a simple list of data,
its easy to do. But when we want to calculate median of a variable for one or
two groups then I dont know what to do ? Say I have a 300 line list
containing selling prices of a product in 30 shops for 10 years. I want to
see the median of the seeling price of this product for each year. When I
create a pivot table for this data, there are a number of averages (such as
arithmetic mean) which could be calculated by any group you want. But there
is not a solution for calculating median according to grouped data. If there
is a solution please let me know
Thank you in advance

 

[Gary''s Student]

You can always use array formulae to set subgroups of data. Say we have in
columns A & B:

year value
2009 100
2009 101
2009 102
2008 300
2008 301
2008 302

and we want the median for the 2008 subgroup only. Enter:

=MEDIAN(IF(A1:A10=2008,B1:B10,""))

as an array formula. That means it must be entered with the
CNTRL-SHFT-ENTER key combination rather than just touching the ENTER key.

The formula should display 301.

This very simple technique can be expanded to include a variety of criteria
in defining the subgroup.

By the way, if you wanted the average rather than the median, a Pivot Table
is the better way.

 

[Bernd P]

Hello,

I suggest to take my UDF MedianIf:
http://www.sulprobil.com/html/medianif.html

Regards,
Bernd

 

[Shane Devenshire]

Hi,

Median is not an option in a pivot table. First it is not one of the 11
built-in functions, second you can't create a custom formula in the pivot
table to do this command.

You must resort to a formula, here is a similar array to the one you already
recieved:

=MEDIAN(IF(A1:A11=C1,B1:B11))

Where C1 contains the group name. As an array you must press
Shift+Ctrl+Enter.

 

[freefighter]

Thank you very much for your practical solutions. Thay really worked.
Although I knew a little bit about array formulas I was not quite familiar
and didn't think they could be used for such purpose. This is a brand new
experience for me. Still I think MS must add median calculation into pivots.
Regards