Adding a linear equation to my groovy scatter plot.

[Guest]

I have a scatter plot chart that plots eight different coordinate points and
it looks really awesome. I also have done some calculations to my data and
have a linear equation of y = 0.35592x + 0.1988939. I want to plot this
linear equation over the same chart as my scatter plot to show that the line
and the scatter plots are very closely related. How can I do this? I assume
you right-click on the chart and click on "Source Data" and add another
Series, but then what?

 

[David Biddulph]

Choose minimum and maximum X values for your new line & put them in one
column. Calculate corresponding Y values from your equation and put them in
the next column. These then form the X and Y series to add as your new
series to the chart.

 

[Guest]

I'm not sure I completely understand.

I want my line to run from x = 0 to x = 4000. Therefore, my corresponding y
min and max would be:

min: y = 0.35592(0) + 0.1988939 = 0.1988939
max: y = 0.35592(4000) + 0.1988939 = 1423.8788939

Now, I did as you said and put these four values into my spreadsheet. I have
the x min in cell E1, the y min in cell F1, the x max in cell E2, and the y
max in cell F2.

So, what exactly do I put under "X Values: " and "Y Values: " when I add a
new Series?

 

[David Biddulph]

Reference to your column with the 0 and 4000 cells (E1:E2) goes in the "X
values", and the reference to the column with the two Y values (F1:F2) goes
in the "Y values" series. If you just click in the relevant series box for
the new series, then go to the sheet with the data and select the relevant
cells, it will put the cell references in automatically.

 

[Guest]

I did that, and the problem is it just interprets it as two more plot points
rather than the endpoints of a continuous line graph.

 

[David Biddulph]

You need to add a new series, rather than just adding to the existing
series.
You may well get away with copying the 4 cells (E1:F2), then going to the
graph, & using Edit/ Paste Special, selecting new series, data in columns,
etc.

 

[Guest]

I already did add a new series.

I have two series: one for the plotted points, one for the line graph.

Why is it interpreting the four cells as plot points and what can I do about
it?

 

[Del Cotter]

Go to Format.. Selected Data Series.. and click the radio button in the
Patterns:Line dialogue from "None" to "Automatic" or "Custom". If you
want, you can also switch off the points by clicking the Marker radio
button from "Automatic" to "None".

Now you have a line and points, or a line and no points.

 

[Guest]

Yay! You rock! Thank you!

I guess this little trick couldn't work with any curved graphs, though
(because all we're doing is connectiong two already calculated endpoints with
a line). I mean, there isn't any real "computing" going on, is there?

BigDaddyCool

 

[David Biddulph]

If you've got a curved line, such as a quadratic rather than your linear
expression, then you'd need to include more than two points. Instead of
just using X values of 0 and 4000, you could interpolate and have values 0,
200, 400, etc. for example. Then use your expression for Y as a function of
X to let Excel calculate the Y values in the second column, and plot the
graph as you've just done. You can either let Excel join the points with
short straight lines like the one line you've just drawn between your two
points, or (if the curve is reasonably well-behaved) you can let Excel put
in a curve through the points. [You've seen the different sub-types within
the XY chart option.]

 

[Jon Peltier]

You can approximate a curve by stringing together a lot of short segments.
In other words, instead of two endpoints based on two X values and the
coefficients, use enough X values so that the curve looks smooth, and
calculate the Y values in the next column in the same way.

- Jon