One series chart with two y axis values

[mbk0523]

Hi,

I have a single data series (one set of x values) that I want to plot with two sets of y values (two axes).

I've tried creating a second series with the same x values and new y values, then setting the series to a second y axis, but even with scales that have the same number of increments, the dots/bubbles don't line up.

Help! Thanks!

Data is as follows:
Preferably pack size would be on the y axis, but I am fine shifting it so pack size is on the x axis and Price per unit and price per kilogram are on each of the y axes.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]2yrs ago
[/TD]
[TD]1yr ago
[/TD]
[TD]current year
[/TD]
[/TR]
[TR]
[TD]Pack Size (g)
[/TD]
[TD] 45.08
[/TD]
[TD] 46.66
[/TD]
[TD] 43.62
[/TD]
[/TR]
[TR]
[TD]Price per Unit
[/TD]
[TD] 1.95
[/TD]
[TD] 2.19
[/TD]
[TD] 2.30
[/TD]
[/TR]
[TR]
[TD]Price per Kg
[/TD]
[TD] 43.18
[/TD]
[TD] 46.94
[/TD]
[TD] 52.72
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

First plot the following 2 series:

Price per Unit
Name: =Sheet1!$A$3
X Values: =Sheet1!$B$2:$D$2
Y Values: =Sheet1!$B$3:$D$3

Price per Kg
Name: =Sheet1!$A$4
X Values: =Sheet1!$B$2:$D$2
Y Values: =Sheet1!$B$4:$D$4

Then right click one of the Series and move it to the Secondary Y axis.

 

[mbk0523]

Hi Andrew,

Thanks for your reply. While I certainly appreciate the guidance, I know how to make secondary y axes, and I stated in my original post that this method did not work.

The scales do not line up, even if I set them to have an equal number of increments (for example, left scale 0-3.5 by increments of 0.5, and right scale 0-70 by increments of 10.) Unfortunately I cannot paste in an image to show you.

Please let me know if you know of a workaround.

​Thanks

First plot the following 2 series:

Price per Unit
Name: =Sheet1!$A$3
X Values: =Sheet1!$B$2:$D$2
Y Values: =Sheet1!$B$3:$D$3

Price per Kg
Name: =Sheet1!$A$4
X Values: =Sheet1!$B$2:$D$2
Y Values: =Sheet1!$B$4:$D$4

Then right click one of the Series and move it to the Secondary Y axis.

 

[Andrew Poulsom]

For me (Excel 2003) the scales line up perfectly if they are set at 0 to 3.5 with a major unit of 0.5 and 0 to 70 with a major unit of 10. You could also use 1.5 to 3 and 30 to 60 with those major units.

 

[mbk0523]

Interesting...
Then is there a way for me to do this on my computer if I only have Excel 2007? Or is there a way for you to send me the file (without me posting my email address for the world to see)?
Thanks

For me (Excel 2003) the scales line up perfectly if they are set at 0 to 3.5 with a major unit of 0.5 and 0 to 70 with a major unit of 10. You could also use 1.5 to 3 and 30 to 60 with those major units.

 

[Andrew Poulsom]

You can send me a Private Message with your EMail address.