X-1 X.0 INSTRUCTIONS FOR USING PS-11 CORRELATION TEST SPREADSHEET
This section provides instructions for using the PS-11 Correlation Test Spreadsheet. The spreadsheet is a Microsoft ExcelTM file that performs the regression analyses for determining correlation equations for the five models described in Section 12.3 of PS-11. The spreadsheet also determines which model provides the best fit while satisfying all of the acceptance criteria specified in Section 13.2 of PS-11. The spread sheet can handle up to 100 test runs on single sampling train data or up to 50 test runs on paired sampling train data.
Note: The current version of the spreadsheet (Version 2-6) accounts for several changes to PS-11 that will be incorporated into amendments to the January 12, 2004, publication of the final rule. These changes include a revised procedure for calculating the confidence and tolerance interval half ranges for the exponential and power correlation models, and revised values for one of the statistical parameters (u n N ) presented in Table 1 of the rule.

X.1 DESCRIPTION OF SPREADSHEET

X.1.1 Overview of the Worksheets
The spreadsheet contains several worksheets, which are listed in Table X-1 along with their functions. The worksheets appear as tabs at the bottom of the Excel screen; click on these tabs to move from one worksheet to another. Because of the number of worksheets, not all tabs are visible at one time, but you can scroll through the tabs by clicking on the arrow icons in the lower left corner of the Excel screen.
The initial worksheet (“Test Data”) is used for data entry. The next 10 worksheets consist of 5 pairs of worksheets. Each pair applies to one of the five correlation models described in Section 12.3 of PS-11: linear, polynomial, logarithmic, exponential, and power. For each of these models, the first worksheet (e.g., “Linear Calcs”) displays the results of the calculations for each data point. The second worksheet in the pair (e.g., “Linear Summary”) displays a summary of the calculations, the correlation equation, and a summary of how the model compares to the acceptance criteria. Following the 10 model-specific worksheets is the “Best Model” worksheet, which displays how each of the models satisfi es the acceptance criteria of PS-11 and identifies the model that provides the best fit for the data. The “Predicted Values” worksheet displays the PM concentrations predicted by each of the five correlation models. The “Stats Table” worksheet is a lookup table that provides the statistical parameters needed to determine the confidence and tolerance interval half range percentages. The final worksheet (“Version”) indicates the spreadsheet version and date.X-2 Table X-1. Description of Worksheets in PS -11 Correlation Test Calculation Spreadsheet

Worksheet Function Test Data • Correlation test data entry sheet • Displays graph of data Linear Calcs • Performs the calculations for fitting a linear regression model to the correlation test data Linear Summary • Presents the r esults of the linear correlation • Includes a summary of the acceptance criteria • Displays graph of data and correlation equation Polynomial Calcs • Performs the calculations for fitting a polynomial regression model to the correlation test data Polynomial Summary • Presents the results of the polynomial correlation • Includes a summary of the acceptance criteria • Displays graph of data and correlation equation Logarithmic Calcs • Performs the calculations for fitting a logarithmic regression model to the correlation test data Logarithmic Summary • Presents the results of the logarithmic correlation • Includes a summary of the acceptance criteria • Displays graph of data and correlation equation Exponential Calcs • Performs the calculations for fitting an exponential regression model to the correlation test data Exponential Summary • Presents the results of the exponential correlation • Includes a summary of the acceptance criteria • Displays graph of data and correlation equation Power Calcs • Performs the calculations for fitting a power regression model to the correlation test data Power Summary • Presents the results of the power correlation • Includes a summary of the acceptance criteria • Displays graph of data and correlation equation Best Model • For each of the five models, displays - Correlation coefficient - Confidence interval half range percentage - Tolerance interval half range percentage • For polynomial model, also indicates if the maximum or minimum value occurs within the allowable range • Indicates which model provides the best fit based on correlation coefficient
Predicted Values • For user-entered PM CEMS response values, displays the predicted PM concentrations for each of the five models Stats Table • Provides a table of statistical values used in determining t f , v f , and u n' , Version • Displays the version and date of the spreadsheetX-3 X.1.2 Locked Cells
All but two of the worksheets are completely locked, which means that you can open the worksheet, view the results of the calculations, and print out the results, but you cannot modify any of the cells in the worksheet.
The “Test Data” worksheet is partially locked. Figure X-1 shows the Test Data worksheet for an example problem. You can enter the test run numbers from the correlation test in cells A6 through A105 and the correlation test data into the block of cells between B6 and C105: the PM CEMS response data go in column B, and the PM concentrations, as determined by the appropriate reference method, go in column C. You can also enter the facility name, location, emission unit name, and test dates in cells F3 to F6, respectively, and the emission limit and emission limit units in cells F9 and G9, respectively. All other cells in the “Test Data” worksheet are locked.

