Cannot get the secondary Y axis cross x asis at zero

[xavier1000]

Hi guys,

As my problem last time was solved excellently here, I'm posting now a different one.

The problem is this (Excel 2007):

I want the secondary axis (the one on the right) to cross my X-axis at an Y-axis value of 0. Currently the X-axis crosses the Y-axis at an Y-axis value of 5.0E7.
Really don't know how to fix this, in the image you can see the settings of each axis?

It should look like this:

Am I overseeing something?

Thanks so much.

 

[JasC]

I always solve this by setting the max and min of the right hand Y axis manually. In this example, your left hand axis goes from 100 to -40. So the ratio of axis above to below zero is 100:40, or 5:2. If you use this ratio for the secondary axis as well then it will cross at zero. So in this case if you have a max of 1.8E8, then set the min to -2 x 1.8E8 / 5 = -7.2E7

 

[xavier1000]

Wow great man! thanks so much.

I just don't think there's a possibility to set the major or minut unit values accordinlgy to zero, instead of according to max-min? Would just have been nice to show the zero on the right as well

Even without that, you helped me so much, been struggling with this for a while now.

Even heard people saying, 'you shouldn't use excel for graphs!' LOL

 

[JasC]

You can do similar with the major and minor units.
In the above example, on the left axis you have 7 "steps" from -40 to +100, for a range of 140. So 140 / 7 = 20. Therefore 20 is the major unit.

If you divide the total range for the right hand axis by 7, then this will give you a major unit for the right hand axis, which aligns with the left hand axis.

Given the numbers involved, this doesn't always give you round Numbers. Sometimes you have to fiddle to make the numbers nice on both sides.

 

[xavier1000]

Hi there,

thank you for you valuable comments.

 

[Khehla]

I have exactly the same problem (5 years later).

Can you help? How do I e-mail the graph to you....?