Rolling graph

[4lowie]

Hi all</SPAN></SPAN>

I have a graph which tracks the following cells, these sit below dates in row 2 and the corresponding columns.</SPAN></SPAN>

=SERIES(Graphs!$U$3,,Graphs!$AX$3:$CK$3,1)</SPAN></SPAN>

Data AY3. Date AY2</SPAN></SPAN>
Data AZ3. Date AZ2</SPAN></SPAN>
Data BA3. Date BA2 and so on.</SPAN></SPAN>

What I’d like to do is have the graph show the last 30 days of data (from today backwards). A rolling graph with the 30 days of current data.</SPAN></SPAN>

How would I code this?</SPAN></SPAN>

Thanks. Dave</SPAN></SPAN>
Hi all</SPAN></SPAN>

I have a graph which tracks the following cells, these sit below date in row 2 and the corresponding columns.</SPAN></SPAN>

=SERIES(Graphs!$U$3,,Graphs!$AX$3:$CK$3,1)</SPAN></SPAN>

Data AY3. Date AY2</SPAN></SPAN>
Data AZ3. Date AZ2</SPAN></SPAN>
Data BA3. Date BA2 and so on.</SPAN></SPAN>

What I’d like to do is have the graph show the last 30 days of data (from today backwards). A rolling graph with the 30 days of current data.</SPAN></SPAN>

How would I code this?</SPAN></SPAN>

Thanks. Dave</SPAN></SPAN>

 

[Boswell]

You can set your chart's source range to be static (e.g. 2 rows of 30 cells: one row for date labels and one row for values) - and then in that range use something like VLOOKUP or OFFSET formula to 'lookup' the appropriate time period of data. That way you are never changing what range the chart is reading, rather you are having excel formulas bring the appropriate data to the chart's source range.

 

[4lowie]

Thanks, Any chance you could give me some example code so I can start trying to sort this out?

I'll need 9 rows (one being date heading) by 30 cells... As this data builds 2 charts plus 1 row for the "Target" value which is used on all charts.

Cheers

 

[Boswell]

Peltier has a pretty good example here: Chart the Last 12 Months Dynamically

His implementation automatically updates the chart each time you add a new day's (months in his example) data.

 

[4lowie]

Thanks, just having a look but it looks like a complete rebuild of my spread sheet as I ready have everything created. The data I want to graph is also in rows not columns.

Thanks for your help but I think this one is beyond me...... But I will keep playing!

Cheers, Dave

 

[dmariotti]

Thanks, just having a look but it looks like a complete rebuild of my spread sheet as I ready have everything created. The data I want to graph is also in rows not columns.

Thanks for your help but I think this one is beyond me...... But I will keep playing!

Cheers, Dave

ave,

The easiest way is to setup dynamic named ranges for the x-axis and each of the data ranges
eg for the X Axis define a name something like xVal=offset(Sheet!$a$1,0,Counta($1:$1)-30,1,30)

for the first data range eg Data1=offset(sheet1!$A$2,0,counta($2:$2)-30,1,30)
and so on for each range.

In the Graph series it should look something like this
=Series(,Book1!xval,Book1!Data1,1). you need to have the range preceded by the workbook name

see www.ozgrid.com/Excel/DynamicRanges.htm (sadly passed away Bunbury person)

 

[4lowie]

Thanks for the reply. I'll have a look but I'm starting to get out of my depth here........

Cheers

 

[hiker95]

Cross posted here:
Rolling graph

A message to forum cross posters, please read the following link:
Excelguru Help Site - A message to forum cross posters