120 day moving graph window

[G Ray]

Greetings Everyone,

I have a 600 row long data set. I have several graphs set up to look
at this data.

I have put scroll bars on a couple of them. This allows me to reduce
the number of points being looked at.

What I would like to do is create a graph that looks at 120 data
points at a time. I would like the scroll bar to move the 120 point
refereneces from the top of the data set to the bottom of the set.

The idea is to be able to view any 120 consecutive data points within
my data set.

Can this be done with worksheet formulas?

Thanks
Glen

 

[G Ray]

I've been able to generate the 120 cell range, references in two cells
and the concatenate them with ":" between them in a single cell. Iv'e
been able to associate this to a scroll bar.
So now I have a cell that contains "B483:B602". As I move the scroll
bar to the other extreme the cell changes to "B3:B122". I just cant
figure out how to get these changing cell references into the graphs
source data. Is it possible?

 

[Xt]

I've been able to generate the 120 cell range, references in two cells
and the concatenate them with ":" between them in a single cell.  Iv'e
been able to associate this to a scroll bar.
So now I have a cell that contains  "B483:B602".  As I move the scroll
bar to the other extreme the cell changes to "B3:B122".  I just cant
figure out how to get these changing cell references into the graphs
source data.  Is it possible?









- Show quoted text -

It sounds like you need a "dynamic range" for your graph. You can
Google it but basically you draw the graph with any range you like.
Say the data is in the A column with a header. Then use your slider
to change a cell, say D1 to the first cell of the data you actually
want. Define a range in the name manager, say n using the offset
function using absolute references which gives the range you want.
Something like define n to be =OFFSET($A$1,$D$1,0,120,1)
Now click on a point on the graph, look at the reference to the data
in the formula bar and replace the data there with the range n. Some
other things may change automatically. Now when you slide, the range
changes and the graph too.

This is far a line graph. Things are only slightly more complicated
if you want the X axis to change as well with an xy scatter.

This may take a bit of experimenting but it will work.

xt

 

[Bob Flanagan]

It sounds like you need a "dynamic range" for your graph.  You can
Google it but basically you draw the graph with any range you like.
Say the data is in the A column with a header.  Then use your slider
to change a cell, say D1 to the first cell of the data you actually
want.  Define a range in the name manager, say n using the offset
function using absolute references which gives the range you want.
Something like define n to be  =OFFSET($A$1,$D$1,0,120,1)
Now click on a point on the graph, look at the reference to the data
in the formula bar and replace the data there with the range n.  Some
other things may change automatically.  Now when you slide, the range
changes and the graph too.

This is far a line graph.  Things are only slightly more complicated
if you want the X axis to change as well with an xy scatter.

This may take a bit of experimenting but it will work.

xt- Hide quoted text -

- Show quoted text -

You could set up 120 cells to be formulas to 600 cells by using
Offset(cell ref, R,C). In one cell you would type the offset amount,
and it would start returning cells that number away from the start of
the data. For example, if your data is in a column and you put in
Offset(cell ref, 0,0), then it will return the 120 cells at the top.
If you use Offset(cell ref, 0,100), then the rows starting at 100 down
will be returned.

Robert Flanagan
Add-ins.com LLC
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, fax 302-234-9859
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel