CODEWITHSUNDEEP
vbaexcel
CCM
CCM

Reputation: 136

Excel VBA AVERAGE(variable range)

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 ?

Upvotes: 3

Views: 12192

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37470

Change Selection.Value = "=RC[-2]-RC[-1]" to Selection.Value = Offset(0, -2).Value - Offset(0, -1).Value.

Then, declare some range variable: Dim rng As Range, then set it to range with data from last 12 months and calculate average:

Set rng = Range(Selection.Offset(0, -13), Selection.Offset(0, -2))
p = Application.Average(rng)

Upvotes: 2

CCM
CCM

Reputation: 136

Found a workaround with ActiveCell.Offset(0, 1).Value = "=AVERAGE(RC[-13]:RC[-2])".

Thanks for your time guys!

Upvotes: 0

Related Questions