Average of the last n values in a column

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hello all,
I need to determine the average of the last 5
values of a column for a rolling average. I can get the
last one, but am not sure how to get the average of the
last n values. I've been toying with the offset function
and didn't find anything on Chip's site. Any help would be
appreciated.

Mike
 
Assuming data in Col A, starting A1, NO Blanks in the data and nothing below
it:-

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

or

=AVERAGE(OFFSET($A$1,COUNT(A:A)-5,,5))
 
And with your n value in say cell C1

=AVERAGE(OFFSET($A$1,COUNT(A:A)-1,,-C1))
or
=AVERAGE(OFFSET($A$1,COUNT(A:A)-C1,,C1))
 
Ken, Thanks for the fast response. Actually there are a
lot of empty cells in the column, but there is nothing
below the range. Currently my data is in Column T and the
last value is at C3334. Each day a new row is added, but
column T may or maynot contain a new value. There are no
specific intervals for column T to contain a value.
Currently I'm using the formula below to pull the last
known value in T, and it accounts for blank cells. I'm not
sure how to pull the last 5 values and average them. To be
honest, I've only got a 50% grasp on how the below formula
works so that doesn't help me much to modify it.

=OFFSET(T8,MATCH(MAX(T8:T4007)+1,T8:T4007,1)-1,0)

Thanks,
Mike
 
Try this array formula

=AVERAGE(A65535:INDEX(A1:A65535,LARGE(ROW(1:65535)*(A1:A65535<>""),5)))

will average the last 5 non blank cells

must be entered with ctrl + shift & enter

=AVERAGE(A65535:INDEX(A1:A65535,LARGE(ROW(1:65535)*(A1:A65535<>""),C1)))

with the last nth non blank cells

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Thanks for the feedback, this version doesn't have to be array entered

=AVERAGE(A65535:INDEX(A1:A65535,SUMPRODUCT(LARGE(ROW(1:65535)*(A1:A65535<>""
),C1))))

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Back
Top