User’s Guide to the ‘QDE Toolkit Pro’ Excel Tools for Presenting Metrological Comparisons

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

Guide Version 2.07 for QDE Toolkit Pro Version 2.07

May 2002…September 2003

National Research Conseil nationalCouncil Canada de recherches

Table of Contents for this Guide: 2

The detail and screenshot resolution make this a large file. Sometimes it needs to be broken into Chapter files.

Chapter Pages

1. Introduction/Overview 3 - 15

2. Getting Started 16 – 293. Building Equivalence Tables (i) 30 - 434. Equivalence Tables (ii) 44 - 585. Building Confidence-in-CMC Tables 59 - 826. Degrees of Freedom 83 - 93 7. Covariance and Correlation Coefficients 94 - 1028. Graphing and Pooling (i) 103 - 1109. Graphing and Pooling (ii) 111 - 12810. Project Organization: Excel/Workbooks/Worksheets 129 - 14311. Admitting and Excluding Labs in the Pool - Outliers 144 - 14812. Automated RVs and RV creators 149 - 17613. Creating Lab Views 177 - 18214. VBA Source Code 183 - 19215. Afterthoughts 193 - 202

National Research Conseil nationalCouncil Canada de recherches

Chapter 1. Introduction and OverviewIn this chapter, we touch on the role of the Toolkit.

An inter-Laboratory comparison, after perhaps difficult analysis into differences and uncertainties, looks deceptively simple. Choose a reference value, assign its uncertainty, apply standard null hypothesis tests and you’re done…It is often much more difficult than this, and at times is even acrimonious.

The QDE Toolkit Pro includes Excel macros to deal with Equivalence, with or without the intermediary of a particular reference value (RV), and provides facilities for examining various candidate RVs and u(RV)’s.

May 2, 2002

User’s Guide to the ‘QDE Toolkit Pro’Excel Tools for Presenting Metrological Comparisons

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

Ch 1: 3

What’s this “QDE”?

It is now only a minor part of the tools available in the current version of the QDE Toolkit.

It’s a single number, having the units of the measurement, that can report how well two measurement processes agree, based on a comparison that has revealed a difference and an uncertainty.

It is the “Quantified Demonstrated Equivalence” of the comparison, and is appropriate only where a single parameter is needed to summarize differences to be expected from similar measurements.

Ch 1: 4

What’s the use of a “QDE” number?

QDE supports simple sentences...

“On the basis of this comparison, similar measurements can be expected to agree within 2.3m, 19 times out of 20†.”

† or “... , with 95% confidence.”

QDE is able to blend the results from the two measurement processes into one QDE parameter. It converts four to seven parameters into one easy-to-use parameter, usually starting from two values with two uncertainties, and perhaps two effective degrees of freedom and a correlation coefficient.

Ch 1: 5

What’s the “QDE” Toolkit?

It’s a suite of Excel macros that can automatically convert a small table of results, from a comparison of N Labs’ measurement processes, into a standard form such as the Degrees of Equivalence Table with ~2N2 entries.

QDE tabulations are also available in the Toolkit.

Version 1 of the QDE Toolkit was written by A.G. Steele of NRC’s Thermometry Group, it was released for public use in October, 2000 and about 100 copies of Version 1.14 (and its maintenance release 1.15) have been distributed.

The QDE Toolkit Pro is the next major public release.

Ch 1: 6

What’s New in the Toolkit ‘Pro’?Loaded by Microsoft Excel, QDE ‘Toolkit Pro’ simply handles Key Comparisons of

many Labs with degrees of freedom and inter-laboratory correlation coefficients,

and provides simple defaults if they are not available.

Now ~10000 lines of Visual Basic code andcomments provide help with the repetitivetasks of analysis and presentation: titles,units, comments and graphs that go beyond error bars.

The toolkit displays all its work openlyon the Excel spreadsheets, rather thanhaving macros communicate with each

other without your supervision. It canalso be shared by other open workbooks.

Ch 1: 7

Who should consider the Toolkit ‘Pro’?It is for ‘pros’ in metrology, who are familiar with degrees of freedom and interlaboratory correlation coefficients, and want to treat them with rigor.

It is for those with a workingknowledge of Excel.

… and communicate their thoughtsclearly and simply to others.

