Calculate median of multiple columns

Question

In my worksheet, named "Sheet1", Column A through Column J, each column contains various values (so they all have different number of rows).

How to calculate median of these columns and put the calculated median in row9, and columns M through V?
I known how to store the calculated median into desired location but really not sure how to calculate median.

I know this can be solved just by using excel median function, but this is not what I am trying to do. I have over 10+worksheets, so a VBA would be the most efficient.

Answer 1

All you need is to set M9 =MEDIAN(A2:A8) and have the A2:A8 set to the range you want.

Then just copy and paste that over to the other columns M9:V9. Excel will auto-change the columns to B,C,D, etc, and will fill in the info you want.

Edit for VBA:

Here is a quick and dirty VBA macro to apply them all:

Sub StacvQ()

    For SheetIndex = 1 To Sheets.Count
        Sheets(SheetIndex).Select
        Cells(9, 13).FormulaR1C1 = "=Median(R2C[-12]" & ":" & "R8C[-12])"
        Cells(9, 13).Copy
        Range(Cells(9, 13), Cells(9, 22)).Select
        ActiveSheet.Paste

    Next SheetIndex

End Sub