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

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

In article <(E-Mail Removed)>,
(E-Mail Removed) says...
> 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
>
>

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


Tushar Mehta wrote:
> 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
>
> In article <(E-Mail Removed)>,
> (E-Mail Removed) says...
> > 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
> >
> >

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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>