Aligning the horizon on 2 y axis

aselldurn

New Member
Joined
Oct 7, 2004
Messages
12
I have a line chart with 2 y axis on it. the data for both the lines has both negative and positive values. The scales on both axis automatically update, but because of this, 0 on both axis aren't aligned. because the data will vary regularly, I can't manually set the values of the axis.

Is there a way to align the 0 on both y axis, without writing a macro to do it?

:rolleyes:

:coffee:
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,020
Create two additional series. One with the max and min for data on the Y-1 axis and another for max and min of data on Y-2 axis. Add these to the chart, assigning max-min Y-2 to the Y-1 axis and vice versa for the other. Then set both series to "invisible" - line & fill to "none". If your chart has a legend, you can click on the legend label for the two faux series and just hit your delete key to remove them from the legend.
book1
ABCDE
120042005Max/Minof2004Max/Minof2005
2North5121534
3South1022-512
4East1534
5West-518
Sheet1


HTH
 

aselldurn

New Member
Joined
Oct 7, 2004
Messages
12
Thanks for this but it doesn't work in my case. The trouble is that the primary axis contains some big numbers, the secondary one is percentages that are generally no more than 10%. So, using the method you suggested basically sets the secondary axis to the same scale as the primary one - defeating the object of having two in the first place!

Thanks for your help - I don't think there's going to be a quick fix for this one.....
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,020
aselldurn said:
...So, using the method you suggested basically sets the secondary axis to the same scale as the primary one - defeating the object of having two in the first place!...
Good point... :confused: I played with this a bit and did come up with a formula that works -- somewhat.

Essentially it's the same concept as before, add a 2nd series to the y2 axis and hide it (leave y1 alone). This is based on the assumption that a portion of y1 is above zero and a portion is below. What the formula does is compare the portion of the y1 axis that is negative against the max-min of y2 and then either raise the max of Y2 or lower the min of Y2 in order to create the same ratio negative portion of y2 versus max-min of Y2 as found on Y1. Sometimes it's right on the money and sometimes it's close. What cannot be known for a certainty without using VBA is what Excel will assign for the major units on Y1 or Y2. So there's some "rounding" on both axis which sometimes misaligns them a bit.

All in all, an interesting problem. Hope this helps...
test y axis synch.xls
ABCD
12004%ofTotHiddenSeries
2North17511.458333
3South10-0.250
4East-50
5West-30-0.25
Sheet1


The formula in D2 is a bit of a humdinger...
=IF(((0-MIN(B2:B5))/(MAX(B2:B5)-MIN(B2:B5)))>((0-MIN(C2:C5))/(MAX(C2:C5)-MIN(C2:C5))),(MAX(C2:C5)*((0-MIN(B2:B5))/(MAX(B2:B5)-MIN(B2:B5))/((0-MIN(B2:B5))/(MAX(B2:B5)-MIN(B2:B5))-1))),((MIN(C2:C5)*(((0-MIN(B2:B5))/(MAX(B2:B5)-MIN(B2:B5)))-1))/((0-MIN(B2:B5))/(MAX(B2:B5)-MIN(B2:B5)))))
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,020
I named the ranges and then applied the names to the formula so hopefully it's a little easier to read...

=IF(((0-MIN(Y1Series))/(MAX(Y1Series)-MIN(Y1Series)))>((0-MIN(Y2Series))/(MAX(Y2Series)-MIN(Y2Series))),(MAX(Y2Series)*((0-MIN(Y1Series))/(MAX(Y1Series)-MIN(Y1Series))/((0-MIN(Y1Series))/(MAX(Y1Series)-MIN(Y1Series))-1))),((MIN(Y2Series)*(((0-MIN(Y1Series))/(MAX(Y1Series)-MIN(Y1Series)))-1))/((0-MIN(Y1Series))/(MAX(Y1Series)-MIN(Y1Series)))))
 

Forum statistics

Threads
1,147,622
Messages
5,742,197
Members
423,711
Latest member
luisfreitas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top