Average of the last n values in a column

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
 
K

Ken Wright

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))
 
K

Ken Wright

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))
 
M

Mike

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
 
P

Peo Sjoblom

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

calculate last 4 data points in a column (with sometimes blank rows) 7
AverageIf function with multiple columns 1
Averaging data across a date range 1
Help requested for nested conditional formulas referencing other c 3
7 Day Pacing Formula Help 2
Nesting SUBTOTAL with SUMPRODUCT? 1
How to make a cell show average of the last 5 entries in a column? 1
Averaging two ranges on the same column (excluding zeros) 2

Top