Hi I'm trying to create a function or a macro that will auto update data. I have 4 different columns lets say red, white, blue, grey values in columns b-G. Each row is a date value. I need to calculate the running average daily of the last 4 dates and compare them to the past 10 dates averages.

This is what I have but the Average functions aren't working. this should be fairy simple for anyone with more experience. I just need a little help if anyone has some suggestions that would be amazing!



Dim lastR As Long
Dim last44 As Long
Dim average4 As Double
Dim average48 As Double


Range("G2").End(xlDown).Select

lastR = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

average4 = Average(Offset(lastR, lastR - 4, 0, 4, 1))

average44 = Average(Offset(lastR - 4, lastR - 40, 0, 40, 1))