Y-axis scale

  • Thread starter Thread starter andy
  • Start date Start date
A

andy

I have a bar chart comparing hours of labor vs. Finished
Goods shipped. The max. value for labor is 28000 and the
max. value for FG is 830000. The scale is defaulting to
100000. The problem is that with this scale you can't see
the variation in labor hours. Is there a way to split the
scale to show the differences in labor hours with smaller
scale units in the bottom half and have the units get
larger to show the variation of FG in the top half? If
anyone has another suggestion on how to present this data
clearly I would appreciate that as well.

I'm using Excel 2003.

Thanks for any suggestions,
Andy
 
Andy -

You have a couple choices.

1. Plot Labor on the primary Y axis. Plot FG on the secondary axis. To
do this, double click on the FG series, and on the Axis tab, select
Secondary. Pretty easy.

2. Harder but pretty cool: use the Broken Y Axis technique on my web
site, or scroll to the bottom of the page and try one of the others I've
cited:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
That is a pretty neat trick. I still have a problem
though. All of the data in one of my series is so much
larger than the data in the other that it doesn't work as
well as if I had an outlier. All of my markers for the
smaller qty data set are smushed on top of each other and
you can't read them. The bars do not adjust for even
spacing of the markers. I am still struggling to find an
effective way to present this data. I was wondering if
reversing the method in which the scale was split would
acheive the desired effect that I'm looking for. If I
wanted to make the smaller numbers larger instead of
reducing the series with the larger numbers I think I
might get the desired result. I have not attempted yet
and am wondering about the complexity. Is it a matter of
reversing the >/< +/- in the IF statements, or is there
more to it.

Any advice would be greatly appreciated.

Thanks,
Andy
 
Andy -

Here's another approach which would be easier to accomplish, and will
help to display two very different series, while keeping them distinct.
Think of good ranges for the small data and for the large data. Make a
chart with both, and put the larger series on the secondary axis.

Format the primary Y axis, so its minimum coincides with your optimum
minimum of the small data. Format the axis maximum so the optimum
maximum of the small data is halfway up the chart. If you decided to
plot between 4 and 10, for example, set the scale to 4-16.

Format the secondary Y axis so its maximum coincides with your optimum
maximum for the larger series. Format the axis minimum so the optimum
minimum is in the middle of the chart. If you decided to plot between
400 and 500, for example, set the scale to 300-500.

Apply a custom number format to the primary axis that only shows numbers
less than the optimum maximum. If the optimum maximum is 10, and the
number format is "0", change it to this: "[<=10]0;;;" (without the quotes.

Similarly, apply a custom number format to the secondary axis that only
shows numbers greater than the optimum minimum. If this value is 400,
use a format like this: "[>=400]0;;;" (without quotes).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Thanks Jon,

I think that does the trick. Much appreciation for all of
the help.

Thanks again,
Andy
-----Original Message-----
Andy -

Here's another approach which would be easier to accomplish, and will
help to display two very different series, while keeping them distinct.
Think of good ranges for the small data and for the large data. Make a
chart with both, and put the larger series on the secondary axis.

Format the primary Y axis, so its minimum coincides with your optimum
minimum of the small data. Format the axis maximum so the optimum
maximum of the small data is halfway up the chart. If you decided to
plot between 4 and 10, for example, set the scale to 4-16.

Format the secondary Y axis so its maximum coincides with your optimum
maximum for the larger series. Format the axis minimum so the optimum
minimum is in the middle of the chart. If you decided to plot between
400 and 500, for example, set the scale to 300-500.

Apply a custom number format to the primary axis that only shows numbers
less than the optimum maximum. If the optimum maximum is 10, and the
number format is "0", change it to this: "[<=10]0;;;" (without the quotes.

Similarly, apply a custom number format to the secondary axis that only
shows numbers greater than the optimum minimum. If this value is 400,
use a format like this: "[>=400]0;;;" (without quotes).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
That is a pretty neat trick. I still have a problem
though. All of the data in one of my series is so much
larger than the data in the other that it doesn't work as
well as if I had an outlier. All of my markers for the
smaller qty data set are smushed on top of each other and
you can't read them. The bars do not adjust for even
spacing of the markers. I am still struggling to find an
effective way to present this data. I was wondering if
reversing the method in which the scale was split would
acheive the desired effect that I'm looking for. If I
wanted to make the smaller numbers larger instead of
reducing the series with the larger numbers I think I
might get the desired result. I have not attempted yet
and am wondering about the complexity. Is it a matter of
reversing the >/< +/- in the IF statements, or is there
more to it.

Any advice would be greatly appreciated.

Thanks,
Andy



secondary axis. To


tab, select


technique on my web
of

the others I've
Click to expand...

.
Click to expand...
 
Back
Top