Can you have 3 variables on a graph chart in Excel, if so how do you do it?

Answer 1

I believe you are wishing for the ability to display a Scatter diagram or a trajectory in 3-space. That is, I believe you have a series of x-coordinates, y-coordinates, and z-coordinates, and you want to plot a dot at that three-dimensional location.

Heres an easy approach in Excel; you could be much more elaborate.

We will adopt a rigid choice of projection of 3-space onto the two-dimensional screen.

1. x-values will be represented by horizontal distances from the origin (right for positive and left for negative)
2. y-values will be represented by vertical distances from the origin (up for positive and down for negative)
3. z-values will be represented by diagonal distances from the origin (down and left for positive, up and right for negatives). The scale factors for up/down and left/right will be adjustable parameters, stored in a Constants worksheet, and given names Cupd and Cler. For the moment, set Cler = -.5, and set Cupd = -.3.

From now, it is easy to do: you have three columns of data in A (the x-values), B (the y-values), C (the z-values), let’s say A2:A51, B2:B51, and C2:C51. Now we’ll make a formula to transform the three data coordinates into TWo screen coordinates.

So, in D2, enter “=a2+Cler*C2”.

In E2, enter “=b2+Cupd*C2”

select cells D2 and E2, and copy them to D2:D51 and E2:E51 respectively.

now Insert Chart, Scatter, no lines between.

This will plot your 50 3-space points on the two-dimensional chart.

if you would like to plot a path from one point to the next, the use a Scatter chart WITH lines between.

if you would like to adjust the projection, simply change the values of Cupd and Cler in the Constants worksheet, and the chart will automatically be adjusted for the new scale factors.

if you have any questions, please ask.