Figure X-1. Example of “Test Data” worksheet.X-4 The “Predicted Values” worksheet is also partially locked. You can enter PM CEMS response values in cells A8 through A39 and see the resulting PM concentrations predicted by each of the correlation models. All other cells in this worksheet are locked.

X.1.3 Calculation Worksheets
For each of the five correlation models described in Section 12.3 of PS-11, the spreadsheet includes a calculation worksheet. Figure X-2 shows an example of the calculation worksheet for the linear model.

The general format of the calculation worksheets is similar for all of the correlation models. The test run numbers, PM CEMS response (x) values, and PM concentration (y) values appear in the first columns. These values are pulled directly from the “Test Data” worksheet and do not have to be reentered here. Test run numbers appear in column A. PM CEMS response values appear in column B. If the correlation model requires the PM CEMS values to be transformed, as is the case for the logarithmic and power models, the transformed PM CEMS values appear in column C. PM concentration values appear in column C for the linear, polynomial, and exponential models. For the logarithmic and power models, the PM concentrations are displayed Figure X-2. Example of “Linear Calcs” worksheet.X-5 Figure X-3. Example of “Linear Summary” worksheet. in column D. The next series of columns in each “Calcs” worksheet presents the results of various intermediate calculations that are needed to determine the correlation equation. These calculations vary from model to model and are defined in the equations presented in Section 12.3 of PS-11. The last columns show the results of the calculations for the confidence interval half range, tolerance interval half range, and the other statistical parameters used to determine the confidence and tolerance interval half ranges.
X.1.4. Summary Worksheets
The summary worksheets display the equations and the results of the intermediate calculations that are used to determine the correlation equation, correlation coefficient, and confidence and tolerance interval half range percentages at the appropriate PM CEMS response value. Figure X-3 shows an example of the summary worksheet for the linear model. The first three columns show the variables, equations, and calculated values, respectively. The summary worksheets also show a summary of the acceptance criteria specified in Section 13.2 of PS-11 and indicate if the acceptance criteria were satisfied. Finally, the summary worksheets show a plot of the correlation test data and the correlation curve.X-6 X.1.5 Best Model Worksheet
This worksheet provides a summary of the results for all of the models. Figure X-4 shows an example of the “Best Model” worksheet. For eachmodel, the correlation coefficient, confidence interval half range percentage, and tolerance interval half range percentage are listed with an indication of whether the model meets the acceptance criterion for each of these parameters. For the polynomial model, the worksheet indicates if the model satisfies the criterion for minimum or maximum value specified in Section 12.4(3) of PS-11. The worksheet shows whether each model satisfies all of the criteria for PS-11. The worksheet identifies the best model based on the correlation coefficients for the models that meet all of the acceptance criteria.

