sum 12 numbers
 

I have a spreadsheet recording monthly sales over several years.

I can easily sum these and find the average sales per month but what I would
like to be able to do is find the sum or average of just the last twelve.
Again this is easy if I am prepared to edit the average formula each time I
enter a new monthly figure but I should like to automate this.

sum 12 numbers
 

If your values are in column N, with the last at the bottom of the column then:

=AVERAGE(OFFSET(N1,MAX(0,COUNTA(N:N)-12),0,12,1))

If your most recent data is always inserted at the top of column N, then:

=AVERAGE(N1:N12)
--
Gary''s Student - gsnu200768


"Ian" wrote:

I have a spreadsheet recording monthly sales over several years.

I can easily sum these and find the average sales per month but what I would
like to be able to do is find the sum or average of just the last twelve.
Again this is easy if I am prepared to edit the average formula each time I
enter a new monthly figure but I should like to automate this.

sum 12 numbers
 

Hi Ian,

You don't say how yout data is setup, but this process may help.

Say you have the last four years data in A2:A49.
Put this formula in B13.
=IF(A13="","",SUM(A2:A13))
Grab the fill handle and drag down to say B100

Now as you add data in A50, A51 etc. the sum of the last 12
entries will appear in B50, B51 etc.

Put the same formula in column C and use the same process
but change SUM to AVERAGE.

Adapt the process to suit your data.

HTH
Martin


"Ian" wrote in message
...
I have a spreadsheet recording monthly sales over several years.

I can easily sum these and find the average sales per month but what I
would
like to be able to do is find the sum or average of just the last twelve.
Again this is easy if I am prepared to edit the average formula each time
I
enter a new monthly figure but I should like to automate this.

sum 12 numbers
 

Many thanks to Martin for elegant solutions that I should have been able to
think of but didn't. Thank you also to Gary's student whose reply has had me
scratching my head for some while until the penny eventually dropped. I am
grateful to you both!

sum 12 numbers
 

You're welcome Ian, thanks for posting back.
I'm sure Gary's student appreciates it as much as I do.

Regards
Martin


"Ian" wrote in message
...

Many thanks to Martin for elegant solutions that I should have been able
to
think of but didn't. Thank you also to Gary's student whose reply has had
me
scratching my head for some while until the penny eventually dropped. I
am
grateful to you both!