Re-basing lines on graph with 2-axis

Hi there, I want to plot 2 lines against seperate y-axis on the same graph. I don't want to lose the quantitative value of the axis, but I want both lines to begin at exactly the same point on the graph. Can I effectively re-base one of the lines while keeping the quantitative values consistent with reality? I use Office 10 btw .... thanks a lot

Hi

To make this work you need both series’ starting points to be the same proportionate distance between their respective y axis minimums and maximums.

(Series1 start value - Y1 minimum)/(Y1 maximum – Y1 minimum) = (Series2 start value - y2 minimum)/(Y2 maximum – Y2 minimum)

This macro should do it automatically for you:

Sub axis_adjust()
ActiveChart.Axes(xlValue, xlSecondary).MinimumScaleIsAuto = True
ActiveChart.Axes(xlValue, xlSecondary).MaximumScaleIsAuto = True
ActiveChart.Axes(xlValue, xlPrimary).MinimumScaleIsAuto = True
ActiveChart.Axes(xlValue, xlPrimary).MaximumScaleIsAuto = True
    
s1min = ActiveChart.Axes(2, 1).MinimumScale
s1max = ActiveChart.Axes(2, 1).MaximumScale
Y1vals = ActiveChart.SeriesCollection(1).Values
s1start = Y1vals(1)

s2min = ActiveChart.Axes(2, 2).MinimumScale
s2max = ActiveChart.Axes(2, 2).MaximumScale
Y2vals = ActiveChart.SeriesCollection(2).Values
s2start = Y2vals(1)

y1ratio = (s1start - s1min) / (s1max - s1min)
y2ratio = (s2start - s2min) / (s2max - s2min)


    ActiveChart.Axes(2, 2).MinimumScale = s2min
    ActiveChart.Axes(2, 2).MaximumScale = (s2start - s2min) / y1ratio

End Sub

many thanks for that reply - my macro skills are nil but I will try to engage a colleague to help. Thanks again.

You could do it using calculations if it's easier

set minimum and maximum to autoscale for both axes
for the Y2 axis, change the axis minimum from autoscale to fixed, setting it at whatever value the autoscale chose

s1start = starting value of Series 1
s1min - minimum value of axis Y1
s1max = maximum value of axis Y1
s2start = starting value of Series 2
s2min - minimum value of axis Y2
s2max = maximum value of axis Y2

set the secondary Y maximum value to:
= (s2start - s2min) / ((s1start - s1min) / (s1max - s1min))

if that pushes your series off the scale, go through the same process but fix the Y1 minimum at whatever autoscale chooses and set the Y1 maximum to:

= (s1start - s1min) / ((s2start - s2min) / (s2max - s2min))

Hi again, the macro didn't work, but I have to say you are a bona fide genius having just used to formula directly.

Thanks a lot