Can't get the x-axis to cross at value 0 for both y-axes

by **RGVyZWs** » Wed, 19 Mar 2008 04:05:01 GMT

Hello - 

I am working on an Excel chart with both a primary and secondary y-axis and 
only one x-axis.  The primary has positive and negative values.  The 
secondary has all positive values.  I am using the secondary as a rank (by 
selecting the box to invert values so the highest rank has a value of 1 and 
appears at the top of the secondary y-axis, where rank 25 is the lowest 
possible rank and appears at the bottom of the secondary y-axis), in theory 
where the x and y axes meet.  The x-axis is different points in time.  So 
this chart shows trends over time.

The issue I am having is the primary x-axis crosses the primary y-axis at 0 
(y-axis value), which is what I want.  However, the primary x-axis crosses 
the secondary y-axis at a value that is not the lowest value on the secondary 
y-axis.  In this case, since I am using it as a rank, it is not crossing at 
the lowest rank.  I am not showing the secondary x-axis associated with rank.

I don't have this issue if all of the values on the primary y-axis are 
positive.  In other words, the primary x-axis crosses at 0 on the primary 
y-axis and at the lowest rank on the secondary axis.

My question is, is there a way to fix my problem?  Is there an option where 
I can set the value where the primary x-axis crosses a value on the secondary 
y-axis?  I know I can set the value for Primary x-axis to primary y-axis - 
and do the same for the secondary x and y axes, but I'm not aware of a way to 
cross this primary - secondary boundary.

I imagine the graph's axes looking like a backwards 4 - the primary y-axis 
goes above and below the primary x-axis.  The primary x-axis is approximately 
in the middle.  The secondary y-axis is as high as the primary y-axis but 
only goes down to the x-axis (i.e., it does not extend below the x-axis).  Is 
this possible?

Something like this:

|            |
|_______|
|
|

Thanks!

- Derek

by **Jon Peltier** » Wed, 19 Mar 2008 04:39:41 GMT

You have to fix the secondary axis so it goes as far negative as a 
proportion of its full scale as the primary axis does. You can do it 
manually or using a VBA procedure:

 http://www.**--****.com/ 

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -  http://www.**--****.com/ 
_______

by **RGVyZWs** » Wed, 19 Mar 2008 05:34:02 GMT

i Jon -

Thanks for the quick response.

I understand what you are saying in terms of having the primary and
secondary y-axes in proportion to each other, but I think the wrinkle in my
chart is I am using the secondary y-axis as a rank with the lowest rank at
the bottom where the y-axis meets the x-axis.

I completely agree with your suggestion if I were dealing with numbers going
in the same direction on both y-axes, but that is not the case here, since
you can't have a negative rank and I have rank in descending order with $
values in ascending order.

So my envisioned chart looks like this with $ as a bar graph (primary axis)
and rank as a line graph (secondary axis). I want the secondary y-axis to
cross / stop at the primary x-axis at x-axis value $0. If I have the rank
proportions to the $ amounts, it implies a $0 is not the bottom rank, which
is not always the case in my data set. I don't want the primary x-axis to
imply $0 is not ranked the lowest.

$100 | | Rank 1
| |
| |
$0 |_________|Rank 25
|
|
($100)|
Q1 Q2 Q3

- Derek

-----------------------

"Jon Peltier" wrote:

by **Jon Peltier** » Wed, 19 Mar 2008 06:20:14 GMT

didn't say plot numbers along the negative secondary Y axis, I was telling
you how to align the 0 point of each Y axis. If you don't want the negative
part of the axis to show, don't display any part of the axis (format: no
line), and add an XY series to the chart to serve as a fake axis:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

"Derek" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...

Can't get the x-axis to cross at value 0 for both y-axes

Can't get the x-axis to cross at value 0 for both y-axes

Jump to:

Sponsored Links

Next

Can't get the x-axis to cross at value 0 for both y-axes

Re: Can't get the x-axis to cross at value 0 for both y-axes

Re: Can't get the x-axis to cross at value 0 for both y-axes

Re: Can't get the x-axis to cross at value 0 for both y-axes

Similar Threads:

Who is online