Spreadsheet #19: Quality Assurance Testing & Reports
[Back to Group 1 Assignments] [Back to Chem3020 home]
According to "Good Laboratory Practice," each chemist should check the analytical results of a standard or reference material every day, to determine if their analytical method is giving correct results. Such analytical standards are usually mixed in among a series of samples and the results of the standards are compared to its "true value," to see if the method is yielding reliable results. A drift in the reported values for the standard reveals that a problem is appearing in the analytical method and something needs fixing as soon as possible.
The following data is from such a series of tests. Calculate and plot
the results of the following moisture determinations for a standard sample
containing exactly 8.00% water. If any results exceed 8.80% or drop below
7.2%, type "ERROR: Standard is Out of Specification" in a cell next
to the calculated percentage. Draw lines on the graph at 7.2% and 8.8% to show
the deviation limits allowed. Hint: Use the " =IF(logical_test,value_if_true,value_if_false)”
command to check results and put this message in automatically. (Click here to download the data file: 19-Moist.)
|
Spreadsheets #19 & # 20: % Moisture Calculations |
|||||
|
Upper Limit: 8.80% |
|||||
|
Lower Limit: 7.20% |
|||||
|
Standard |
Test Date |
Sample |
Loss on |
% Moisture |
Note: |
|
Label |
(Nov__, 98) |
Mass (g) |
Drying (g) |
. |
. |
|
P13B |
2 |
10.548 |
0.891 |
. |
. |
|
S24D |
3 |
10.229 |
0.920 |
. |
. |
|
S26F |
5 |
10.851 |
0.869 |
. |
. |
|
R12X |
6 |
10.004 |
0.712 |
. |
. |
|
T02B |
7 |
11.468 |
0.953 |
. |
. |
|
T02C |
9 |
9.954 |
0.807 |
. |
. |
|
T07F |
10 |
10.445 |
0.835 |
. |
. |
|
T08G |
11 |
10.692 |
0.778 |
. |
. |
|
T76F |
12 |
10.543 |
0.823 |
. |
. |
|
U11U |
13 |
10.809 |
0.817 |
. |
. |
|
U11V |
15 |
10.439 |
0.762 |
. |
. |
|
W56B |
16 |
11.107 |
0.801 |
. |
. |
|
X43G |
17 |
9.799 |
0.704 |
. |
. |
|
Y08E |
18 |
11.935 |
0.845 |
. |
. |
|
D24Y |
19 |
10.886 |
0.750 |
. |
. |
|
D24Z |
21 |
10.944 |
0.717 |
. |
. |
|
K77Q |
22 |
10.080 |
0.808 |
. |
. |
|
K77R |
23 |
10.693 |
0.864 |
. |
. |
|
D00E |
24 |
10.442 |
0.834 |
. |
. |
|
D01G |
25 |
10.569 |
0.840 |
. |
. |
|
H32U |
26 |
9.897 |
0.800 |
. |
. |
|
G84P |
27 |
9.369 |
0.764 |
. |
. |
|
G21S |
28 |
10.581 |
0.835 |
. |
. |
|
G22S |
29 |
10.600 |
0.900 |
. |
. |
|
G32S |
30 |
10.949 |
0.884 |
. |
. |
|
Excel Programming Tips: |
|
|
IF(logical_test,value_if_true,value_if_false) Example: If checking a value in cell E6 to display a message in cell E7, enter the command: =if(D6>1.0,”Greater than one”,”Less than or equal to one”) You will need to “nest” your IF commands to accomplish this assignment, since you must test above or below two different limits. Consult Excel’s help files for detailed examples. Example: Checking cell D6 for a value greater than 5 and less than 2: =if(D6>5,”greater than 5”,(if(D6<3,”less than 3”,”between 3 and 5”))) |
|