Excel VBA AVERAGE(variable range)

Question

I'm trying to use an Excel formula that averages the last 12 month values and compares it to the current month value (in this case RC[-2]-RC[-1] and a 30% variance).

The issue is that the row changes, it is nested in a loop.

Below the code:

Dim i As Long 
Dim o As Double 
Dim p As Double

'some code here

For i = 1 To n
    Selection.Value = "=RC[-2]-RC[-1]"
    o = ActiveCell.Value
    p = Application.Formula.Average(Range("RC[-13]", "RC[-2]"))
    If (o >= (p + p * 3 / 10)) Or (o < (p + p * 3 / 10)) Then
        ActiveCell.Font.Color = vbRed
    End If
    ActiveCell.Offset(1, 0).Select
Next i

Any ideas on how to define that average without a separate average function ?

Excel VBA AVERAGE(variable range)

2 Answers