Chart SERIES OFFSET

Discussion in 'Excel Charting' started by djforgone@gmail.com, Jan 23, 2013.

  1. Guest

    I've been reading up on Dynamic Charts and have come across a slight challenge that I am trying to figure out.

    I have 12 charts running off the same data table (using different rows) as I am charting FTE per division for the current financial year.

    We're currently up to pay period 15 out of a possible 26, thus I have 11 pay periods that are 0.

    What I am hoping to achieve is whether there is another way rather than using named ranges and OFFSET for it to chart what I want it to automatically.

    One idea that i was trying to achieve was using OFFSET within the SERIES formula based on the number of periods I want to chart.

    For example: CELL A1 = PERIOD 14 (named PERIOD)

    Current formula for Series 1: =SERIES('FTE Count'!$D$31,,'FTE Count'!$E$31:$AD$31,2)

    The idea I had for Series 1: =SERIES('FTE Count'!$D$31,,'FTE Count'!$E$31:OFFSET($E$31,,PERIOD),2)

    the idea is that if i have the data for period 15, I tell the charts to chart period 1 to 15. Rather than charting all 26 periods and charting 0
     
    , Jan 23, 2013
    #1
    1. Advertisements

  2. Steven North Guest

    On Wednesday, 23 January 2013 09:28:44 UTC+8, Steven North wrote:
    > I've been reading up on Dynamic Charts and have come across a slight challenge that I am trying to figure out.
    >
    >
    >
    > I have 12 charts running off the same data table (using different rows) as I am charting FTE per division for the current financial year.
    >
    >
    >
    > We're currently up to pay period 15 out of a possible 26, thus I have 11 pay periods that are 0.
    >
    >
    >
    > What I am hoping to achieve is whether there is another way rather than using named ranges and OFFSET for it to chart what I want it to automatically.
    >
    >
    >
    > One idea that i was trying to achieve was using OFFSET within the SERIES formula based on the number of periods I want to chart.
    >
    >
    >
    > For example: CELL A1 = PERIOD 14 (named PERIOD)
    >
    >
    >
    > Current formula for Series 1: =SERIES('FTE Count'!$D$31,,'FTE Count'!$E$31:$AD$31,2)
    >
    >
    >
    > The idea I had for Series 1: =SERIES('FTE Count'!$D$31,,'FTE Count'!$E$31:OFFSET($E$31,,PERIOD),2)
    >
    >
    >
    > the idea is that if i have the data for period 15, I tell the charts to chart period 1 to 15. Rather than charting all 26 periods and charting 0


    I've also tried this...

    =SERIES('FTE Count'!$D$31,,OFFSET($E$31,,'FTE Count'!$A$1),2)

    Excel advises that the formula is not valid.
     
    Steven North, Jan 23, 2013
    #2
    1. Advertisements

  3. Steven North Guest

    Problem resolved... had to create a whole series of NAMED ranges and use formulas such as ....

    =OFFSET('FTE Count'!$E$6,,0,1,COUNTA('FTE Count'!$E$6:$AD$6))



    On Wednesday, 23 January 2013 09:32:27 UTC+8, Steven North wrote:
    > On Wednesday, 23 January 2013 09:28:44 UTC+8, Steven North wrote:
    >
    > > I've been reading up on Dynamic Charts and have come across a slight challenge that I am trying to figure out.

    >
    > >

    >
    > >

    >
    > >

    >
    > > I have 12 charts running off the same data table (using different rows) as I am charting FTE per division for the current financial year.

    >
    > >

    >
    > >

    >
    > >

    >
    > > We're currently up to pay period 15 out of a possible 26, thus I have 11 pay periods that are 0.

    >
    > >

    >
    > >

    >
    > >

    >
    > > What I am hoping to achieve is whether there is another way rather than using named ranges and OFFSET for it to chart what I want it to automatically.

    >
    > >

    >
    > >

    >
    > >

    >
    > > One idea that i was trying to achieve was using OFFSET within the SERIES formula based on the number of periods I want to chart.

    >
    > >

    >
    > >

    >
    > >

    >
    > > For example: CELL A1 = PERIOD 14 (named PERIOD)

    >
    > >

    >
    > >

    >
    > >

    >
    > > Current formula for Series 1: =SERIES('FTE Count'!$D$31,,'FTE Count'!$E$31:$AD$31,2)

    >
    > >

    >
    > >

    >
    > >

    >
    > > The idea I had for Series 1: =SERIES('FTE Count'!$D$31,,'FTE Count'!$E$31:OFFSET($E$31,,PERIOD),2)

    >
    > >

    >
    > >

    >
    > >

    >
    > > the idea is that if i have the data for period 15, I tell the charts to chart period 1 to 15. Rather than charting all 26 periods and charting 0

    >
    >
    >
    > I've also tried this...
    >
    >
    >
    > =SERIES('FTE Count'!$D$31,,OFFSET($E$31,,'FTE Count'!$A$1),2)
    >
    >
    >
    > Excel advises that the formula is not valid.
     
    Steven North, Jan 23, 2013
    #3
  4. Spencer101 Guest

    'Steven North[_2_ Wrote:
    > ;1608816']On Wednesday, 23 January 2013 09:28:44 UTC+8, Steven North
    > wrote:-
    > > I've been reading up on Dynamic Charts and have come across a sligh

    > challenge that I am trying to figure out.
    > >
    > >
    > >
    > > I have 12 charts running off the same data table (using differen

    > rows) as I am charting FTE per division for the current financial year.
    > >
    > >
    > >
    > > We're currently up to pay period 15 out of a possible 26, thus I hav

    > 11 pay periods that are 0.
    > >
    > >
    > >
    > > What I am hoping to achieve is whether there is another way rathe

    > than using named ranges and OFFSET for it to chart what I want it t
    > automatically.
    > >
    > >
    > >
    > > One idea that i was trying to achieve was using OFFSET within th

    > SERIES formula based on the number of periods I want to chart.
    > >
    > >
    > >
    > > For example: CELL A1 = PERIOD 14 (named PERIOD)
    > >
    > >
    > >
    > > Current formula for Series 1: =SERIES('FTE Count'!$D$31,,'FT

    > Count'!$E$31:$AD$31,2)
    > >
    > >
    > >
    > > The idea I had for Series 1: =SERIES('FTE Count'!$D$31,,'FT

    > Count'!$E$31:OFFSET($E$31,,PERIOD),2)
    > >
    > >
    > >
    > > the idea is that if i have the data for period 15, I tell the chart

    > to chart period 1 to 15. Rather than charting all 26 periods an
    > charting 0-
    >
    > I've also tried this...
    >
    > =SERIES('FTE Count'!$D$31,,OFFSET($E$31,,'FTE Count'!$A$1),2)
    >
    > Excel advises that the formula is not valid.


    Have a look at the link below for details on dynamic charts.

    http://tinyurl.com/ayzkaks

    Personally I'd use INDEX to create the dynamic named ranges rather tha
    OFFSET as it's not volatile in the same way OFFSET is and therefore wil
    cause less slowdown of your workbook.

    Details on using INDEX for dynamic ranges can be found about half wa
    down the page that this link goes to.

    http://www.excelhero.com/blog/2011/03/the-imposing-index.html

    Let me know if you need more help.

    S


    --
    Spencer101
     
    Spencer101, Jan 23, 2013
    #4
  5. Spencer101 Guest

    'Steven North[_2_ Wrote:
    > ;1608819']Problem resolved... had to create a whole series of NAME
    > ranges and use formulas such as ....
    >
    > =OFFSET('FTE Count'!$E$6,,0,1,COUNTA('FTE Count'!$E$6:$AD$6))
    >
    >


    As mentioned in my previous post in this thread, I would recommend usin
    INDEX rather than OFFSET due to it's nonvolatile nature.

    OFFSET recalculates every time any cell is changed, regardless o
    whether or not if affects the named ranges. INDEX does not do that, bu
    will still produce the same dynamic effect. If you have many of thes
    named ranges OFFSET will very quickly slow you down.

    S


    --
    Spencer101
     
    Spencer101, Jan 23, 2013
    #5
  6. Steven North Guest

    Thanks Spencer101,

    Looking into INDEX function now.

    On Wednesday, 23 January 2013 17:11:46 UTC+8, Spencer101 wrote:
    > 'Steven North[_2_ Wrote:
    >
    > > ;1608819']Problem resolved... had to create a whole series of NAMED

    >
    > > ranges and use formulas such as ....

    >
    > >

    >
    > > =OFFSET('FTE Count'!$E$6,,0,1,COUNTA('FTE Count'!$E$6:$AD$6))

    >
    > >

    >
    > >

    >
    >
    >
    > As mentioned in my previous post in this thread, I would recommend using
    >
    > INDEX rather than OFFSET due to it's nonvolatile nature.
    >
    >
    >
    > OFFSET recalculates every time any cell is changed, regardless of
    >
    > whether or not if affects the named ranges. INDEX does not do that, but
    >
    > will still produce the same dynamic effect. If you have many of these
    >
    > named ranges OFFSET will very quickly slow you down.
    >
    >
    >
    > S.
    >
    >
    >
    >
    >
    >
    >
    >
    >
    > --
    >
    > Spencer101
     
    Steven North, Jan 24, 2013
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. jcknortheast
    Replies:
    1
    Views:
    270
    Stephen Bullen
    Dec 5, 2003
  2. Brian
    Replies:
    1
    Views:
    179
    Jon Peltier
    Jan 30, 2004
  3. nlee144(NoSpamTakeSquareRootOfNumber)

    Multiple Series of Series in Stacked Column chart?

    nlee144(NoSpamTakeSquareRootOfNumber), Jun 4, 2004, in forum: Excel Charting
    Replies:
    2
    Views:
    257
    Jon Peltier
    Jun 5, 2004
  4. Bill
    Replies:
    1
    Views:
    171
    medicenpringles
    Nov 14, 2004
  5. rich zielinski via OfficeKB.com

    series graph -- one series being added to another series

    rich zielinski via OfficeKB.com, Mar 28, 2005, in forum: Excel Charting
    Replies:
    3
    Views:
    261
    rich zielinski via OfficeKB.com
    Mar 30, 2005
  6. Yaniv
    Replies:
    4
    Views:
    190
    Jon Peltier
    Jun 16, 2005
  7. hjc
    Replies:
    7
    Views:
    249
    Jon Peltier
    Sep 20, 2005
  8. Beertje

    Using offset in series values of a chart

    Beertje, Oct 10, 2007, in forum: Excel Charting
    Replies:
    1
    Views:
    78
    Jon Peltier
    Oct 10, 2007
Loading...