software

Can I get a chart to always use say the last 9 values of a series?

Does excel also do interactive charts?
Salmonh:

Yes you can have Excel chart the last 9, 30 or whatever number of values
that you would like.

You'll need to use dynamic range names to do this. Here are several links
that will get you started.www.stfx.ca/people/bliengme/E...ps/Dynamic.htm
processtrends.com/pg_chart_ti...rt_Range_Names
peltiertech.com/Excel/Charts/Dynamics.htmlThe idea is to create dynamic ranges that will update as you add data and
will set the last 9 values as your plot range.

For a simple data set of X and Y in Cols A and B, you would use 4 offset
formulas:

First we set up a range name for the last data row

last_row =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0)

Second: we set up a range name for the first data row that we want to plot,
using your 9 example:

first_row
=OFFSET(last_row,-8,0)Third: we set up the dynamic X value range name using the first and last
rows:

dyn_x =OFFSET(first_row,0,0,last_row-first_row 1)Fourth: we set up the dynamic Y value range as an offset to the dynamic_X
range

dyn_y =OFFSET(dyn_x,0,1)We are now set to create our chart. I do this by creating my chart using the
chart wizard. When I have my chart formatted and set the way I want, I edit
the chart series formulas and replace the X value range with dyn_X and the Y
value range with the dyn_Y range name.

If you want another period besides 9, edit the offset formulas.You also asked about interactive charts. There are many interactive
examples. If you give me an idea of the type of interaction you are looking
for, I can give you links to specific examples.

...Kelly
quot;Salmonh20quot; gt; wrote in message
...
gt; Can I get a chart to always use say the last 9 values of a series?
gt;
gt; Does excel also do interactive charts?
gt;
gt;
This one shows the last 12 values, which is easily adjusted to 9:

peltiertech.com/Excel/Charts/DynamicLast12.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
peltiertech.com/Excel/ExcelUserConf06.html
_______

quot;Salmonh20quot; gt; wrote in message
...
gt; Can I get a chart to always use say the last 9 values of a series?
gt;
gt; Does excel also do interactive charts?
gt;
gt;
Kelly,

Thanks that was really helful.Interactive charts, on a set dat I have 20 different lab results on a
sample, I have 500 sample per year.
I would like to send the results as an interactive chart which will allow
the user to pick from the 20 different result types, what they wish to view,
changing from one to another.

Robert

quot;Kelly O'Dayquot; wrote:

gt; Salmonh:
gt;
gt; Yes you can have Excel chart the last 9, 30 or whatever number of values
gt; that you would like.
gt;
gt; You'll need to use dynamic range names to do this. Here are several links
gt; that will get you started.
gt;
gt;
gt; www.stfx.ca/people/bliengme/E...ps/Dynamic.htm
gt; processtrends.com/pg_chart_ti...rt_Range_Names
gt; peltiertech.com/Excel/Charts/Dynamics.html
gt;
gt;
gt; The idea is to create dynamic ranges that will update as you add data and
gt; will set the last 9 values as your plot range.
gt;
gt; For a simple data set of X and Y in Cols A and B, you would use 4 offset
gt; formulas:
gt;
gt; First we set up a range name for the last data row
gt;
gt; last_row =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0)
gt;
gt; Second: we set up a range name for the first data row that we want to plot,
gt; using your 9 example:
gt;
gt; first_row
gt; =OFFSET(last_row,-8,0)
gt;
gt;
gt; Third: we set up the dynamic X value range name using the first and last
gt; rows:
gt;
gt; dyn_x =OFFSET(first_row,0,0,last_row-first_row 1)
gt;
gt;
gt; Fourth: we set up the dynamic Y value range as an offset to the dynamic_X
gt; range
gt;
gt; dyn_y =OFFSET(dyn_x,0,1)
gt;
gt;
gt; We are now set to create our chart. I do this by creating my chart using the
gt; chart wizard. When I have my chart formatted and set the way I want, I edit
gt; the chart series formulas and replace the X value range with dyn_X and the Y
gt; value range with the dyn_Y range name.
gt;
gt; If you want another period besides 9, edit the offset formulas.
gt;
gt;
gt; You also asked about interactive charts. There are many interactive
gt; examples. If you give me an idea of the type of interaction you are looking
gt; for, I can give you links to specific examples.
gt;
gt; ...Kelly
gt;
gt;
gt;
gt;
gt;
gt;
gt; quot;Salmonh20quot; gt; wrote in message
gt; ...
gt; gt; Can I get a chart to always use say the last 9 values of a series?
gt; gt;
gt; gt; Does excel also do interactive charts?
gt; gt;
gt; gt;
gt;
gt;
gt;