positive and negative x-axis with different scales
John Walkenbach
by using a secondary axis for the negative values. Set the scale for the
primary axis to be -10 through +10, and set the scale for the secondary axis
to be -500 through +500.
If that doesn't work, just kick your boss.
John Walkenbach
For Excel tips, macros, & downloads...
http://j-walk.com/ss
"Tom" <thomas...@randolph.af.mil> wrote in message
news:7e2101c1ef9c$16cffa70$b1e62ecf@tkmsftngxa04...
> all,
>
> i run a help desk and am trying to create a stacked bar
> chart for my boss (yes I can create a stacked bar, keep
> reading)
>
> Chart would have a column for each month
> On the positive axis, it would have # of trouble tickets
> on the negative axis, it would have the amount of time it
> took us to fix the problems
>
> The problem I have is one of scale. I want the positive
> axis to be 0-10 (by 1's)and the negative axis to be 0-500
> (by 25's). If they both use the same scale, then the
> number of problems is a tiny blip. I know I can create a
> secondary axis, but then the primary axis is on the left
> side and the secondary axis is on the right side. What I
> want is a secondary axis is negative.
>
> I sort of created this effect by having two charts and
> overlapping them. The problem is, the charts don't line-
> up nice and pretty. (see attachment)
>
> Please don't give me solutions of kicking my boss. I'll
> just have to refer you to the Dilbert comic strip on this
> one. This is the way he wants it to look and none of my
> guys can come up with a way to make it work
>
> thanks in advance for any help
tom
the right hand side
I want
3 |
2 | X
1 | X X
0 ------------
10 | X X
20 | X
30 |
not
3 | X |30
2 | X X |20
1 | X X |10
0 -----------------
do you know how to move the secondary axis??
>.
>
Jon Peltier
I started with this dummy data:
Count Duration
1 6 108 -4.32
2 3 225 -9
3 4 229 -9.16
4 9 227 -9.08
5 4 200 -8
6 6 164 -6.56
7 7 44 -1.76
8 6 203 -8.12
9 8 76 -3.04
10 9 112 -4.48
11 3 227 -9.08
Column A is your categories, B is the count of incidents, C is the duration
in minutes, D is transformed by dividing (in this case) by -25. Make a
stacked column chart. Keep the X axis at Y=0, but for clarity, format the
axis and for Tick Mark Labels (Patterns tab), select Low.
For the Y axis, my thought is to use a dummy series to generate a dummy axis.
Start with three columns:
X Y Label
0 10 10
0 9 9
0 8 8
...
0 1 1
0 0 [blank]
0 -1 25
0 -2 50
0 -3 75
...
0 -9 225
0 -10 250
Copy the first tow columns, and paste-special into the chart, as new series,
categories in first column. Right click on the new series you just created
(it's now another stacked column), and choose Chart Type. Select an XY
Scatter type with mrkers but no line. Right click on the chart, select Chart
Options, Axes tab, and uncheck the Secondary Y checkbox. Double click the
new X axis on the top of the chart, and on the Patterns tab, check None for
Major and Minor ticks and Tick Labels. Double click on the Y axis, pick the
scale tab and format its scale to +10 to -10, switch to the patterns tab and
pick none for major and minor ticks and markers. Now resize the plot area so
there's room for dummy labels in the margin. Double click the dummy series
along the Y axis, and change the marker to black crosses (which look like
tick marks; I use size 6 or 7).
Now the trick. Download Rob Bovey's XY Chart Labeler from
http://www.appspro.com. It's a free addin that lets you use any other range
of cells for data point labels. Install it and use the new Chart Labels
command from the Tools menu. Make sure you select the new series you added,
use the mouse to select the column with the dummy labels, and choose the
"Left" position.
Finally, select the legend, then select the legend entry for the dummy
series, and press delete. You didn't need that legend entry anyway.
- Jon
-------
Jon Peltier, Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______
In article <7ebc01c1efac$2e574850$b1e62ecf@tkmsftngxa04>, tom said...