X 1.6 Predicted Values Worksheet
This worksheet displays the predicted concentrations for each of the five models as a function of any PM CEMS response values that are entered in cells A8 to A39. Figure X-5 shows an example of the “Predicted Values” worksheet. Figure X-4. Example of “Best Model” worksheet.X-7 X.2 HOW TO USE THE SPREADSHEET
X.2.1 Data Entry
Using the spreadsheet entails entering the correlation test data and the emission limit into the “Test Data” worksheet. The spreadsheet is designed to handle up to 100 data points and can be modified to handle more. As you enter data, the data are displayed on a graph that appears on the same worksheet.
As indicated in Section 8.6(1)(iii) of PS-11, refere nce method test results must first be converted to units of mass concentration that are consistent with the conditions measured by the PM CEMS. For example, if the PM CEMS responds in terms of actual conditions, the PM emission data should be converted to units of milligrams per actual cubic meter (mg/acm). You should perform this data conversion before you enter the data into the spreadsheet. Figure X-5. Example of “Predicted Values” worksheet.X-8 For each test run, enter the run number, PM CEMS response value, and the PM concentration into columns A, B, and C, respectively. You can enter the data in any order; for example, it is not necessary to order the data by concentration from lowest to highest. If the PM data were measured using paired sampling trains, each PM concentration should be entered as a separate data point (x value), as specified in Section 12.3 of PS-11. In such cases, you would enter the same PM CEMS response value twice, once for each of the two sampling train data points. For example, assume that the PM concentrations measured by Trains A and B during Run 3 were 6.8 and 7.3 mg/acm, respectively, and the PM CEMS response for Run 3 was 5.4 mA. For Run 3A, you would enter 6.8 mg/acm as the x value and 5.4 mA as the y value. For Run 3B, you would enter 7.3 mg/acm as the x value and 5.4 mA as the y value.
To determine the confidence interval and tolerance interval half range percentages, you must also enter the emission limit value and units (e.g., mg/acm, mg/dscm) into cells F9 and G9, respectively.
X.2.2 Checking Results
Once you have entered the test data, the spreadsheet automatically calculates the correlation equation, correlation coefficient, confidence interval half range percentage, and tolerance interval half range percentage for each of the five correlation models. A summary of the results is displayed on the “Best Model” worksheet, as shown in Figure X-4 for an example data set. In this example, all five models meet all of the acceptance criteria. The linear model has the highest correlation coefficient, with an r va lue of 0.927. The confidence interval half range percentage at the mean PM CEMS response value is 4.48 percent, which meets the criterion of less than or equal to 10 percent. The tolerance interval half range percentage at the mean PM CEMS response value is 15.5 percent, which meets the acceptance criterion of less than or equal to 25 percent.
Note: For data that show little to no correlation, the correlation coefficient (r) is undefined because the equation used to calculate r requires taking the square root of a negative number. In such cases, the spreadsheet displays an asterisk instead of a number for the correlation coefficient, and indicates that th e data do not meet the correlation coefficient criterion of 

### $ 0.85 specified in Section 13.2 of PS-11.
You can see more detailed results for any of the correlation models by clicking on the appropriate worksheet tab (e.g., Linear Summary, Polynomial Summary). As shown in Figure X-3, the value of each statistical parameter used in the calculations for the selected model is displayed on the left side of the worksheet. On the right side of the worksheet is a summary of the acceptance criteria specified in Section 13.2 of PS-11, the values for the specific data set and models, and an indication of whether or not the model satisfies those criteria. For all models, the parameters displayed in the summary are the correlation coefficient, confidence interval half range as a percentage of the emission limit, and the tolerance interval half range as a percentage of the emission limit. The correlation curve and equation also are displayed on the data plot in the summary worksheets.
Section 12.4(3) of PS-11 specifies additional criteria for minimum and maximum values for polynomial correlations. Each polynomial correlation curve has a minimum or maximum value,X-9 which must occur outside the allowable range of values. The minimum allowable value corresponds to the minimum PM CEMS response value used to develop the correlation equation. The maximum allowable value corresponds to 1.25 times the maximum PM CEMS response value used to develop the correlation equation. Therefore, the summary of acceptance criteria displayed on the “Polynomial Summary” worksheet also indicates whether the correlation equation satisfies these criteria for minimum and maximum PM CEMS response values.

X.2.4 Predicted Values
The “Predicted Values” worksheet displays the PM concentrations predicted by each of the five correlation models for a range of PM CEMS response values. You can enter any PM CEMS response value into the appropriate cells in column A of the worksheet, and the predicted concentrations are automatically calculated and displayed, as shown in Figure X-5.
X.2.5 Printing Results
To print the results of any worksheet, click on the print button at the top of the spreadsheet. The worksheet will be printed with the file name, worksheet name, and date displayed in the footer of the page.