Using a nested Address function for a reference

[Sadler]

I am trying to graph a weighted moving average. I need the graph to show 12 months of history. With every new month (column) I add to the right off the data, I need the graph to drop the month's data from 13 month ago. I think I am close, but no cigar yet.

Example Included Graph test.xls

Here are my defined Names:
Date2=OFFSET(Sheet1!$B$2,0,0,1,COUNTA(Sheet1!$2:$2)-1)
Sales2=OFFSET(Sheet1!$B$3,0,0,1,COUNTA(Sheet1!$2:$2)-1)

Here is my current graph series equation:
=SERIES(Sheet1!$A$3,'Graph Test.xls'!Date2,'Graph Test.xls'!Sales2,1)

I thought there may be some way to use the address function, but It keeps giving me an error. I trie both as a defined name and jut in th regular equation.

=SERIES('Graph Test.xls'!(ADDRESS(3,(COUNT($3:$3))-12)),'Graph Test.xls'!Date2,'Book3 (version 1).xls'!Sales2,1)

Can you help?
thanks
Sadler

[bpeltzer]

I don't think you need the address function; instead, just modify the OFFSET
function you're using. As you've got it now, you're explictly picking up all
but one number from the data in rows 2 (date) and 3 (sales). As long as
you're series already has 12 months, you already know that's how many entries
you want, so the final argument to the offset will be 12. That just leaves
the questing of how many columns in from column B you want to start.
If the functions you showed worked up until you exceeded a year, I think the
function that works will be
Date2=OFFSET(Sheet1!$B$2,0,COUNTA(Sheet1!$2:$2)-13,1,12),
and a similar construct for Sales2, but anchored at $B$3 instead of $B$2.

"Sadler" wrote:

>
> I am trying to graph a weighted moving average. I need the graph to
> show 12 months of history. With every new month (column) I add to the
> right off the data, I need the graph to drop the month's data from 13
> month ago. I think I am close, but no cigar yet.
>
> Example Included Graph test.xls
>
> Here are my defined Names:
> Date2=OFFSET(Sheet1!$B$2,0,0,1,COUNTA(Sheet1!$2:$2)-1)
> Sales2=OFFSET(Sheet1!$B$3,0,0,1,COUNTA(Sheet1!$2:$2)-1)
>
> Here is my current graph series equation:
> =SERIES(Sheet1!$A$3,'Graph Test.xls'!Date2,'Graph Test.xls'!Sales2,1)
>
> I thought there may be some way to use the address function, but It
> keeps giving me an error. I trie both as a defined name and jut in th
> regular equation.
>
> =SERIES('Graph Test.xls'!(ADDRESS(3,(COUNT($3:$3))-12)),'Graph
> Test.xls'!Date2,'Book3 (version 1).xls'!Sales2,1)
>
> Can you help?
> thanks
> Sadler
>
>
> --
> Sadler
> ------------------------------------------------------------------------
> Sadler's Profile: http://www.excelforum.com/member.php...o&userid=29950
> View this thread: http://www.excelforum.com/showthread...hreadid=496489
>
>