Average 12 week sales

W

Will

Hello,
Is there a way to create a spreadsheet to do the following?
-Get the last 12 week sales average on a continuous basis while
adding a new sales figure every week. In other words, say I've added
week #13 to the spreadsheet, is it possible to get the average sales
figure for weeks 2 through 13? And then when I add week #14, have it
average weeks 3 through 14?
Hope I haven't been too vague?

Tia,

....Will
 
V

Vaughan

If you are entering your weekly sales figures in the range B2:B53, then the following would work.

=AVERAGE(OFFSET($B$2,MATCH(0,$B$2:$B$53,-1)-12,0,12,1))

The MATCH function is finding the last cell with an entry in the sales range. OFFSET gets the array of the twelve cells in the range leading up to and including that cell. AVERAGE, well, calculates the average.
 
V

Vaughan

The formula above needs improvement, as it could cause a problem in the early weeks. I think the following would be better:

=AVERAGE(OFFSET($B$2,MAX(MATCH(0,$B$2:$B$53,-1)-12,0),0,12,1))
 
P

Peo Sjoblom

Another option

=AVERAGE(OFFSET($B$2,MAX(COUNT($B$2:$B$53)-12,0),,12,))

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



Vaughan said:
The formula above needs improvement, as it could cause a problem in the
Click to expand...
early weeks. I think the following would be better:
=AVERAGE(OFFSET($B$2,MAX(MATCH(0,$B$2:$B$53,-1)-12,0),0,12,1))
Click to expand...
range. OFFSET gets the array of the twelve cells in the range leading up to
and including that cell. AVERAGE, well, calculates the average.
 
P

Peo Sjoblom

Mo,

just shuffle the commas a bit

=AVERAGE(OFFSET($B$3,,MAX(COUNT($B$3:$Z$3)-12,0),,12))

will do the same starting in B3 going across to Z3

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


Mo said:
Hello..

I need to do exactly the same but with the data being on the same row but
Click to expand...
spread out on different colums so in this case the range would be for
example B3:Z3. This formula doesn't seem to work for my case, would you know
how to adapt it for it to work for me?
 
M

Mo

Thanks a lot for your help...!

Your are so helpful, would I dear asking something else...?

Is it possible in the forumla to make it choose from which column (corresponding to a month) it should start so for example asking it to check the actual month (updated in the document every month) and from there take the average of the last twelve months. This would be to be able later on to go back in the document and depending on the month you enter as the actual month it will re-calculate the 12 months average from that month.
Am I clear?

Thanks again for your help!
 
P

Peo Sjoblom

Assume you have the names of the months in B2:IV2, the values you want to
average in B3:IV3
and you put the name of the month in A1

=AVERAGE(OFFSET(OFFSET($B$3,,MATCH($A$1,$B$2:$IV$2,0)-1),,MAX(COUNT(OFFSET($
B$3,,MATCH($A$1,$B$2:$IV$2,0)-1):$IV$3)-12,0),,12))



--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



Mo said:
Thanks a lot for your help...!

Your are so helpful, would I dear asking something else...?

Is it possible in the forumla to make it choose from which column
Click to expand...
(corresponding to a month) it should start so for example asking it to check
the actual month (updated in the document every month) and from there take
the average of the last twelve months. This would be to be able later on to
go back in the document and depending on the month you enter as the actual
month it will re-calculate the 12 months average from that month.
 
Top