User’s Guide to the ‘QDE Toolkit Pro’

National Research Conseil nationalCouncil Canada de recherches

Excel Tools for Presenting Metrological Comparisons by

B.M. Wood, R.J. Douglas & A.G. Steele

Excel Tools for Presenting Metrological Comparisons by

B.M. Wood, R.J. Douglas & A.G. Steele

Chapter 4. Tables of Equivalence (ii)Chapter 4. Tables of Equivalence (ii)This chapter presents some of the refinements that are in the ‘QDE Toolkit Pro’ for creating Tables of Equivalence. This chapter presents some of the refinements that are in the ‘QDE Toolkit Pro’ for creating Tables of Equivalence.

April 16, 2002 April 16, 2002

Ch 4: 1Ch 4: 1

The Toolkit includes:

tk_mraCCQM_TableBuilder

It creates a table in the format circulated by C.Thomas of BIPM who is in charge of the database.

This format was proposed by the CCQMto meet the needs of chemical metrology.

It is a full, anti-symmetric table of ordered pairs of data with row minus column differences in the first cell and expanded (k0.95 ) uncertainties in the second cell. The choice of the particular cell bolding and cell outlines reflect our understanding of their most recent format.

The Toolkit includes:

tk_mraCCQM_TableBuilder

It creates a table in the format circulated by C.Thomas of BIPM who is in charge of the database.

This format was proposed by the CCQMto meet the needs of chemical metrology.

It is a full, anti-symmetric table of ordered pairs of data with row minus column differences in the first cell and expanded (k0.95 ) uncertainties in the second cell. The choice of the particular cell bolding and cell outlines reflect our understanding of their most recent format.

Tables of Equivalence - CCQM Format Ch 4: 2Ch 4: 2

Tables of Equivalence - CCQM Format

tk_mraCCQM_TableBuilder :

output is row-column difference D12 and expanded uncertainty U12= k12(0.95)(u1

2+u22 - 2 r12u1u2)0.5 as

values in each “double” element of the table.

Each 95% confidence coverage factor k12(0.95) is calculated by numerically integrating the Student distribution with a pair effective degrees of freedom (not truncated to an integer) calculated using the Welch-Satterthwaite formula.

Ch 4: 3Ch 4: 3

Tables of Equivalence - CCQM Format: Pop-up Comments

Pop-up Comments: the macro has automatically added an explanatory pop-up comment to each number in the table.

As your mouse pointer hovers over a cell, its comment “pops-up”. The comments can be managed and edited with Excel.

Ch 4: 4Ch 4: 4

Tables of Equivalence - Formatting Precision and Fonts

The format of the columns in the equivalence tables use the format of the input data. In particular, the number of decimal places used in cells B2 and C2 for the input data is used in the output tables. This is also true for the font typeface and size (although bolding is forced on for differences and off for uncertainties).

Toolkit comments use a fixed 8 point font, and other parts use 8 and 10 point fixed fonts. We suggest using 10 point fonts for your input data, and using Excel’s ‘Zoom’ to adjust the on-screen size.We prefer the way Windows’ “Small Fonts” interacts with the Excel ‘Zoom’ to the way Windows’ “Large Fonts” interacts with the Excel ‘Zoom’ feature.

The format of the columns in the equivalence tables use the format of the input data. In particular, the number of decimal places used in cells B2 and C2 for the input data is used in the output tables. This is also true for the font typeface and size (although bolding is forced on for differences and off for uncertainties).

Toolkit comments use a fixed 8 point font, and other parts use 8 and 10 point fixed fonts. We suggest using 10 point fonts for your input data, and using Excel’s ‘Zoom’ to adjust the on-screen size.We prefer the way Windows’ “Small Fonts” interacts with the Excel ‘Zoom’ to the way Windows’ “Large Fonts” interacts with the Excel ‘Zoom’ feature.

Ch 4: 5Ch 4: 5

Tables of Equivalence - Formatting cell sizes and colors

Individual or groups of table cells can still be formatted using Excel’s standard features which allow you to change cell height and width, colors, borders, etc.

