Changing Averages

  • Thread starter Thread starter JimS
  • Start date Start date
J

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
 
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
 
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.
 
Back
Top