Custom EXCEL chart value axis BRAIN TEASER

[MikeM_work]

I need to format my VALUE AXIS with postive numbers above and below the zero
value on the axis:

8,000
6,000
4,000
2,000
0
400
800
1,200
1,600

I know it can be done as I am looking at an existing COLUMN chart in EXCEL
2002.
The chart has both primary and secondary Value axes. One series is attached
to the primary value axis and one series attached to the Secondary Value axis.
The primary Value Axis has a custom number format that hides all the
negative values. The secondary Value Axis has a custom number format that
hides all the negative values and the zero value. This axis is also
formatted so its values appear in reverse order.
So you end up with ZERO through 8,000 visible on the Primary Value axis
(above the horizontal category axis) and 400 through 1,600 visible on the
Secondary Value axis (below the horizontal category axis).

There are also primary and secondary Category Axes. The Secondary Category
axis is formatted to be invisible. When you 'flip' the category axes (by
either selecting or de-selecting 'Value axis (Y) crosses at maximum
category) both value axis ranges combine for the final result as described
above.

Whenever I try it, however, one set of value axis labesl always 'erases' the
other set of value axis labels.

Any ideas?

PS: I'd be happy to attach the existing EXCEL file to an e-mail if anyone
would like to see this chart.

Thanking you all in advance,

Mike

 

[Jon Peltier]

You need your value axes on opposite sides of the chart. Format one category
axis (usually the secondary) so its value axis crosses at the maximum
category (i.e., check the checkbox). Format the other so its values axis
does not cross at the maximum (uncheck the checkbox).

Then you need to adjust the min and max of the two value axes so that zero
corresponds to the same vertical position, and they go in the appropriate
direction. Pretty much the way I set up the horizontal axes in this chart:

http://peltiertech.com/WordPress/2008/09/04/us-employment-slump-chart-how-to/

- Jon