The width and height of cell A2 is read by many macros which use them for scaling some non-critical sizes: comment box sizes and the initial size of graphs.

The background color of the Lab name cells (column A) is set (by tk_Toggle_Outlier_INvsOUT) to yellow for out-of-pool Labs, and to white for in-pool Labs.

Individual or groups of table cells can still be formatted using Excel’s standard features which allow you to change cell height and width, colors, borders, etc.

The width and height of cell A2 is read by many macros which use them for scaling some non-critical sizes: comment box sizes and the initial size of graphs.

The background color of the Lab name cells (column A) is set (by tk_Toggle_Outlier_INvsOUT) to yellow for out-of-pool Labs, and to white for in-pool Labs.

Ch 4: 6Ch 4: 6

Multiple Tables of Equivalence

Multiple tables each have a comment in its anchor cell, identifying the date and time of output. This may help you to correlate notebook and workbook, and to validate the order of tables as subtle changes are made.

Multiple tables can be created on the same worksheet. Simply select a new output ‘anchor’ cell and run (or re-run) a macro. The consequences of different degrees of freedom or correlation coefficients can be easily explored in this way.

Multiple versions of input data (outlier identification, degrees of freedom, correlation coefficients, or conjectured corrections to values or uncertainties…) can be comfortably accommodated on different worksheets of the workbook.

Multiple tables each have a comment in its anchor cell, identifying the date and time of output. This may help you to correlate notebook and workbook, and to validate the order of tables as subtle changes are made.

Multiple tables can be created on the same worksheet. Simply select a new output ‘anchor’ cell and run (or re-run) a macro. The consequences of different degrees of freedom or correlation coefficients can be easily explored in this way.

Multiple versions of input data (outlier identification, degrees of freedom, correlation coefficients, or conjectured corrections to values or uncertainties…) can be comfortably accommodated on different worksheets of the workbook.

Ch 4: 7Ch 4: 7

Where to Put Multiple Tables of Equivalence

Each worksheet has a “safe zone” which macros do not try to clear. If there is a total of N labs and RVs, then columns to the right of N+5, and rows below N+5, are ‘safe’.

A new table can overwrite an old matching table of the same position, type and size. Conflicting “merged cells” can cause problems in some other cases.

Tip: copying one blank cell and pasting it to the entire region of the old table will remove any troubling formatting.

Each worksheet has a “safe zone” which macros do not try to clear. If there is a total of N labs and RVs, then columns to the right of N+5, and rows below N+5, are ‘safe’.

A new table can overwrite an old matching table of the same position, type and size. Conflicting “merged cells” can cause problems in some other cases.

Tip: copying one blank cell and pasting it to the entire region of the old table will remove any troubling formatting.

Ch 4: 8Ch 4: 8

Adjusting Column Widths with Multiple Tables

If columns need to be widened, Excel’s Format | Columns | AutoFit Selection can often help. With columns from other equivalence tables sharing the same column, this process can create problems in other tables.

Tip: Select the table and run the Toolkit Pro macro tk_Widen_Columns_to_Fit, which operates like Excel’s AutoFit except that it only widens and does not contract column widths.

If columns need to be widened, Excel’s Format | Columns | AutoFit Selection can often help. With columns from other equivalence tables sharing the same column, this process can create problems in other tables.

Tip: Select the table and run the Toolkit Pro macro tk_Widen_Columns_to_Fit, which operates like Excel’s AutoFit except that it only widens and does not contract column widths.

Ch 4: 9Ch 4: 9

Multiple Candidate KCRVs

Multiple candidate KCRVs can be accommodated on one worksheet, and even in one Equivalence Table: just add them at the bottom of the input table, below the first name with an “RV” in it. Some automated RV macros are available. They use outlier status and weights stored on worksheet ‘TK Title’, and will be discussed later.

Multiple candidate KCRVs can be accommodated on one worksheet, and even in one Equivalence Table: just add them at the bottom of the input table, below the first name with an “RV” in it. Some automated RV macros are available. They use outlier status and weights stored on worksheet ‘TK Title’, and will be discussed later.

The first row withan ‘RV’ in its namestarts the block ofcandidate KCRVs(here 3 RVs).

