Aligning the horizon on 2 y axis

[aselldurn]

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?

 

[Greg Truby]

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
	A	B	C	D	E
1		2004	2005	Max/Minof2004	Max/Minof2005
2	North	5	12	15	34
3	South	10	22	-5	12
4	East	15	34
5	West	-5	18
Sheet1

HTH

 

[aselldurn]

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]

...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... 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
	A	B	C	D
1		2004	%ofTot	HiddenSeries
2	North	175	1	1.458333
3	South	10	-0.25	0
4	East	-5	0
5	West	-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]

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)))))