Chart SERIES OFFSET


D

djforgone

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
 
Ad

Advertisements

S

Steven North

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.
 
S

Steven North

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))
 
S

Spencer101

'Steven North[_2_ said:
;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
 
S

Spencer101

'Steven North[_2_ said:
;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
 
Ad

Advertisements

S

Steven North

Thanks Spencer101,

Looking into INDEX function now.

'Steven North[_2_ said:
;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.
 
Ad

Advertisements


Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using offset in series values of a chart 1
Can a series be offset to the right? 0
Chart series 0
Chart series 1
Offsetting Chart data 5
Chart Series 2
Charts, HTML, offset 8
Chart background offset 0

Top