It is for those who want tothink about their comparisons...

It still does all the simple analyses of Version 1.14, but explicitly writes out its default assumptions infinite degrees of freedom and no correlations...

Ch 1: 8

Who should try the Toolkit ‘Pro’?Pilots for Key Comparisons: the Toolkit provides KCRV-independent analyses, can help examine a variety of candidate KCRVs, and automatically builds standard Equivalence Tables for cross-checking with those done manually or with other software.

Participants in Key Comparisons who wish to present arigorous argument about some aspect of the Comparison.

Those voting on MRA Appendix C CMCs, or those providing counsel, when there is a similar Key Comparison in Appendix B.

Those who want to interpret, think about and justify results from multi-laboratory comparisons. Here, the toolkit computes, records, and prepares presentation tables and graphs - and the expert is free to have fun!

The Toolkit Pro can also deal with proficiency demonstrations effectively,but its real strength is in dealing with comparisons where the “right answer”is not easily apparent.

Ch 1: 9

Why such a Toolkit?As metrologists, how are we to cope with reviewing and discussing the many, many comparisons that are planned in support of the CIPM MRA?

We will be examining candidate KCRVs,tabulating MRA degrees of equivalence,and documenting decisions made forthe MRA Appendix C… Repetitive partsof all these tasks should be automated.

This toolkit is the authors’ responseto these challenges. It is offered in thebelief that others may find it useful.

Ch 1: 10

About the Present Toolkit

The Toolkit runs within Microsoft Excel to automate some of the rigorous analysis and presentation required for the international comparisons that are proliferating in metrology.

It calculates confidence, and confidence intervals, in the QDE (Quantified Demonstrated Equivalence) formalism. It works with Lab values and reference values (RVs) that may have unavoidable correlations. It deals with pooled distributions and outliers.(Wood and Douglas, Metrologia 35, 187-196 (1998)) (Steele, Wood and Douglas, Metrologia 38, 483-488 (2002)) (Hill, Steele and Douglas, Metrologia 38, accepted to appear (2002))

Even those who choose not to use the QDEformalism will like some other features of the Toolkit.

Presentation of comparisons in forms suitable for MRA submissions and decisions are included.Some functions are included that are not documented in the User’s Guide 2003-09-05, but rely on Excel’s documentation tools.

Ch 1: 11

What we see the Toolkit becoming

New releases of the Toolkit Pro have increased the rigor and variety of functions and macros. They more fully automate the work of calculating, interpreting and presenting comparison data in the useful formats that we have encountered.

As further techniques of analysis and presentation aredeveloped and/or become popular, we hope to automatethose as well.

We encourage you to suggest or contribute extensionsto this toolkit to make it better suit your needs.

Ch 1: 12

What are the Toolkit’s limitations?Limitations arise from trying to be universal and generic:

To be universal, and target all fields of measurement, it will remain a “post-processor” to be used after most of the analysis specific to a measurement field has been done.

To be generic (“…the measurand was…”) rather than specific (“… on … the measurement is predicted to be…”), the Toolkit Pro assumes that time dependences of the measurand have been removed and that it does not have to deal with time-dependent uncertainties.

Ch 1: 13

Toolkit Distribution

Rather than distributing the Toolkit and Guide yourself, please ask other potential users to register by sending an email to QDE@nrc-cnrc.gc.caBy registering with us, we can notify you of any errors, flaws or problems which have been identified.

Our website http://inms-ienm.nrc-cnrc.gc.ca/QDE maintains the revision history, known problems, FAQs, other documentation, papers and presentations.

We plan to coordinate Toolkit extensions to address other specific common needs and provide a forum for validation discussions.

Ch 1: 14

About the Toolkit User’s Guide

We are still in the process of improving and enhancing both the QDE Toolkit and this User’s Guide.

The QDE Toolkit Pro has been in use outside NRC since early 2002.

In order to avoid problems associated with having different versions in use, we ask you not to distribute this Toolkit or the Guide to others. We will provide the latest version of this software and documentation to anyone who registers.

This User’s Guide is available as a Microsoft PowerPoint (.ppt) file or as an Adobe Acrobat (.pdf) file. Because of the size of these files, individual Chapter files are also available for convenience in e-mailing and downloading.

Ch 1: 15