Spreadsheet Assignment #10:  Non-linear Calibration Curve - Atomic Absorption for Mg

[Back to Group 1 Assignments] [Back to Chem3020 home]

Sometimes a series of data points is best fit by a non-linear curve.  How can you tell when a linear or a non-linear fit is best?  One method is to calculate the difference between your actual data points and the calculated value of your best-fit line at each point.  These differences are often called the "residuals."   It is helpful to plot the residuals to see which type of line best fits the data.   Since the residuals often have both positive and negative values, they are normally squared and the results summed for all data points.  The goal of obtaining the best fit, is to minimize the sum of the squared residuals.

In this spreadsheet activity, you will fit atomic absorption data from a magnesium calibration curve using both a linear equation, y=mx + b, and a second-degree polynomial of the form y = ax^2 + bx +c.  If your spreadsheet can fit a polynomial of this form, great.  Try this and see what results you get. 

Whether you do or not, you should still manually enter variables for a, b, and c. Enter equations that use the values you enter in these cells to create a best-fit line and plot both the data and your line on the same chart.  Then, edit your values for a, b, and c.  Through trial and error, along with some simple logic, find the three values that give you the best fit. You should be able to visualize the effects of your values on the chart and on the sum of the squared residuals.  When you think you have a good fit print your results and compare your result with the computer fit(s).

Report which equation (linear or polynomial) gives you the best fit for the calibration curve and defend your answer.  

(Click to download the data set: 10-AA_Mg)

 

Linear Regression with y = m * x + b
.	Slope (m):	.	.	.
.	Intercept: (b):	.	.	.
.
[Mg]	Absorbance	Calculated	.	Residuals
(ppm)	Reading	Best-fit Line	Residuals	Squared
0.0	0.006	.	.	.
3.0	0.077	.	.	.
6.0	0.144	.	.	.
9.0	0.205	.	.	.
12.0	0.261	.	.	.
15.0	0.310	.	.	.
20.0	0.383	.	.	.
25.0	0.437	.	.	.
30.0	0.474	.	.	.
.	.	.	Sum:	.
Polynomial Fit with a*x^2 + b* x + c
.	.	.	Initial Estimates for Constants:
.	x^2 coef. (a) =	.	<-- Between -1e-3 and -1e-4 <-- Between 0.010 and 0.100 <-- Between -0.010 and +0.100
.	x coef. (b) =	.
.	constant (c) =	.
.
[Mg]	Absorbance	Calculated	.	Residuals
(ppm)	Reading	Best-fit Line	Residuals	Squared:
0.0	0.006	.	.	.
3.0	0.077	.	.	.
6.0	0.144	.	.	.
9.0	0.205	.	.	.
12.0	0.261	.	.	.
15.0	0.310	.	.	.
20.0	0.383	.	.	.
25.0	0.437	.	.	.
30.0	0.474	.	.	.
.	.	.	Sum:	.