# Data processing exercise

Student Details
First Name Surname Student No. Note: do not change any of the model data in red until all the formulas have been inserted and you
have checked that they are working properly by comparing the results with the model answers.
Once the formulas are correct all data in red should be replaced by your assigned data
Data and Analysis – AAS Don’t forget to plot a calibration graph with error bars showing the 99% confidence interval
Tablet mass / g Sample mass / g Standard Average Instrument Standard 99%
1.0217 0.5003 Conc  Reading RSD Deviation CI Use LINEST
/ppm /a.u. /% /a.u. /a.u.
0.00 0.010 5.10 ? ? m ? ? c
10.00 0.042 0.01 ? ? Sslope ? ? Sintercept
20.00 0.106 0.40 ? ? R2 ? ? Sy/x
30.00 0.142 0.90 ? ? F ? ? Degrees of Freedom
40.00 0.210 0.80 ? ? SSreg ? ? SSresid
50.00 0.263 0.50 ? ?
Sample 1 0.251 0.600 ? ?
Sample 2 0.251 0.600 ? ?
Number of calibration graph data points Number of sample repeat measurements Limit of Detection from graph
nC = 6 nS = 5
Choice of uncertainty threshold
Mean values of calibration graph x and y k= 3
 x =
?
 y =
? yL = c + k.Sy/x= ?
xL = (yL – c)/m = ?
 ppm
5.829 ppm, expected value for test data
Σ(x – x)² = (Sy/x / Sslope)2 = ?
Sample 1 Sample 2
y0 = 0.251 y0 = 0.251
x0 = (y0 − c) / m = ? x0 = (y0 − c) / m = ?
Contributions to uncertainty in x0 Contributions to uncertainty in x0
1/ns= ? If one of these contributions is large it will dominate uncertainty in the determined concentration 1/ns= ? If one of these contributions is large it will dominate uncertainty in the determined concentration
1/nc= ? 1/nc= ?
(y0-y)2/m2Σ(x – x)²= ? (y0-y)2/m2Σ(x – x)²= ?
conc =  x0 = ? ppm conc =  x0 = ? ppm
sX0 = ? ppm sX0 = ? ppm
conc = ? × x0 = ?
 ppm
974 ppm expected value for test data conc = ? × x0 = ?
 ppm
974 ppm expected value for test data
sconc = ? × sX0 = ?
 ppm
32 ppm expected value for test data sconc = ? × sX0 = ?
 ppm
32 ppm expected value for test data
Mass Bi in tablet = ? mg Mass Bi in tablet = ? mg
Mass Bi subsalicylate in tablet = ?
 mg
344 mg expected values for test data Mass Bi subsalicylate in tablet = ?
 mg
344 mg expected values for test data
Uncertainty = ?
 mg
11 mg expected values for test data Uncertainty = ?
 mg
11 mg expected values for test data
Student Details
First Name Surname Student No. Note: do not change any of the model data in red until all the formulas have been inserted and you
have checked that they are working properly by comparing the results with the model answers.
Once the formulas are correct all data in red should be replaced by your assigned data
Data and Analysis – ICP-OES Don’t forget to plot a calibration graph with error bars showing the 99% confidence interval
Tablet mass / g Sample mass / g Standard Average Instrument Standard 99%
1.0217 0.5003 Conc  Reading RSD Deviation CI Use LINEST
/ppm /a.u. /% /a.u. /a.u.
0.00 68.98 5.10 ? ? m ? ? c
10.00 1047 0.01 ? ? Sslope ? ? Sintercept
20.00 2014 0.40 ? ? R2 ? ? Sy/x
30.00 3025 0.90 ? ? F ? ? Degrees of Freedom
40.00 4085 0.80 ? ? SSreg ? ? SSresid
50.00 5182 0.50 ? ?
Sample 1 4940 0.600 ? ?
Sample 2 4940 0.600 ? ?
Number of calibration graph data points Number of sample repeat measurements Limit of Detection from graph
nC = 6 nS = 5
Choice of uncertainty threshold
Mean values of calibration graph x and y k= 3
 x =
?
 y =
? yL = c + k.Sy/x= ?
xL = (yL – c)/m = ?
 ppm
1.588 ppm, expected value for test data
Σ(x – x)² = (Sy/x / Sslope)2 = ?
Sample 1 Sample 2
y0 = 4940.000 y0 = 4940.000
x0 = (y0 − c) / m = ? x0 = (y0 − c) / m = ?
Contributions to uncertainty in x0 Contributions to uncertainty in x0
1/ns= If one of these contributions is large it will dominate uncertainty in the determined concentration 1/ns= If one of these contributions is large it will dominate uncertainty in the determined concentration
1/nc= 1/nc=
(y0-y)2/m2Σ(x – x)²= (y0-y)2/m2Σ(x – x)²=
conc =  x0 = ? ppm conc =  x0 = ? ppm
sX0 = ? ppm sX0 = ? ppm
conc = ? × x0 = ?
 ppm
964.8 ppm expected value for test data conc = ? × x0 = ?
 ppm
964.8 ppm expected value for test data
sconc = ? × sX0 = ?
 ppm
8.7 ppm expected value for test data sconc = ? × sX0 = ?
 ppm
8.7 ppm expected value for test data
Mass Bi in tablet = ? mg Mass Bi in tablet = ? mg
Mass Bi subsalicylate in tablet = ?
 mg
341 mg expected values for test data Mass Bi subsalicylate in tablet = ?
 mg
341 mg expected values for test data
Uncertainty = ? mg 3 mg expected values for test data Uncertainty = ? mg 3 mg expected values for test data
Student Details
First Name Surname Student No. Note: do not change any of the model data until all the formulas have been inserted and you
have checked that the formulas are working properly by comparing the results with the model answers.
Data on this sheet is automatically populated from the AAS and ICP-OES table
Data and Analysis – Comparison of Methods
Back calculate concentration x=(y-c)/m
of standards from regression
Standard ICP-OES AAS ICP-OES AAS
Conc Average Average /ppm /ppm
/ppm /a.u. /a.u. (x) (y)
0.00 0.00 (x) ? ?
10.00 0.00 ? ? ?
20.00 0.00 ? ? ?
30.00 0.00 ? ? ?
40.00 0.00 ? ? ?
50.00 0.00 ? ? ?
m c
ICP-OES ? ?
AAS ? ?
Analysis of AAS vs ICP-OES graph
Use LINEST 0 to 50 ppm
expected values for test data
m ? ? c m 1.000721585
Sslope ? ? Sintercept c -0.01803961
R2 ? ? Sy/x R2 0.993512196
F ? ? Degrees of Freedom DoF 4
SSreg ? ? SSresid
Use LINEST 0 to 40 ppm
expected values for test data
m ? ? c m 0.988430573
Sslope ? ? Sintercept c 0.149175729
R2 ? ? Sy/x R2 0.988322573 Test data shows no deviation from linearity
F ? ? Degrees of Freedom DoF 3 what does your real data show?
SSreg ? ? SSresid