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 spreadsheet 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 (unN

) 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 satisfies 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 results 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 tf

, vf, and un',

Version • Displays the version and date of the spreadsheet
X-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 each model, 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, reference 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 value 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 the 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.