Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3
Default Chart SERIES OFFSET

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

  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 14
Default Chart SERIES OFFSET

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.
  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 14
Default Chart SERIES OFFSET

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.

  #4   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by Steven North[_2_] View Post
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.
Have a look at the link below for details on dynamic charts.

http://m.techrepublic.com/blog/msoff...-in-excel/7836

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

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

http://www.excelhero.com/blog/2011/0...ing-index.html

Let me know if you need more help.

S.
  #5   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by Steven North[_2_] View Post
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.


  #6   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 14
Default Chart SERIES OFFSET

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
User Selectable Series and Number of Series for Line Chart Dave in NJ Charts and Charting in Excel 2 February 23rd 09 01:18 AM
Can a series be offset to the right? es330td Charts and Charting in Excel 0 October 22nd 08 06:24 PM
how to plot column chart with one series against multiple series. svenkateshmurthy Charts and Charting in Excel 2 October 16th 07 09:09 PM
Using offset in series values of a chart Beertje Charts and Charting in Excel 1 October 10th 07 06:50 PM
chart data series -- plot a table as a single series hjc Charts and Charting in Excel 7 September 20th 05 05:52 PM


All times are GMT +1. The time now is 05:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"