How can i extrapolate and find/show values on a calibration curve

[Heekla]

Im am writing the scientific report where I have a calibration curve. I want
to use that and find the concentration of an unknown sample. This is easily
done by hand, but how do i show it on exel and on the chart itself?

 

[Bernard Liengme]

I will assume that the calibration curve is linear (y = mx + b)
In some cell (say D1) we can find the slope (m) with the formula
=SLOPE(y-values, x-values)
Something like =SLOPE(B2:B12, A2:A12)
Likewise in say D2 we can find intercept (b) with =INTERCEPT(y-values,
x-values)

Let D3 contain the measured y-value from which you need to compute its
x-value
Let these values be Y and X
So Y =mX + b or X = (Y-b)/m
Turning this into Excel: in cell D4 enter =(D3-D2)/D1

Now having done that, you need to be able to report also your confidence
intervals
See
http://people.stfx.ca/bliengme/ExcelTips/RegressionAnalysisConfidence3.htm

By the way: I hope you are INTERPOLATING for the calibration curve not
EXTRAPOLATING
The former is generally valid, while the later is fraught with dangers

best wishes

 

[Dave Curtis]

Hi,
Following on from Bernard, once you've found the x and y values for your
sample, just plot them as a new series on the chart. You can also add x and
y error bars linking the point with each axis if you want to do it like in
the "old days" when we used graph paper.
Obviously, it looks better if you've removed the lines between the data
points and added a suitably formatted (preferably linear)trendline to the
chart. If you've done the calculations properly, you should see your new
sample point sitting on the trendline.
Don't be tempted to apply some higher order polynomial trendline just
because it looks to fit the data better.

Dave

url:http://www.ureader.com/msg/10297359.aspx