Moving graph

Posted by Mike on May 28, 2001 3:16 PM

I need to graph the last 13 values from a list of data, and I would like the graph to automatically add a new value that is added to the list, and at the same time drop off the 1st value.

This is so that at any time the last 13 values in the data list are shown in the graph. Anyone got any thoughts? Does this demand a macro solution?

Thanks

-Mike

Posted by Dave Hawley on May 28, 2001 3:41 PM

Hi Mike

I would say this could be done without any VBA.

Try this, to see what I mean.

Put 13 entries in A1:A13

Go to Insert>Name>Define and type in: MovingRange

Then in the Refers to box type:
=Offset($A$1,Counta($A:$A)-13,0,13,1)

Click Add, then Ok.

Now refer your chart (or any chart) to the range, then add another entry.

I have a few other examples of these type of things on my Website under "Chart Tips and Trick" that may interest you.

Dave

OzGrid Business Applications

Posted by Mike Larsen on May 28, 2001 4:17 PM

Thanks for that Dave - it seems to work ok.

Is there any way I can update it easily? I seem to have to go back into the source data of the chart and re-enter the range ('MovingRange') before it updates the chart? Ideally I would like to put in an update button so that when the user has entered the latest data they can generate and update the graph either automatically or with the press of a button.

Cheers

-Mike

Posted by Mike Larsen on May 28, 2001 6:03 PM

Don't worry - figured it out (well daahhh all I had to do was replace the series with the range name).

Thanks

-Mike