xy charts??

[Telegraph Sam]

Trying to figure out how to graph / chart a relationship between one dependent and two independent variables. The tutorials I have found all address the opposite. I suspect the answer will be contained in xy scatter charts. But do I use two / dual axes as I once did with line charts?? If so I can't work out how to do this, how to set out the data and where to find the dual axes option button.

Tks for assistance!

[shg]

That would be a surface chart, and Excel has very limited capabilities for them.

[Telegraph Sam]

I can visualize a simple surface (= 2 dimensional?) chart with independent variables on the x and y axes respectively and the dependent values graphed against each. As I said I've a feeling that x y charts do this, with or without dual axes, but I can't quite figure out how to do it. Trial and error?

[shg]

I have a feeling that no x-y charts do this. You need need three independent axes.

[Telegraph Sam]

If you wanted to chart (for example) the second hand values of a model of a car against the age and the mileage as displayed in various used car adverts, I can't see why Excel cannot do this (without resorting to 3 axes - I can't think in 3 dimensions!). There are only two independent variables to go on 2 independent axes. I thought this was what x y charts were designed to do.

[shg]

Maybe. Draw one by hand, scan it and post it.

[Andy Pope]

for xyz points you can try this
http://www.andypope.info/charts/3drotate.htm

[Telegraph Sam]

Tks - the contents look really interesting even if I don't believe that for my simple purposes it's necessary to go to 3 dimensions let alone rotate them! I just need to get the hang of using standard xy charts properly and I suspect that somewhere within what you've sent will be the answer. Weekend's work ahead ..

[Andy Pope]

Probably for you example of the car you need

X axis milage
Y axis age
Data label text is the car and cost

Use this free addin to link data labels to cells.
http://www.appspro.com/Utilities/ChartLabeler.htm

[Telegraph Sam]

Great minds think alike. This is what I had in mind to progress to if and when I succeed in getting the 2-dimensional basics to work using what you sent previously (incl. how to lay out the raw data so that the chart picks it up properly)

[Andy Pope]

for 5 pairs of value I would layout like this.

A2:A6 contains X values
B2:B6 contains Y values
B1 contains text to label series

Select A1:B6 and create a single series XY scatter.

[Telegraph Sam]

Tks for realtime response! I'll have to experiment with this because in my case there is no direct correlation between the values in A2:A6 and B2:B6 as would be implied (both are "independent"). May be the xy graph function can accomodate this somehow. I have got my raw data set out in 2-D matrix form but haven't yet tried to "scatter" graph it.

[Andy Pope]

You will need to alter the layout of your data.
Creating a 2 column list where each Row value is repeated for the unique Col value.

[Telegraph Sam]

Sorry you've lost me there. I have attached an imaginary simple example of what I am trying to chart. May be you could show me what you mean using this.

[Andy Pope]

This uses the index formula with a few IF tests to suppress empty matrix cells for outputting point or label.

[Telegraph Sam]

Brill, I think that's the solution though I admit it is more complex than I had first imagined. I had to refresh my understanding of index (vs vlookup and hlookup) and it will require quite some work to adapt the solution to my original data which is more comprehensive (umpteen rows) than the example. I hope your workings are not copyright! Tks.

[teylyn]

Telegrap Sam, please stop quoting whole posts. It's just clutter. Use the quick reply box or the New Reply button, not the Quote button when replying to a post.

thanks

[Andy Pope]

The simple solution, using your matrix, is to plot a line chart.

The two variations are included in the attached.

[Telegraph Sam]

Andy. Your two latest tks are no doubt neater solutions but unfortunately don't demonstrate the dependency on the combination of "age" and "mileage" which is what I had in mind when I thought of xy scatter graphs. Further thought and experimentation with what you sent, over Easter ...