+ Reply to Thread
Results 1 to 4 of 4

Getting charts to automatically pick data from a series

  1. #1
    Salmonh20
    Guest

    Getting charts to automatically pick data from a series

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

    Does excel also do interactive charts?



  2. #2
    Kelly O'Day
    Guest

    Re: Getting charts to automatically pick data from a series

    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.


    http://www.stfx.ca/people/bliengme/E...ps/Dynamic.htm
    http://processtrends.com/pg_chart_ti...rt_Range_Names
    http://peltiertech.com/Excel/Charts/Dynamics.html


    The 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

    [email protected]




    "Salmonh20" <[email protected]> wrote in message
    news:[email protected]...
    > Can I get a chart to always use say the last 9 values of a series?
    >
    > Does excel also do interactive charts?
    >
    >




  3. #3
    Jon Peltier
    Guest

    Re: Getting charts to automatically pick data from a series

    This one shows the last 12 values, which is easily adjusted to 9:

    http://peltiertech.com/Excel/Charts/DynamicLast12.html

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

    "Salmonh20" <[email protected]> wrote in message
    news:[email protected]...
    > Can I get a chart to always use say the last 9 values of a series?
    >
    > Does excel also do interactive charts?
    >
    >




  4. #4
    Salmonh20
    Guest

    Re: Getting charts to automatically pick data from a series

    Kelly,

    Thanks that was really helful.
    Re: 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

    "Kelly O'Day" wrote:

    > 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.
    >
    >
    > http://www.stfx.ca/people/bliengme/E...ps/Dynamic.htm
    > http://processtrends.com/pg_chart_ti...rt_Range_Names
    > http://peltiertech.com/Excel/Charts/Dynamics.html
    >
    >
    > The 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
    >
    > [email protected]
    >
    >
    >
    >
    > "Salmonh20" <[email protected]> wrote in message
    > news:[email protected]...
    > > Can I get a chart to always use say the last 9 values of a series?
    > >
    > > Does excel also do interactive charts?
    > >
    > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1