Search code examples
excelautomationspssvba

Automation help, excel or SPSS, need median calculated for every fifth value

I have one big column, I need the median of every five/fifth value in a list. I have excel and SPSS. How can I do this without doing it manually? 600 values. When calculated I should have a column with 120 median values.

Solution

  • If your data in column A. The result of median will be recorded in column c.

    Sub test()
    Dim rngDB As Range, rng As Range
    Dim vR() As Variant
    Dim i As Long, n As Long

    Set rngDB = Range("a1", Range("a" & Rows.Count).End(xlUp))

    For i = 1 To rngDB.Rows.Count Step 5
        Set rng = Range("a" & i).Resize(5)
        n = n + 1
        ReDim Preserve vR(1 To n)
        vR(n) = WorksheetFunction.Median(rng)
    Next i
    Range("c1").Resize(n) = WorksheetFunction.Transpose(vR)

End Sub