Create Function for Average of Specified Range in VBA

Question

Please help!

I need to create a function in VBA that allows me to return an average based on a specified range of cells.

For this function, I want to specify the range of cells, have the code ID the active cell within that range, and then average the cells above and below based off of the active cell. The image attached shows what I am trying to do.

Here is the VBA Function I have thus far, but I cannot seem to get it to work at all:

VBA:

Function SpecialAVERAGE(rng As Range, LowerBound As Integer, UpperBound As Integer) As Double

Dim Origin As Range
Dim Total As Double
Dim Count As Integer
Dim Lower As Long
Dim Upper As Long
Dim A As Long
Dim B As Long

Set Origin = ActiveCell

For Each Cell In rng
    Lower = Range(Cell.Offset(-LowerBound, 0), Cells(Origin.Row, 1))
    A = wf.Sum(Lower)
    Upper = Range(Cell.Offset(UpperBound, 0), Cells(Origin.Row, 1))
    B = wf.Sum(Upper)
    Total = Total + Cell.Value + A + B
    Count = Count + 1

Next Cell

SpecialAVERAGE = Total / Count

End Function

Any direction is greatly appreciated!! I understand that =AVERAGE(OFFSET(.... ))) may help with this, but I haven't found the solution there either.

Thank you!!

dwirony · Accepted Answer · 2019-02-20 22:34:54Z

1

No need to overcomplicate - just use the lower and upper boundary to widen your row values for WorksheetFunction.Average, and use one cell (instead of the whole column) for the reference range:

Function SPECIALAVERAGE(rng As Range, LowerBound As Integer, UpperBound As Integer) As Double

    SPECIALAVERAGE = WorksheetFunction.Average(Range(Cells(rng.Row - LowerBound, rng.Column), Cells(rng.Row + UpperBound, rng.Column)))

End Function

answered Feb 20, 2019 at 22:34

dwirony

5,4604 gold badges26 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user10914349 Over a year ago

Thank you so much! I will give this a try!!

user10914349 Over a year ago

Did exactly what I needed! Thank you!!

Collectives™ on Stack Overflow

Create Function for Average of Specified Range in VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related