Calculating average in excel with an OFFSET range

[gentles]

Hi all, this looks like a great forum!

I want to calculate the average for the 5 previous days only IF it is the last day of the month. I want to display that average next to the last day of each month.

Here is my line of code for the average function, I am not sure how to write it when it refers to an offset of the active cell rather than a set range. I will add a loop after i find this to fill the entire range

ActiveCell.Offset(0, 1) = Application.WorksheetFunction.Average(Range(ActiveCell.Offset(1, 1).Value: ActiveCell.Offset(5, 1)))

Sorry if this is a very confusing question, I have done a lot of searching about this...and it seems quite specific.

Thanks!