Limiting an AVERAGE to a maximum number of values?

  • Thread starter Thread starter Scott Lyon
  • Start date Start date
S

Scott Lyon

I've got a table for which I want to find the average of the right-most
(filled) 12 columns.


To be specific, the data in the table is organized by month, and I want to,
for each given row, see what the average is for the prior year (12 months).


For example, I have it set up that:

Column J represents January 2003 data
Column K represents February 2003 data
Column L represents March 2003 data
And so on...


For each row, how do I average just the last 12 months, bearing in mind that
if less than 12 months are used, it will average JUST those fields.


For example, if (starting with Column J), I have the following amounts
(comma delimited):
5, 5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 13, 13

I want to get a value of 3 (average of 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 13, and
13 - notice: not including the first two values - 5 and 5- as all I wanted
was the right-most 12 values).

If the values are:
1, 1, 1, 1, 1 (and the rest still blank/not yet filled in)

I want a value of 1 (average of 1, 1, 1, 1, and 1)


I don't mind if I have to use VBA code to do this, as I'm comfortable with
VBA. But if there's a standard way to do it, I'd prefer something built-in
instead of something I'd write myself.


Is there an easy way to do this?


Thanks!
 
Hi Scott
if your data starts in column J try the following formula for row 1
=AVERAGE(OFFSET($J$1,0,COUNTA($J$1:$IV$1)-1,1,-12))
 
Back
Top