last 20-day average of numbers after updating everyday

[ffarshadd]

I'll appreciate your help
I have an excel file in which there is a column with some numbers. a new row is added to the file everyday.
I want to calculate the last 20-day average of this column every day by pressing a button.

[Bob Phillips]

Just use a formula


=SUM(INDEX(A:A,COUNTA(A:A)-19):INDEX(A:A,COUNTA(A:A)))

[SamT]

Actually, I prefer a User Defined Function that can be used in a formula in a cell and doesn't need any buttons pushed.

Public Function BowlingAverage(AnyCellInColumnToFindAverageIn As Range)As Double

Const NumAve As Long = 20 'Adjust when you need an average of more or less than 20 values
Dim MyColumn As Range
Dim LastInColumn As Range

Set MyColumn = AnyCellInColumnToFindAverageIn.EntireColumn 'You an edit this long name as you see fit in both places, here and above

Set LastInColumn = MyColumn.Cells(Cells.Count).End(xlUp)
BowlingAverage = Sum(Range(LastInColumn, LastInColumn.Offset(1-NumAve))) / NumAve
End Function


Place the above Function Code in a Standard Module.

To use: In the Cell that gets the Average of the last 20 cells in whatever column, use this formula
=BowlingAverage(A1) Where the numbers are in column A. If they happen to be in Column x, then use (X1). Note that you can also use the Column Address (A:A).

You can type the formula in, or you can use the Insert Function ability of Excel to select User Defined Functions.

[SamT]

xld, I think he wants


=average(INDEX(A:A,COUNTA(A:A)-19):INDEX(A:A,COUNTA(A:A)))
But I like it.


Wontze in a while, I think I wish I knew more about formulas... Nah, I'm happy with me the way I am.

[Bob Phillips]

xld, I think he wants


=average(INDEX(A:A,COUNTA(A:A)-19):INDEX(A:A,COUNTA(A:A)))


Oh yeah, so he does. Thanks!