Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a nested Address function for a reference
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a nested Address function for a reference
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect and Address in Reference to other sheets | Excel Worksheet Functions | |||
Need to reference existing functions in a custom function: possibl | Excel Worksheet Functions | |||
how to make cell address reference increment? | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
how do i reference multiple rows/columns with one function? | New Users to Excel |