CODEWITHSUNDEEP
exceldynamicrangedimensional
Evan
Evan

Reputation: 1

Excel - Average of Step-wise Dynamic Range

I am aiming to take the average of two-dimensional step-wise dynamic range. Step-wise meaning one to the right and one to left of a reference cell, x number of times. For example, if I have table below, I'd like to take the average of A1, B2, C3, D4, and E5 and these cells are part of a dynamic range. So if I were to change the reference cell to B2, it would take the average of B2, C3, D4, and E5. I've done research and OFFSET or a variation of Sumproduct may work; however, nothing in particular for a step-wise structure. Any help would be greatly appreciated!

Example Table

Upvotes: 0

Views: 48

Answers (1)

user4039065
user4039065

Reputation:

The following user defined function return an array of 'step-wise' numbers that you can SUM, AVERAGE, etc.

Function stepWise(rng As Range, steps As Long)
    Dim i As Long
    ReDim arr(1 To steps, 1 To 1) As Variant

    For i = 1 To steps
        arr(i, 1) = rng.Cells(1).Offset(i - 1, i - 1)
    Next i

    stepWise = arr
End Function

Paste the UDF code in a standard public Module code sheet.

Example to AVERAGE(A1,B2,C3,D4,E5):

=AVERAGE(stepWise(A1, 5))

Example to AVERAGE(B1,C2,D3,E4):

=AVERAGE(stepWise(B2, 4))

Upvotes: 0

Related Questions