Average (2000)

Topic

New Reply

I want to average a series of numbers but I only want to average 5 numbers up in the column. When I add another number, I only want the previous 4 numbers plus the new number I am adding in. How would I do this????

Reply | Quote

Replies

Assuming your column of numbers starts in A1 and has no blanks except after the last number, you can use this formula to average the last five numbers in the column:

=AVERAGE(OFFSET($A$1,COUNT($A:$A)-5,0,5,1))

HTH

Reply | Quote

One of the neat thing about Excel (and probably its competitors) is the relative referencing for equations makes this more or less simple …

Assuming you have your numbers starting in Row 1, Column A, and continuing down that column. If you type in =average(a1:a5) into Row 5, Column B, and then copy that formula into Row 6, Column B, Row 7, etc. you’ll get what you need — a running-average of five the “current” value plus the previous 4.

This “solution” has the advantage that you can have missing values … which might be important say in an application were the values are daily readings of something … that might have a missing value sometimes … the result will (as long at the corresponding value in Column A is blank) be an average of fewer than 5 values (which may or may not be what you want).

Hope this helps.

Reply | Quote