52 week average based on date
 

I have 110 (so far) columns with sales, each column being one week. I
know I can do a HLOOKUP for a column based on the date at the top of
the column.

What I was wondering is if there's a way to get a 52 week average using
the final week as the lookup date? For example, if I do a hlookup (or
something similar?) for May 1, 2004 I could get the average of that
column and the 51 columns before it (back to week ending May 10, 2003).
The reason I want something like an hlookup is because the dates will be
changing, for one use I want the most recent week and 51 previous, for
other reports I want a certain special date (new compitition, new ad
program) and the 51 weeks leading up to it.

Thanks in advance,
Tom


--
tomandrobyn
------------------------------------------------------------------------
tomandrobyn's Profile: http://www.excelforum.com/member.php...o&userid=13879
View this thread: http://www.excelforum.com/showthread...hreadid=376076

Hey, Tom-
I'm not sure I'm following your question, so let's see if I have it
right. You have historical data for 110 weeks of sales, which I mocked
up in a spreadsheet by using May 1 2005 in the far-right column.
Column A (in my sprdsht) is labeled 3/30/2003; column #110, or col DF,
is labeled 5/1/2005. I populated row 3 in each column with simulated
data.

If you need an average of the date in question and the prior 51 weeks,
could you simply write a formula for it and copy it into each column?
For instance, in my mockup the formula for the May 1 2005 52-week
average is =AVERAGE(BG3:DF3). Would your application be solved by
copying that formula into all the previous columns? This assumes your
columns are contiguous (no gaps) and fully populated. Would this do it
for you, or do I still not get it?

Dave O

On Thu, 2 Jun 2005 13:26:41 -0500, tomandrobyn
wrote:


I have 110 (so far) columns with sales, each column being one week. I
know I can do a HLOOKUP for a column based on the date at the top of
the column.

What I was wondering is if there's a way to get a 52 week average using
the final week as the lookup date? For example, if I do a hlookup (or
something similar?) for May 1, 2004 I could get the average of that
column and the 51 columns before it (back to week ending May 10, 2003).
The reason I want something like an hlookup is because the dates will be
changing, for one use I want the most recent week and 51 previous, for
other reports I want a certain special date (new compitition, new ad
program) and the 51 weeks leading up to it.

Thanks in advance,
Tom

If I understand you correctly, you could use the MATCH function to find the
correct week's column; and the OFFSET function to decide the range you wish to
average.

For example, if your column headers are Dates in Row 1 and are in increasing
order going to the right, and you wanted to average the contents of the 10 rows
below the headers for the index week and the 51 preceding weeks, a formula of
the type:

=AVERAGE(OFFSET(A1,1,MATCH(lookup_date,1:1)-1,10,-52))

would do that.


--ron

Ron, I think you have the right idea. I'm just playing around with it a
bit to suit it to my spreadsheet... plus I had to understand OFFSET as
I hadn't used it. I'll post back if I have any issues.

Dave, I thought of doing someingthing along that idea if I had too,
just wanted something more dynamic so that I could easily change dates,
maybe do the occasional 6 week average, etc.

Thanks to both for the quick responses!

Tom


--
tomandrobyn
------------------------------------------------------------------------
tomandrobyn's Profile: http://www.excelforum.com/member.php...o&userid=13879
View this thread: http://www.excelforum.com/showthread...hreadid=376076

Ron (or anyone else who can help),

I tried using the formula from one spreadsheet to another, with both
open it's working great, but if I just open the spreadsheet that has
the formula it turns up as a VALUE error. Looking at the OFFSET
function in the Function Arguements window is says Formula Result=
Volatile. Any idea how to stop this from happening? My formula is...

=AVERAGE(OFFSET('S:\Analyst\Weekly Sales\Sales History\[Sales
History.xls]Sales'!$A$1,3,MATCH($A$1,'S:\Analyst\Weekly Sales\Sales
History\[Sales History.xls]Sales'!$2:$2)-1,1,-52))


--
tomandrobyn
------------------------------------------------------------------------
tomandrobyn's Profile: http://www.excelforum.com/member.php...o&userid=13879
View this thread: http://www.excelforum.com/showthread...hreadid=376076

On Fri, 3 Jun 2005 10:56:32 -0500, tomandrobyn
wrote:


Ron (or anyone else who can help),

I tried using the formula from one spreadsheet to another, with both
open it's working great, but if I just open the spreadsheet that has
the formula it turns up as a VALUE error. Looking at the OFFSET
function in the Function Arguements window is says Formula Result=
Volatile. Any idea how to stop this from happening? My formula is...

=AVERAGE(OFFSET('S:\Analyst\Weekly Sales\Sales History\[Sales
History.xls]Sales'!$A$1,3,MATCH($A$1,'S:\Analyst\Weekly Sales\Sales
History\[Sales History.xls]Sales'!$2:$2)-1,1,-52))

I seem to recall reading that Excel has a problem dealing with multi-cell range
references in closed workbooks, although it can deal with arrays.

If it's not possible to always have the source workbook open, (perhaps by using
a workspace), then we may have to come up with a different solution.

Will you only be averaging the contents of one row?


--ron