calibration curve

[PAUL GRAZIDE]

I am setting up a chemistry calibration curve using data below

OD reading Concentration
..020 0
..045 10
..245 40 etc

Plotting in XY scatter chart and get trendline using polynomial function.
How can I determine the concentration of an unkown solution ? I want to use
trendline data to do this.

 

[Bernard Liengme]

Put the Conc data in column A and the OD data in B
You can have headers ("Concentration", "OD" ) in row 1
Let's say the last row is 10
Select A1:B10, click the chart tool, select XY chart, use the first subtype
(markers without line)
On chart right click on a data point, select Insert Trendline and specify
polynomial or required order
To out the coefficients into cell use LINEST as shown in
http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

You now have equation such as y = ax^2 + bx +c where x is conc and y is OD
You will have real numbers 12.45 = 2.34c^2 - 5.46c + 8.9 and will need to
solve for x

Most calibration curve I every used were linear y =mx + b
So x = (y - b)/m

best wishes

 

[Guest]

Are you sure that a polynomial is an appropriate calibration curve here? You
don't give enough points to assess the fit, but I am much more familiar with
OD calibration curves that are either log-linear or a 4- or 5-parameter
logistic equation.

Jerry

 

[PAUL GRAZIDE]

Polynomial in this case is correct, because the curve is not linear. If
curve was linear, then i would use y=mx+b.

Thanks

 

[Guest]

I did not suggest that your data was not curved. I suggested that over an
appropriate range log(OD) might straighten it into a line. Barring that, you
would probably get a better fit from a model that flattens out toward max and
min values (as OD must) instead of increasing or decreasing without bound (as
a polynomial must).

Jerry