Solved: Create New Series on the Secondary Axis

[Cyberdude]

I've got a stock chart that plots normal stock stuff on the primary axis. I would like to add a new series that plots my daily percent profit/loss on the secondary axis. For the life of me I cannot figure out how to put a new series on the secondary axis. I found where to switch the existing series to the secondary axis, but how do you tell it to put a new series there? Should I switch the four existing series to the secondary axis, then add the new series to the primary axis?? I REALLY would prefer not to go through that song and dance.

[Killian]

I don't think there's any need for a song and dance - unless you're in the mood...
You set a reference to the new series when you add it, then just set it's properties accordingly, including which axis it's on, e.g.[VBA]Dim srsPandL As Series

With ActiveChart
Set srsPandL = .SeriesCollection.NewSeries
With srsPandL
.Values = "=Sheet1!R5C5:R14C5"
.AxisGroup = 2
.ChartType = xlLine
End With
End With[/VBA]

[Cyberdude]

Hi, Killian! I didn't make myself clear. I create all my charts manually, not with VBA. Perhaps that's my problem. It turns out that since I want dollars profit/loss on one axis and percent on the other, the charts that the series for each axis produces is identical (if the scale is set up correctly). In fact they overlay each other. That's what I want, except it would be nice if I could make one of the series with no color. If I make the color the same as the background, then there are little gaps showing up where the two graphs cross each other. Apparently a graph with no color isn't an option.

Let me restate what I want. I really want a chart that has a scale on one side that is dollars and the other side would be percent. I just need one series plotted. In fact if I could show both percent AND dollars on one axis, that would be perfect, but there doesn't seem to be a way to do that.

[Killian]

No VBA??? Now I'm stuck

Well, working backwards from the code...
Add your second series, then select it and right-click and choose Format data series.
On the Axis Tab, select secondary - chances are Excel will adjust the scale of the secondary axis and automatically overlay the 2 series. (If the don't overlay how you want, format the 2nd axis - Scale tab Max and Min) On the patterns tab you can choose the formatting to hide it (no line no fills etc).
You can set up the axis titles in Chart options (right-click chart area)

[Cyberdude]

OK, I think I've got it now. The only shortcoming is that I'm always adjusting the scale of these charts as they edge off the top of the screen. If I change the scale of the dollars axis, the percent axis doesn't change with it, so seeing $5000 on one axis doesn't necessarily mean 10% on the other until I rescale it too. Anyway I think I can create the secondary axis now. Thanx for the help.

[JonPeltier]

I think there's a conceptual problem here. You do not add a new series to the secondary axis. You add a new series to the chart, then once the new series is in the chart, you can format the series so it is plotted against the secondary axis.

Also, the primary and secondary axes are not linked to each other, so as you said, $5000 on one axis does not relate to 10% on the other. If you think about it, that independence of the axes is a good thing.