subtotal median

[oscarbei]

we've got a excel sheet populated with column ROA and firm year, there's over 10000+rows of data and we have already sorted the data according to the firm year but now we want to get the median ROA for each year. and the subtotal function only have ave, is there a way aotomatically group the same year data and give us the ROA median.
Thanks

 

[Jerry Sullivan]

Hi and Welcome to the Board,

This thread might help.

http://www.mrexcel.com/forum/excel-...ic-applications-subtotal-median-function.html

Just ask if you want some help applying that approach for your purpose.

 

[T. Valko]

we've got a excel sheet populated with column ROA and firm year, there's over 10000+rows of data and we have already sorted the data according to the firm year but now we want to get the median ROA for each year. and the subtotal function only have ave, is there a way aotomatically group the same year data and give us the ROA median.
Thanks

See if this helps...

This array formula will return the median of the filtered (or unfiltered) range. The full range is C4:C20.

=MEDIAN(IF(SUBTOTAL(2,OFFSET(C4,ROW(C4:C20)-ROW(C4),0)),C4:C20))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.