Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky chart axes synchronising question
Hi All,
I'd appreciate some assistance with the following: I have a chart that can display Sales $, Quantity, Margin % or Average Unit Sell Price on the Primary axis (User selectable). I have put together a rather ugly Worksheet Change Procedure that checks whats on display and adjusts the primary axis display accordingly. For example if the data displayed is "Margin %", the worksheet change code will set 0 as min, 1 as max and display as "%". I have also copied and adjusted to do this for the secondary axis which is also user selectable from the above measures..... That's working good so far. Here's my problem. When I plot sales $ data on the primary axis and margin $ data on the secondary axis, I would like them to match in terms of the min, max values so that I can see proportionally how the margin $ compares to the sales $. At the moment I adjust the axis manually - this is not good during presentations however ! Additionally, the higher valued item may be on the primary or secondary axis, so I can't simply tell it to match the primary axis setup for the secondary axis as this may chop the top off some of my data if it is the secondary axis data that contains the highest value for the chart. To manage the process, I have created a range that looks at the combination on display and returns a value to flag whether the axes need to be matched, ie. if sales $ and margin $ are displayed it flags "yes", if sales $ and margin % are on display, it flags "no". The groups collective wisdom is greatly appreciated. Thanks, Neil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky chart axes synchronising question
Think you left out the punch line (i.e. what is the question)?
I would guess that if the flag is yes, that the code should check each data source and determine which data source has the maximum value. then set the max for both axes to accomodate that value. -- Regards, Tom Ogilvy "Neil" wrote: Hi All, I'd appreciate some assistance with the following: I have a chart that can display Sales $, Quantity, Margin % or Average Unit Sell Price on the Primary axis (User selectable). I have put together a rather ugly Worksheet Change Procedure that checks whats on display and adjusts the primary axis display accordingly. For example if the data displayed is "Margin %", the worksheet change code will set 0 as min, 1 as max and display as "%". I have also copied and adjusted to do this for the secondary axis which is also user selectable from the above measures..... That's working good so far. Here's my problem. When I plot sales $ data on the primary axis and margin $ data on the secondary axis, I would like them to match in terms of the min, max values so that I can see proportionally how the margin $ compares to the sales $. At the moment I adjust the axis manually - this is not good during presentations however ! Additionally, the higher valued item may be on the primary or secondary axis, so I can't simply tell it to match the primary axis setup for the secondary axis as this may chop the top off some of my data if it is the secondary axis data that contains the highest value for the chart. To manage the process, I have created a range that looks at the combination on display and returns a value to flag whether the axes need to be matched, ie. if sales $ and margin $ are displayed it flags "yes", if sales $ and margin % are on display, it flags "no". The groups collective wisdom is greatly appreciated. Thanks, Neil |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky chart axes synchronising question
This tells how to synch axes so their zeros line up:
http://peltiertech.com/Excel/Charts/AlignXon2Ys.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Neil" wrote in message ... Hi All, I'd appreciate some assistance with the following: I have a chart that can display Sales $, Quantity, Margin % or Average Unit Sell Price on the Primary axis (User selectable). I have put together a rather ugly Worksheet Change Procedure that checks whats on display and adjusts the primary axis display accordingly. For example if the data displayed is "Margin %", the worksheet change code will set 0 as min, 1 as max and display as "%". I have also copied and adjusted to do this for the secondary axis which is also user selectable from the above measures..... That's working good so far. Here's my problem. When I plot sales $ data on the primary axis and margin $ data on the secondary axis, I would like them to match in terms of the min, max values so that I can see proportionally how the margin $ compares to the sales $. At the moment I adjust the axis manually - this is not good during presentations however ! Additionally, the higher valued item may be on the primary or secondary axis, so I can't simply tell it to match the primary axis setup for the secondary axis as this may chop the top off some of my data if it is the secondary axis data that contains the highest value for the chart. To manage the process, I have created a range that looks at the combination on display and returns a value to flag whether the axes need to be matched, ie. if sales $ and margin $ are displayed it flags "yes", if sales $ and margin % are on display, it flags "no". The groups collective wisdom is greatly appreciated. Thanks, Neil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tricky Ranking Question | Excel Discussion (Misc queries) | |||
Excel Chart question using two y axes | Excel Discussion (Misc queries) | |||
Line - Column 2 Axes chart question | Charts and Charting in Excel | |||
New guy with a tricky question | Excel Programming | |||
Tricky Question | Excel Worksheet Functions |