3 different scales on the Y-axis

[Guest]

I have three different columns that I want on one chart to show that they all
follow the same trend. Is there a way to have all on a line chart but so
that the smallest numbers (last column) don't disappear into the x-axis line?
Basically want three lines but all in the middle of the chart showing spikes
& lows.


ex:
client A B C
c1 1000 100 1.0
c2 2400 200 2.0
c3 1500 150 1.5
c4 3000 300 3.0

 

[Jon Peltier]

You can add a tertiary axis (which I don't particularly like):

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

or you could make a panel chart, in which the chart has three separate
panels, one for each axis scale range:

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

- Jon

 

[Guest]

This is almost getting there. But my problem is that the values are such
different scales that the examples given wont help. Is there some chart or
manipulation of a chart that can plot a series in the 1000s as well as a
series in the 1(ones) and not have the second series get lost making it look
like just a flat line along the bottom over the x-axis?

Thanks!

 

[Ron Rosenfeld]

I have three different columns that I want on one chart to show that they all
follow the same trend. Is there a way to have all on a line chart but so
that the smallest numbers (last column) don't disappear into the x-axis line?
Basically want three lines but all in the middle of the chart showing spikes
& lows.


ex:
client A B C
c1 1000 100 1.0
c2 2400 200 2.0
c3 1500 150 1.5
c4 3000 300 3.0

Perhaps you could use a logarithmic scale?
--ron

 

[Jon Peltier]

See the stacked charts example? Adjust your values (and dummy axis data
labels) so that one series is scaled from 0 to 1, the next from 1 to 2, the
next from 2 to 3.

Taking your example, I would scale A from 0 to 4000, and put it into the
bottom part of the chart (0-1), so the plotted value A' is related to A by:

A' = A/4000

I would scale B from 0 to 400 and put it into the middle part of the chart
(1-2):

B' = B/400 + 1

I would scale C from 0 to 0 and put it into the top part of the chart (2-3):

C' = C/4 + 2

My real Y axis would have a major spacing of 1, minimum of 0 and maximum of
3. Major Y axis gridlines would separate the chart into its three parts.

My dummy axis series would have points from 0 to 3 at every 0.25, with the
following labels in order:

0, 1000, 2000, 3000, 0, 100, 200, 300, 0, 1, 2, 3, 4

- Jon