How to choose a scale for the SECOND Y-Axis to avoid visual distortion

[moshe.kravitz]

To explain the issue by way of example: revenues are declining over
time more quickly than # of customers and that should be apparent by
looking at a 2-line graph which plots revenues and customers over time.
The revenues (in milllions) and customers (in thousands) are plotted
against two different y-axes. The downward slope of revenues should be
steeper than the downward slope of customers. But, by default Excel
chooses a scale to fill the most space and this visually misrepresents
the facts. It looks like decline in revenues is proportional to
decline in customers.

QUESTION: How to calculate the interval, maximum and minimum for the
second y-axis to show the actual relationship between revenues and
customers.

Thank you!

Moshe

 

[Tushar Mehta]

Wouldn't it be more effective to plot two % change series? That would make
the impact obvious even with a single y-axis.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[moshe.kravitz]

Agreed. That would show the rate of decline of one relative to the
other.

We want to show the absolute amounts of revenues and customers while at
the same time depicting the true relationship between them

 

[Mike Middleton]

Moshe -

I'm not willing to take the time to describe a general step-by-step
solution, but I am willing to take a few minutes to try to show you a
solution example for your specific situation if you list the revenue and
customer values of the time series (or list only the start and end values
for each). Since we usually want to see "nice" rounded values for the min,
step, and max for each axis, a palatable solution often involves some
trial-and-error.

- Mike
http://www.mikemiddleton.com