Scatter graph linear trendline and equation to find unknown

[S_9]

Hello,
First, I'm very much a newbie with Excel, so go easy on me please.
I have some data on a scatter graph, and have put in a linear trendline. I have got it to show to equation, but I cannot figure out how to use that to find the point at which my unknown y values intercept.

Any help is appreciated.
I don't know what you need to help me, so I've attached (I hope) a screenshot of my data points and graph. What I need to find is the y (concentration) of two points. The equation I've got there doesn't help me one bit.

How do I get an actual y=mx+b equation out of that graph so that I can put in my x-values and calculate the y-values?

I apologise if this is in the wrong section, or a silly question. I did try to search for my answer first.

[MrShorty]

How do I get an actual y=mx+b equation out of that graph so that I can put in my x-values and calculate the y-values?

For someone who is "very much a newbie with Excel," the easiest at this point might be to hand enter the m and b values you can see in the chart into spreadsheet cells (maybe D2 and E2). Then your formula in B8 can be =A8*$D$2+$E$2 (note the use of relative and absolute references. Copy down to B9 for the second computation. This has some good introductory information on building formulas, including relative and absolute references http://office.microsoft.com/en-us/ex...997.aspx?CTT=1

If this is something you are going to do a lot of, you will eventually want to learn how to use the LINEST() function to regress equations. http://office.microsoft.com/en-us/ex...005209155.aspx

[S_9]

Thanks for replying.
I tried what you said, but my points were not on the trendline. They were close, but no cigar. Did I do something wrong?

Reading about the LINEST function just confused me - I'll save that until I've had a bit more practice.

[S_9]

Just posting this in the same thread - is it possible to put lines on the graph horizontally from my two unknown absorbances to the trendline, and vertically across to the concentration?

[MrShorty]

I tried what you said, but my points were not on the trendline. They were close, but no cigar. Did I do something wrong?

Hard to say without knowing exactly what you did. What concentration did you get? What value did you expect to get? Did you double check that you had entered the constants for the trendline correctly?

is it possible to put lines on the graph horizontally from my two unknown absorbances to the trendline, and vertically across to the concentration?

Yes, but Excel does not inherently know how to do this. The first step in adding these lines is being able to calculate y from x. Once you can calculate y from x, then you can plot these points as a second series, and format them with error bars that extend to the axes. But, as noted, the first part of this is being able to calculate y from x, which is the original problem you've given.

[S_9]

Apologies - I've attaches two screenshots, one after the first point has been added, and another from after the formula has been dragged down and the second point added on the curve.
The first point is pretty much on the line, the second isn't. And after the two points have been put on the graph, the trendline moves slightly to compensate, messing up the concentrations I put in by eye.

[MrShorty]

Your formulas in B8 and B9 are, effectively y=0.2x+14 where y is concentration and x is absorbance. Why are you using this equation instead of the y=155.2x-17.2 that the trendline was showing in the first screenshot?

It looks like D1:E7 is where you put the concentrations you eyeballed from the original plot.

I can see that you pasted the text representation of the original trendline in H3, but you have not extracted the constants into their own cells. It looks to me like you need to go back to the first screenshot you gave, extract the constants for the trendline, and use those constants in building the formulas in B8 and B9.

[S_9]

I wasn't using any equation to get what's in B8 and B9? I used the formula you gave me, and the x- and y-values by eye. Like you said to do.

I asked here how to do this because when I first tried to use y=155.2x-17.2 to get my unknowns by inserting x, it wouldn't work for me, the numbers it gave me were completely out. Now that I have just tried to insert my unknown absorbance into x, it has worked. Go figure..

The two new points I have on a new series - they're on the line but not exactly centre. We'll not worry about that.
So...there's another screenshot to show you. Thank you for your help.
Now that that is done, how do I get those lines for series 2 from the points down to the x-axis and across to the y-axis? Without printing it out and drawing them on myself like an idiot..

[MrShorty]

I wasn't using any equation to get what's in B8 and B9? I used the formula you gave me, and the x- and y-values by eye. Like you said to do.

Once again, I failed to communicate clearly. My intention was for m and b from the trendline to go into D2 and E2, not eyeballed x and y. My apologies for not explaining clearly.

they're on the line but not exactly centre.

Not certain but, if you've used the values as given in the latest screenshot -- or, worse yet, the rounded values I gave in post 7-- this could be rounding error. Try formatting the trendline to show more significant figures, and use the expanded values in your formulas.

how do I get those lines for series 2 from the points down to the x-axis and across to the y-axis?

using error bars. http://office.microsoft.com/en-us/ex...159.aspx?CTT=1
1st add the error bars, then format the error bar to only show the negative error bar and set it to be 100%

[S_9]

Ah, being a complete noob I wouldn't have picked up that you meant that, sorry.
I have gotten better results with the equation set to 8 decimal places!

You are fantastic, thank you so much for your help. My graph looks very presentable now