A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Matching the min & max values for Primary and Secondary axes



 
 
Thread Tools Display Modes
  #1  
Old March 8th, 2006, 11:26 AM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Matching the min & max values for Primary and Secondary axes

Hi All,

I am constructing a tricky scrolling chart that makes use of dynamic named
ranges to allow me to chart a defined number of months of data (eg. 6 months
of sales figures) from a total of 30 available months.

This is all easy to understand and setup with offset formulae and custom
control scrollers etc.

What I would like to do is be able to plot two sets of data on the one chart
to compare them, for example sales $ and quantity for a single product.

Given the difference in type of data, these by necessity need to be plotted
on separate axes - no problem there.

If however I wish to plot the sales $ and profit $ for a single product, it
makes sense to 'synchronise' the min and max scale values for the two axes
so that the profit $ line displays as the appropriate % of the sales $
line...

How can I do this ??
I'd like to be able to switch between 'synchronised values' and non linked
values automatically, ie. if $ based information is present on both axes -
synchronise the scales otherwise leave them independent of each other.

I'd also like to be able to control the min and max values such that
regardless of which 6 month period I am scrolling through, the chart will
not 'jump' around to automatically optimise the axis scale.
I therefore would like to set the min chart value near to the lowest value
present in the 30 months of data and the max value to the highest value in
the 30 months of data.
How best can I do this ??

Now I am probably pushing my luck here, but is it possible to dynamically
vary the chart type depending on no. of months of data plotted ??
For example, if the user elects to plot less than 6 months of data, I'd like
it to display as a bar graph otherwise a line graph...


Thanks for any and all assistance.

Regards,

Neil


  #2  
Old March 8th, 2006, 04:05 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Matching the min & max values for Primary and Secondary axes

How good are your programming skills? ;-)

Since XL doesn't allow one to link chart scale parameters to cells (or named
formulas) you will have to resort to VBA code.

Once you decide to do that almost everything on your wish list becomes
feasible. {grin}

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , says...
Hi All,

I am constructing a tricky scrolling chart that makes use of dynamic named
ranges to allow me to chart a defined number of months of data (eg. 6 months
of sales figures) from a total of 30 available months.

This is all easy to understand and setup with offset formulae and custom
control scrollers etc.

What I would like to do is be able to plot two sets of data on the one chart
to compare them, for example sales $ and quantity for a single product.

Given the difference in type of data, these by necessity need to be plotted
on separate axes - no problem there.

If however I wish to plot the sales $ and profit $ for a single product, it
makes sense to 'synchronise' the min and max scale values for the two axes
so that the profit $ line displays as the appropriate % of the sales $
line...

How can I do this ??
I'd like to be able to switch between 'synchronised values' and non linked
values automatically, ie. if $ based information is present on both axes -
synchronise the scales otherwise leave them independent of each other.

I'd also like to be able to control the min and max values such that
regardless of which 6 month period I am scrolling through, the chart will
not 'jump' around to automatically optimise the axis scale.
I therefore would like to set the min chart value near to the lowest value
present in the 30 months of data and the max value to the highest value in
the 30 months of data.
How best can I do this ??

Now I am probably pushing my luck here, but is it possible to dynamically
vary the chart type depending on no. of months of data plotted ??
For example, if the user elects to plot less than 6 months of data, I'd like
it to display as a bar graph otherwise a line graph...


Thanks for any and all assistance.

Regards,

Neil



  #3  
Old March 8th, 2006, 06:56 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Matching the min & max values for Primary and Secondary axes

Tushar has a utility that scales a chart according to values you set in the
worksheet:

http://tushar-mehta.com/excel/softwa...art/index.html

On my site I have some VBA code that performs the same task, only you have
to set it up yourself, instead of letting Tushar's program do it for you:

http://peltiertech.com/Excel/Charts/...nkToSheet.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
_______

"Tushar Mehta" wrote in message
om...
How good are your programming skills? ;-)

Since XL doesn't allow one to link chart scale parameters to cells (or
named
formulas) you will have to resort to VBA code.

Once you decide to do that almost everything on your wish list becomes
feasible. {grin}

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , says...
Hi All,

I am constructing a tricky scrolling chart that makes use of dynamic
named
ranges to allow me to chart a defined number of months of data (eg. 6
months
of sales figures) from a total of 30 available months.

This is all easy to understand and setup with offset formulae and custom
control scrollers etc.

What I would like to do is be able to plot two sets of data on the one
chart
to compare them, for example sales $ and quantity for a single product.

Given the difference in type of data, these by necessity need to be
plotted
on separate axes - no problem there.

If however I wish to plot the sales $ and profit $ for a single product,
it
makes sense to 'synchronise' the min and max scale values for the two
axes
so that the profit $ line displays as the appropriate % of the sales $
line...

How can I do this ??
I'd like to be able to switch between 'synchronised values' and non
linked
values automatically, ie. if $ based information is present on both
axes -
synchronise the scales otherwise leave them independent of each other.

I'd also like to be able to control the min and max values such that
regardless of which 6 month period I am scrolling through, the chart will
not 'jump' around to automatically optimise the axis scale.
I therefore would like to set the min chart value near to the lowest
value
present in the 30 months of data and the max value to the highest value
in
the 30 months of data.
How best can I do this ??

Now I am probably pushing my luck here, but is it possible to dynamically
vary the chart type depending on no. of months of data plotted ??
For example, if the user elects to plot less than 6 months of data, I'd
like
it to display as a bar graph otherwise a line graph...


Thanks for any and all assistance.

Regards,

Neil





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Lining up scales on primary and secondary y axes MarkN Charts and Charting 2 January 9th, 2006 01:26 AM
Changing display order of primary & secondary axes in Excel charts APW in Hou,TX Charts and Charting 1 October 15th, 2004 09:43 AM
Secondary X- and Y- Axes Allison Faris Charts and Charting 1 June 12th, 2004 12:56 PM
Getting primary and secondary axes to line up Varsha Charts and Charting 1 June 5th, 2004 03:33 AM
Multiple Stacked Columns (need secondary y-axis values too) Ross M Charts and Charting 1 January 20th, 2004 05:55 PM


All times are GMT +1. The time now is 08:59 PM.


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