Tricky chart axes synchronising question

[Neil]

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

 

[Tom Ogilvy]

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.

 

[Jon Peltier]

This tells how to synch axes so their zeros line up:

http://peltiertech.com/Excel/Charts/AlignXon2Ys.html

- Jon