Excel 2007 : Finding The Average of the Last 20 Cells in a Column

[Ebikeguy]

Hi All,

This should be simple, but I'm being extra gumbylike today, so I cannot figure it out.

I am trying to find the value of the last 20 cells in a column and paste it into one cell. The number of rows in the spreadsheet will vary from instance to instance.

Any help or suggestions you could provide would be much appreciated.

Just to prove what a gumby I am, here is my current code from my attempts to make this happen:

Sub Determine_Offset()

Dim LastTwenty As Range
Dim LastTwentyAverage As Double

Range("G1").Select
Range("F1").End(xlDown).Select
Set LastTwenty = ActiveCell.Offset(-20, 0).Range("A1:A21")
'LastTwentyAverage = [Average(Range("F1:F21"))]
'ActiveCell.Value = LastTwentyAverage
ActiveCell.Formula "=AVERAGE(Range(LastTwenty).Cells)"


End Sub

Thanks!

Ebikeguy

[tom1977]

hi I do not understand Your code but let's say You have data in column A than below code will count average of last 20 cells and will put the result in B1

Sub Determine_Offset()

Dim last As Long
last = Cells(Rows.Count, 1).End(xlUp).Row
Range("b1") = Application.WorksheetFunction.Average(Range("a" & last & ":" & "a" & last - 19))

End Sub

[Ebikeguy]

Worked perfectly! Thank you very much! I'm so glad I found this forum!