The first row withan ‘RV’ in its namestarts the block ofcandidate KCRVs(here 3 RVs).

Ch 4: 10Ch 4: 10

Multiple Candidate KCRVs

Multiple input candidate KCRVs give rise to multiple outputs in the Degrees of Equivalence Tables:Multiple input candidate KCRVs give rise to multiple outputs in the Degrees of Equivalence Tables:

Ch 4: 11Ch 4: 11

Tables of Equivalence - Different Data Sets

The macro works in the same fashion with other comparison data sets.

The Toolkit Pro works best with a separate Excel workbook for each separate Comparison.

A new workbook can be started from an old workbook.

To avoid overwriting the old workbook, simply Save the old workbook, and then Save As a new workbook file.

Next you should edit the just-copied worksheet ‘TK Title’.

Enter, or copy-and-paste the new data into the first three columns of a new worksheet and

run the tk_mraCCQM_TableBuilder macro to create the editable defaults for i and rij, and the output equivalence table.

The macro works in the same fashion with other comparison data sets.

The Toolkit Pro works best with a separate Excel workbook for each separate Comparison.

A new workbook can be started from an old workbook.

To avoid overwriting the old workbook, simply Save the old workbook, and then Save As a new workbook file.

Next you should edit the just-copied worksheet ‘TK Title’.

Enter, or copy-and-paste the new data into the first three columns of a new worksheet and

run the tk_mraCCQM_TableBuilder macro to create the editable defaults for i and rij, and the output equivalence table.

Ch 4: 12Ch 4: 12

Other Tables of Equivalence

The Toolkit offers several other tables of equivalence for use by metrologists and others to more fully communicate the equivalence relationships.These include tables which present:

• the MRA Degree Of Equivalence (DOE) in a format commonly used for physical measurements, (D ± U),

• the single parameter QDE0.95 description, and

• the single probability parameter, Quantified Demonstrated Confidence for agreement in a specified interval, QDC.

Reference:Wood and Douglas, Metrologia 35, 187-196 (1998) This paper covers the essentials of probability calculus for computing confidence in an interval centered on the concept of equivalence.

The Toolkit offers several other tables of equivalence for use by metrologists and others to more fully communicate the equivalence relationships.These include tables which present:

• the MRA Degree Of Equivalence (DOE) in a format commonly used for physical measurements, (D ± U),

• the single parameter QDE0.95 description, and

• the single probability parameter, Quantified Demonstrated Confidence for agreement in a specified interval, QDC.

Reference:Wood and Douglas, Metrologia 35, 187-196 (1998) This paper covers the essentials of probability calculus for computing confidence in an interval centered on the concept of equivalence.

Ch 4: 13Ch 4: 13

The Toolkit includes :

tk_mraDOE_TableBuilder creates a table: row-column difference D ± k12(0.95)(u1

2+u22 - 2 r12u1u2)0.5

as a string in each element of the table

The Toolkit includes :

tk_mraDOE_TableBuilder creates a table: row-column difference D ± k12(0.95)(u1

2+u22 - 2 r12u1u2)0.5

as a string in each element of the table

MRA Degree Of Equivalence Table

again, there are lots of pop-up comments automatically addedagain, there are lots of pop-up comments automatically added

Ch 4: 14Ch 4: 14

Degree of Equivalence and QDE0.95

The Toolkit includes :

tk_mraDOEandQDE_TableBuilder creates a table: row-column difference ± k12(0.95)(u1

2+u22 - 2 r12u1u2)0.5 as a string in the

above-diagonal elements of the table and in the below-diagonal elements

it places the 95% confidence interval for agreement, QDE0.95.

The Toolkit includes :

tk_mraDOEandQDE_TableBuilder creates a table: row-column difference ± k12(0.95)(u1

2+u22 - 2 r12u1u2)0.5 as a string in the

above-diagonal elements of the table and in the below-diagonal elements

it places the 95% confidence interval for agreement, QDE0.95.

again, there are lots of pop-up comments automatically addedagain, there are lots of pop-up comments automatically added

Ch 4: 15Ch 4: 15