Linear Calibration Example
Enter the data:
- Either, leave a blank column on the left for labels as done
with column A below. Or in column A record data from which the
x-values are calculated. For example, you might record the volumes
of standard diluted for obtaining the set of standards used to
determine the graph.
- Enter a column of 1's for each data points as done in column
B.
- Enter a column of the x-values as done in column C. If a
measurement is repeated, enter the x-value multiple times. Often
the x-values will be calculated from solution preparation data
recorded elsewhere in the spreadsheet. For example, if column A
recorded the volume diluted, the values in column C might be
calculated from these volumes and from the concentration of a
stock standard displayed elsewhere in the spreadsheet.
- Enter the corresponding y-values as done in column D.
- Calculate the y-values which fall on the best straight line
using the linfit(x-range,y-range) as
shown in column E.
Graph the data and insert it into the
spreadsheet.
Linear Regression:
- Type the lls(x-range,y-range) function into a cell such as
cell B35 in the example below. This function lies in the same
column as the 1's. Leave room for labels as shown with row 34 and
column A below.
- Select both the 1's and the x's for the x-range. This
range is a 2-column x n-row block, where n = number of points.
- Select the experimental y-values NOT the values fit to
a straight line by the linfit function.
- A 3-column x 4-row set of results will appear.
- Label the 3 columns and 4 rows as shown. (In the figure below
the third column, second row is mislabeled; it should say R**2.)
- First column, first row is the y-intercept,
a0; second row is the standard deviation in
the y-intercept, sa0; third row is the ratio
of a0 to sa0, which is the t-value for
a0 being significantly different from zero and
forth row is the probability that random errors might
cause a 0 y-intercept to be as large as the observed value.
- Second column, first row is the slope,
a1; second row is the standard deviation in
the slope, sa1; third row is the ratio of
a1 to sa1, which is the t-value for
a1 being significantly different from zero and
forth row is the probability that random errors might
cause a 0 slope to be as large as the observed value.
- Third column, first row is the variance in
the y-values, which is the square of the standard deviation;
second row is the square of the R-value. The last two
rows are other statistical parameters.
If the straight line is used as a calibration curve, the slope and
y-intercept will be used to calculate the concentration of an
unknown.
- Enter the repeated measurements of "y" on the unknown as shown
in cells B42..B44 below.
- Compute the average and standard deviation in the repeated
y-values for the unknown as shown in B46 and B47.
- Calculate the concentration and its standard deviation as
shown in cells B49 and B50.
- Count the number of calibration points as shown in cell B51.
NOTE: Next that the equations used for
these calculations are revealed in cells C46, C47, D51 and A52-53.
Although it is NOT shown here, calculate the 95 % confidence
limits (CL) in the concentration. For df = 8, the
t-value is 2.306 and CL = 2.306*B50.