Changing Averages

[JimS]

If I want to average the numbers in these two columns that's easy.
But what if every day I add a new number to the bottom of each column,
and I only want to average the latest ten numbers? In other words,
the oldest number (on top) drops off of the average.

How would I do that?

85 86
86 85
105 92
90 100
90 86
106 83
97 85
87 101
92 89
90 93
94 97

 

[Dennis Chou]

=AVERAGE(OFFSET(a11,-10,0,10))

change a11 to the last range of data

 

[Bernie Deitrick]

Jim,

With a header value in A1, and numbers starting in A2

=AVERAGE(OFFSET(A2,COUNT(A:A)-10,0,10,1))

This assumes that you have no blanks in between values.


HTH,
Bernie
MS Excel MVP

 

[Jason]

Jim,

The solutions provided will average the 10 values at the bottom of the
lit, which is exactly what you need.

However, if each value relates to a time/day and you want the 10
*latest* values averaged then things such as your lists of values being
sorted would mean the formulas do not give you the average of the
*latest* ten values. Ideally you would have a related field containing
some means of indicating sequence, this needn't be a date - it could be
just an integer sequence, so using the first column of values you
posted, add another column:

Seq Value
1 85
2 86
3 105
4 90
5 90
6 106
7 97
8 87
9 92
10 90
11 94

Then you would need some means of averaging the 'values' based on the 10
highest sequence numbers.

 

[JimS]

Thanks, everyone. I learned something from each of you.