5-Control Charts for QC Data

There are 3 excel files demonstrated in this module. The objective is for you to become familiar with each and be able to modify it to suit your system.

Ozone routine checks:

1. Open the file QC-O3-2010.xls. This file includes the QC data from the ozone site (we invalidated data from this site in previous exercise). The first sheet lists the data from the routine QC checks during 2010, as well as the calculations in the yellow highlighted columns. The graphs of the data (control chart) are in the 2nd sheet. Also in the 2nd sheet are the critical validation criteria for ozone.

2. (After making a copy of the sheet and working from the copyJ): Edit the QC data (green cells) so that (1) a single-point QC check fails, (2) a span drift fails, and (3) a zero drift fails.

3. Apply conditional formatting to columns Q (zero drift), W (span % difference), X (span drift), and AE (QC1 % difference) so that the values you changed to make that check fail automatically make the QC result in that column red.

Ozone calibration and linearity checks:

1. Open the file Calibration_with_graph.xls, sheet Cal_4_upscale. This file includes ozone analyzer calibration (or a multi-point verification) results.

2. How is the acceptance criteria in cell B6 calculated? Is it consistent with the validation template requirement (in yellow text box in sheet beginning in cell G17)?

Edit the analyzer results for concentration 2 (cells C18 through C24, in green) so that the acceptance criteria now in row 6 is not met, and the value in row 29 turns red.

3. Edit the analyzer results for concentration 5 so that the acceptance criterion for the linearity error (5%) is not met. How does this affect the line in the graph? How does this affect the slope, intercept, and r-squared?

4. Assume you changed analyzers and your new analyzer’s full scale is 500 ppb. Change that value in cell B5. How does that affect the acceptance value in cell B6?

PM flow rate checks:

1. Open the file control charts and excel tips.xls. This file includes flow check data from a PM2.5 analyzer.

2. Change the values in cells C13-C24 (dark green) so that some checks fail.

3. Review the sheet “if-then flagging” for an explanation of the way flags is applied in column F.

4. If there is time, review the sheet SelectDates for an intro to Array calculations.