Two X value scales for the same data

[balhal]

I have a scatter graph with x values of 1, 2, 3, 4.
I want a scale on top or maybe below the first scale that is, say, X^2,
i.e., 1 4,9 16 which matches the bottom X scale as to placement
Can I do that?

It like plotting data in inches but also showing the equivalent measure in
centimeters.

 

[Bernard Liengme]

I made an XY plot of this data
1 10
2 20
3 30
4 40

Then I made this data
1 10
4 20
9 30
16 40
I selected and Copied this; activated the chart; used Edit | Paste Special
and specified New Series with Category data in first column

Then I formatted the new data series in the chart to have a secondary y-axis
(necessary step)
Next I use a chart option to give the chart a secondary x-axis; it will be
at the top of the chart.

So we have what you want with some extraneous matter. Hide the second series
in the chart by specifying non Line and no Markers. If you do not like the
secondary y-axis, hide it making the font colour the same as the chart area
fill.

best wishes

 

[balhal]

I made an XY plot of this data
1 10
2 20
3 30
4 40

Then I made this data
1 10
4 20
9 30
16 40
I selected and Copied this; activated the chart; used Edit | Paste Special
and specified New Series with Category data in first column

Then I formatted the new data series in the chart to have a secondary y-axis
(necessary step)
Next I use a chart option to give the chart a secondary x-axis; it will be
at the top of the chart.

So we have what you want with some extraneous matter. Hide the second series
in the chart by specifying non Line and no Markers. If you do not like the
secondary y-axis, hide it making the font colour the same as the chart area
fill.

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email




Thanks much. I thought of something like that, but was hoping for something a bit simpler. I'll give it a shot -- if I can figure out where all the options are in my new Excel 2007.

 

[balhal]

I tried the solution and it's fine for data easily converted to a line
graph. I don't see how it can work with variable x data you often use for a
scatter plot. For the simple graph described, you can use a line graph and it
will be okay. But what if the original X data was something like 1, 1.2, 1.8,
4, 4.1 that you would like to see on a linear scale for the first graph.

Of course an obvious solution is to just use a set of text boxes, klugy but
workable I suppose.

Did I miss something I can do to use the suggested approach for a scatter
plot?

 

[Bernard Liengme]

It works for XY charts. That's what I use it for frequently. Did you try it?

 

[Bernard Liengme]

Why not use one of the free chart labellers to add to the secondary x-axis?
Rob Bovey's XY Chartlabeler (www.appspro.com)
or John Walkenbach's Chart Tools (www.j-walk.com).

 

[Shane Devenshire]

Hi,

Here are the steps in 2007 with your data set up as follows:

X1 X2 Data Data
1 1 3 72
2 4 39 68
3 9 70 75
4 16 42 36

If you are only plotting one series of Data, make up the other one, its just
dummy data.

1. Select the whole range and plot it.
2. Select the chart and choose Chart Tools, Design, Select Data, click the
X1 series and click Switch Row/Column. OK.
3. With the chart selected there will be a blue highlight around your data,
drag the bottom left blue square sizing handle to the right one column so you
outline the range C25.
4. Right-click one of the series on the chart and choose Format Data
Series, on the Series Options tab pick Secondary Axis and click Close.
5. With one of the series selected the purple highlight will be around the
X1 data A2:A5. Put your mouse along the border until you see a white arrow
with four black arrows and drag the highlight to B2:B5.
6. Choose Chart Tools, Axis, Secondary Horizontal Axis, Show Default Axis.

Cleanup:
1. Delete the data in D15
2. Select the Legend, then select the legend key for the "dead series" and
press delete.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[balhal]

Hi,

Here are the steps in 2007 with your data set up as follows:

X1 X2 Data Data
1 1 3 72
2 4 39 68
3 9 70 75
4 16 42 36

If you are only plotting one series of Data, make up the other one, its just
dummy data.

1. Select the whole range and plot it.
2. Select the chart and choose Chart Tools, Design, Select Data, click the
X1 series and click Switch Row/Column. OK.
3. With the chart selected there will be a blue highlight around your data,
drag the bottom left blue square sizing handle to the right one column so you
outline the range C25.
4. Right-click one of the series on the chart and choose Format Data
Series, on the Series Options tab pick Secondary Axis and click Close.
5. With one of the series selected the purple highlight will be around the
X1 data A2:A5. Put your mouse along the border until you see a white arrow
with four black arrows and drag the highlight to B2:B5.
6. Choose Chart Tools, Axis, Secondary Horizontal Axis, Show Default Axis.

Cleanup:
1. Delete the data in D15
2. Select the Legend, then select the legend key for the "dead series" and
press delete.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

I probably am not making myself clear. The above approaches, which are
really clever, don't do what I need, I dont think, If the second scale was
linear, say X2 = 5 + X1, then they would work nicely. But, we have one linear
scale, 1,2,3,4 and one quadratic, 1,4, 9, 16. Doing what you suggest you can
get the second scale where the 1 and 1 match and the 4 and 16 match, but you
can't match up the 2 with the 4 and the 3 with the 9. Or can you in an XY
graph?