Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to tell if cell contains a FORMULA or user-entered number? | Excel Worksheet Functions | |||
Lining up scales on primary and secondary y axes | Charts and Charting in Excel | |||
Secondary Axis | Charts and Charting in Excel | |||
Secondary Axis Match Primary Axis Gridlines | Charts and Charting in Excel | |||
secondary axis scale | Charts and Charting in Excel |