Thread: watch a curve change over time like a movie

I would like to be able to view my data set such that I can view the curve
change over time instead of viewing for example 10 curves on one graph. I
would like to be able to select the speed at which the curve is refreshed
(new data set), and the direction (so I can run the curve forward and
backward in time).

In essence, I have monthly data that represents one curve. I would like to
visually watch the curve change month by month instead of plotting one curve
for each month, I see one graph with the curve in motion over my data set
(time).

Randy -

You set this up with defined names, such that the Y values is defined
based on an integer value in another cell. Something like

Name: YValues
RefersTo:
=Offset(Sheet1!$A$2:$A$12,0,Sheet1!$E$1)

As the value in E1 increases, the range defined by the Offset is further
and further to the right. Your chart is based on these YValues.

Then you have a macro that moves forwards or backwards through the data
by changing the value in E1 by a certain amount after a given delay.
Shorter delays mean the curve changes more quickly.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

RandyBarrett wrote:

> I would like to be able to view my data set such that I can view the curve
> change over time instead of viewing for example 10 curves on one graph. I
> would like to be able to select the speed at which the curve is refreshed
> (new data set), and the direction (so I can run the curve forward and
> backward in time).
>
> In essence, I have monthly data that represents one curve. I would like to
> visually watch the curve change month by month instead of plotting one curve
> for each month, I see one graph with the curve in motion over my data set
> (time).

For a solution that uses iteration (it has its own pros and cons) visit
the Excel Tips section (http://www.tushar-
mehta.com/excel/tips/index.html) and scroll down to 'Iteration to
animate a chart'

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <08A9A507-4061-4D22-92E1-A3044D1F15C5@microsoft.com>,
RandyBarrett@discussions.microsoft.com says...
> I would like to be able to view my data set such that I can view the curve
> change over time instead of viewing for example 10 curves on one graph. I
> would like to be able to select the speed at which the curve is refreshed
> (new data set), and the direction (so I can run the curve forward and
> backward in time).
>
> In essence, I have monthly data that represents one curve. I would like to
> visually watch the curve change month by month instead of plotting one curve
> for each month, I see one graph with the curve in motion over my data set
> (time).
>

Thank you for your quick resonse, here is what I did:

YValue is the defined name for the offset as you suggested.
I created a graph and typed "YValue" into the Data Range
I entered an integer into the referenced cell in the offset formula.
It worked great ... but

I figured that I should be able to enter a new integer into the referenced
cell and the graph would automatically change the curve ... it did not.

I went into Chart, Source Data, and the Data Range was the actual range
calculated by the last integer offset (Data!$D$1:$M$1) and NOT the name
"YValue" that I originally entered.

It seems that if "YValue" would remain the Source Data, then I could simply
enter a new integer and it would automatically update the chart. Then I
could create the macro as you have suggested.

What am I doing wrong?

"Jon Peltier" wrote:

> Randy -
>
> You set this up with defined names, such that the Y values is defined
> based on an integer value in another cell. Something like
>
> Name: YValues
> RefersTo:
> =Offset(Sheet1!$A$2:$A$12,0,Sheet1!$E$1)
>
> As the value in E1 increases, the range defined by the Offset is further
> and further to the right. Your chart is based on these YValues.
>
> Then you have a macro that moves forwards or backwards through the data
> by changing the value in E1 by a certain amount after a given delay.
> Shorter delays mean the curve changes more quickly.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> RandyBarrett wrote:
>
> > I would like to be able to view my data set such that I can view the curve
> > change over time instead of viewing for example 10 curves on one graph. I
> > would like to be able to select the speed at which the curve is refreshed
> > (new data set), and the direction (so I can run the curve forward and
> > backward in time).
> >
> > In essence, I have monthly data that represents one curve. I would like to
> > visually watch the curve change month by month instead of plotting one curve
> > for each month, I see one graph with the curve in motion over my data set
> > (time).
>

Thank you for the reply. Jon's suggestion worked for my application but I
enjoyed your article and may use it in the future.
Randy

"Tushar Mehta" wrote:

> For a solution that uses iteration (it has its own pros and cons) visit
> the Excel Tips section (http://www.tushar-
> mehta.com/excel/tips/index.html) and scroll down to 'Iteration to
> animate a chart'
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
>
> In article <08A9A507-4061-4D22-92E1-A3044D1F15C5@microsoft.com>,
> RandyBarrett@discussions.microsoft.com says...
> > I would like to be able to view my data set such that I can view the curve
> > change over time instead of viewing for example 10 curves on one graph. I
> > would like to be able to select the speed at which the curve is refreshed
> > (new data set), and the direction (so I can run the curve forward and
> > backward in time).
> >
> > In essence, I have monthly data that represents one curve. I would like to
> > visually watch the curve change month by month instead of plotting one curve
> > for each month, I see one graph with the curve in motion over my data set
> > (time).
> >
>

Jon,
After playing with the code it works beautifully!! Excellent suggestion and
I thank you very much. Now I just need a good book on programming macros
(I'm an old Fortran 77 guy). Thanks again.
Randy

"Jon Peltier" wrote:

> Randy -
>
> You set this up with defined names, such that the Y values is defined
> based on an integer value in another cell. Something like
>
> Name: YValues
> RefersTo:
> =Offset(Sheet1!$A$2:$A$12,0,Sheet1!$E$1)
>
> As the value in E1 increases, the range defined by the Offset is further
> and further to the right. Your chart is based on these YValues.
>
> Then you have a macro that moves forwards or backwards through the data
> by changing the value in E1 by a certain amount after a given delay.
> Shorter delays mean the curve changes more quickly.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> RandyBarrett wrote:
>
> > I would like to be able to view my data set such that I can view the curve
> > change over time instead of viewing for example 10 curves on one graph. I
> > would like to be able to select the speed at which the curve is refreshed
> > (new data set), and the direction (so I can run the curve forward and
> > backward in time).
> >
> > In essence, I have monthly data that represents one curve. I would like to
> > visually watch the curve change month by month instead of plotting one curve
> > for each month, I see one graph with the curve in motion over my data set
> > (time).
>

Randy -

Here are the books I use. Spend a half hour in a book store, and see
which one(s) you like:

http://peltiertech.com/Excel/xlbooks.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

RandyBarrett wrote:

> Jon,
> After playing with the code it works beautifully!! Excellent suggestion and
> I thank you very much. Now I just need a good book on programming macros
> (I'm an old Fortran 77 guy). Thanks again.
> Randy
>
> "Jon Peltier" wrote:
>
>
>>Randy -
>>
>>You set this up with defined names, such that the Y values is defined
>>based on an integer value in another cell. Something like
>>
>>Name: YValues
>>RefersTo:
>>=Offset(Sheet1!$A$2:$A$12,0,Sheet1!$E$1)
>>
>>As the value in E1 increases, the range defined by the Offset is further
>>and further to the right. Your chart is based on these YValues.
>>
>>Then you have a macro that moves forwards or backwards through the data
>>by changing the value in E1 by a certain amount after a given delay.
>>Shorter delays mean the curve changes more quickly.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>RandyBarrett wrote:
>>
>>
>>>I would like to be able to view my data set such that I can view the curve
>>>change over time instead of viewing for example 10 curves on one graph. I
>>>would like to be able to select the speed at which the curve is refreshed
>>>(new data set), and the direction (so I can run the curve forward and
>>>backward in time).
>>>
>>>In essence, I have monthly data that represents one curve. I would like to
>>>visually watch the curve change month by month instead of plotting one curve
>>>for each month, I see one graph with the curve in motion over my data set
>>>(time).
>>