Users Guide to the QDE Toolkit Excel Tools for Presenting Metrological Comparisons by B.M. Wood, R.J

1
Users Guide to the QDE Toolkit Excel Tools for
Presenting Metrological Comparisons byB.M.
Wood, R.J. Douglas A.G. Steele Guide Version
1.14 for QDE Toolkit Version 1.14 October 2000
National Research Conseil national Council
Canada de recherches
2
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 for the MRA Appendix C Repetitive
parts of all these tasks should be automated.
This toolkit is the authors response to these
challenges. It is offered in the belief that
others may find it useful.
3
About the Present Toolkit
The Toolkit runs from within Microsoft Excel to
automate some of the analysis and presentation
required for the international comparisons that
are proliferating in metrology. It includes the
calculation of confidence, and of confidence
intervals, in the Quantified Demonstrated
Equivalence (QDE) formalism. (Wood and Douglas,
Metrologia 35, 187-196 (1998)) Even those who
choose not to use the confidence interval
formalism may like some other features of the
Toolkit. Presentation of comparisons in forms
suitable for MRA submissions and decisions are
included. Some functions are available that are
not fully documented in the Users Guide
2000-10-05.
4
What we see the Toolkit becoming
So far the toolkit contains a variety of
functions and macros to automate much of the work
for calculating, interpreting and presenting
comparison data in the formats that we have
encountered. As other researchers develop
different analysis techniques and presentation
styles, our intention is to automate those as
well. We encourage you to suggest or contribute
extensions to this toolkit to make it better suit
your needs.
5
Toolkit Distribution
Rather than distributing the Toolkit and Guide
yourself, please ask other potential users to
register by sending an email to QDE_at_NRC.CA By
registering with us, your colleague can obtain
the latest version of the Toolkit and its Users
Guide (this document). Perhaps most importantly,
we can notify registered users of any errors,
flaws or problems which have been identified. A
website is planned detailing revision history,
known problems, FAQs, other documentation, papers
and perhaps a discussion group. We plan to
coordinate Toolkit extensions to address other
specific common needs and provide a forum for
validation discussions.
6
About the Toolkit Users Guide
We are still in the process of improving and
enhancing both the QDE Toolkit and this Users
Guide. We feel that the toolkit is ready for
use outside NRC. 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 Users Guide is
available as a Microsoft PowerPoint (.ppt) file
or as an Adobe Acrobat (.pdf) file.
7
Using the Toolkit
Only For Excel Experts? This Users Guide is not
intended to be an introduction in the use of
Excel. You should be familiar with Excel basics
navigating, entering and editing data,
formatting, cutting and pasting, annotating and
graphing. This Guide to the Toolkit is intended
to be quite complete, even at the risk of being
verbose. We particularly appreciate feedback on
points that need better explanations.
Okay, the background and introduction is
overlets get on with explaining what youve
just downloaded, and how you can use it to
simplify your tasks.
8
What is the QDE Toolkit?
The QDE Toolkit is a collection of MS Excel
functions and macros written to perform various
calculations common to most metrological
comparisons. QDE Toolkit functions return a
single value, while the macros generate more
involved output such as an entire table. The QDE
Toolkit functions and macros are made available
by including Visual Basic Modules in an Excel
Workbook. Visual Basic programmers can add other
functions and macros to the QDE Toolkit, using
the supplied Visual Basic Modules as a
template. The QDE Toolkit is already included
within the example Excel Workbook. The Workbook
has some test data so that you can see how the
data should be organized and so that you can gain
experience by testing out the functions and
macros.
9
What is in the QDE Toolkit?
The QDE Toolkit includes a variety of statistical
functions, such as a weighted mean and functions
for calculating uncertainties. Weighted means can
be used to explore a wide variety of KCRVs, since
the weights can be manually adjusted to exclude
outliers or impose cutoff limits. These
functions work now but are not yet documented.
Of course, the Toolkit also includes functions
for evaluating QDE0.95, with and without
correlations, as well as other confidence
intervals (such as QDE0.68) and demonstrated
confidence (QDC). The Toolkit macros
automatically generate equivalence tables from
lists of laboratory results of deviations and
uncertainties. The use of these macros will be
explained and demonstrated in this Users Guide.
10
Selection of KCRV UKCRV
Different CCs have tried the Mean, Weighted Mean
and Median for the KCRV, with and without Weight
Limits and Selective Exclusion. A clear
definition of both KCRV and UKCRV simplifies the
statistics. For example, the CCEM has used the
mean of the travelling artifacts as determined by
the weighted mean of the values from laboratories
using independent realizations of the SI the
weight given by (1/Ulab)2 and UKCRV given by the
weighted uncertainty. Whatever the definition,
it must be agreed upon by the participants and
the CC. The description must also be sufficiently
detailed to allow subsequent recalculation from
the Appendix B data.
11
Functions for KCRV UKCRV
Try out the various statistical functions of the
toolkit to explore the consequences of different
choices for the KCRV. Select a particular
worksheet cell, then Paste Function. Function
category is User Defined, where several
specialized routines appear in the Function Name
list. Further documentation to follow!
12
Tables of Equivalence - Input Data
After the KCRV and its uncertainty have been
chosen, the table detailing the bilateral degrees
of equivalence can be created. The input data
for this table has three pieces of information
for each laboratory name (or acronym), deviation
from the KCRV, and claimed uncertainty. We
recommend that the KCRV be entered as a separate
item, preferably at the end of the list, with a
deviation of zero and the KCRV uncertainty, if
any. The input data must be listed in columns
A,B, and C in an Excel worksheet the first row
of each column must be used to label the data
Name, Deviation, Uncertainty.
13
Tables of Equivalence - Input Data
The input data must be listed in columns A,B, and
C in an Excel spreadsheet the first row of each
column must be used to label the data Name,
Deviation, Uncertainty.
put column titles in the first row
put comparison data in the next rows
leave a blank row to denote the end of the data
put lab names in first column, deviation in
second column, uncertainty in third column
14
Tables of Equivalence - Input Constraints
The first row must contain the column labels (A1,
B1, C1). The data must start in the next row (A2,
B2, C2). Toolkit macros adapt to any number of
laboratories, and the first blank row indicates
the end of the data list. The KCRV need not be a
separate entry but we recommend it always be
entered as a virtual lab, preferably as the
last row of the data set. Multiple candidate
KCRVs can be included, listing them as the last
few rows. The uncertainties are listed as
standard (k1) uncertainties as recommended by
the MRA. The uncertainties are assumed to be
uncorrelated. We defer treating correlations
until later.
15
Tables of Equivalence - Run the Macro
To run one of the table of equivalence macros,
begin by selecting a cell corresponding to the
upper left hand corner of the equivalence table
to be created. Select the Tools menu, which drops
down. In the drop down menu, select Macro, which
cascades. In the cascaded menu, select Macros,
which opens a dialog box containing the available
macros for your worksheet. The next slides show
what this looks like in Excel.
16
Tables of Equivalence - Run the Macro
Select the top left anchor cell for table output
Run the macro from the Tools drop-down menu list
17
Tables of Equivalence - Run the Macro
Select the macro from the list...
then Run it
18
Tables of Equivalence - Run the Macro
The table is anchored with the top left corner
in the cell which you selected
The table output format is determined by the
input data format
The table extends beyond what is shown, and
includes all of the 10x10 bilateral degrees of
equivalence for this example data set of nine
labs KCRV
19
Tables of Equivalence - The Output
The tables can be formatted differently after
they have been generated by the macro. These
formatting features include font name, font size,
font color, number of displayed digits, etc. The
table can be cut and pasted into other
documents. To change the order of the
laboratories, to add other laboratories or to
change the input values the macro must be run
again. Simply select the output anchor cell, and
repeat the steps to run the macro. Note some
tables of strings require re-running the macro to
modify the number of digits displayed.
20
Tables of Equivalence - The Macros

• The Toolkit includes the following macros
• tk_mraCCQM_TableBuilder
• CCQM format for MRA Degree of Equivalence
• tk_mraDOE_TableBuilder
• MRA Degree of Equivalence (DOE) with D ? UD
  formatting
• tk_mraDOEandQDE_TableBuilder
• DOE and QDE0.95 95 confidence interval in one
  table
• tk_mraQDC_MatrixBuilder
• Demonstrated confidence for agreement within ?
  Ulab (k2)

21
Tables of Equivalence - CCQM Format
The Toolkit includes tk_mraCCQM_TableBuilder I
t 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 (k2)
uncertainties in the second cell. The choice of
the particular cell bolding and cell outlines
reflect our understanding of their most recently
accepted format.
22
Tables of Equivalence - CCQM Format
tk_mraCCQM_TableBuilder output is row-column
difference and (U12U22)0.5 as values in each
double element of the table
23
Tables of Equivalence - Formatting
The format of each column of the input data is
reflected in the equivalence tables. In
particular, the number of decimal places used for
the input data differences and for the
uncertainties is retained in the equivalence
tables. This is also true for the font typeface
and size. Individual or groups of table cells can
still be formatted using Excels standard
features which allow you to change cell height
and width, colours, borders, etc. Different
tables can be created on the same sheet. Simply
select a new output anchor cell and run a
different macro.
24
Tables of Equivalence - Different Data Sets
The macro works in the same fashion with other
data sets, or with alternate KCRV choices. Simply
paste the new data (in this case electrical) into
the first three columns of a worksheet and run
the tk_mraCCQM_TableBuilder macro to create the
output.
25
Tables of Equivalence - CCQM format
Changing the input data is easy...
and so is generating a new table
26
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 in a more standard
  fashion for physical measurements, (D UD),
• the single parameter QDE0.95 description, and
• the single probability parameter, quantified
  demonstrated confidence for agreement in a
  specified interval, QDC.
• ReferenceWood and Douglas, Metrologia 35,
  187-196 (1998) This paper covers the essentials
  of probability calculus for computing confidence
  in an interval centred on the concept of
  equivalence.

27
MRA Degree of Equivalence Table
The Toolkit includes tk_mraDOE_TableBuilder
creates a table row-column difference
(U12U22)0.5 as a string in each element of the
table
28
Macros tk_mraDOE_TableBuilder
tk_mraDOE_TableBuilder output is row-column
difference (U12U22)0.5 as a string in each
element of the table
29
Degree of Equivalence and QDE0.95
The Toolkit includes tk_mraDOEandQDE_TableBuild
er creates a table row-column difference
(U12U22)0.5 as a string in above diagonal
elements of the table and the 95 confidence
interval for agreement, QDE0.95, in the below
diagonal elements
30
Macros tk_mraDOEandQDE_TableBuilder
tk_mraDOEandQDE_TableBuilder output is
row-column difference (U12U22)0.5 as a string
in above diagonal elements and QDE0.95 in below
diagonal elements
31
Demonstrated Confidence within ?Ulab
The Toolkit includes tk_mraQDC_TableBuilder cr
eates a table of probability for agreement of the
column lab value with the row lab value within
the confidence interval created by the claimed
(k2) uncertainty of the row lab, ? Ulab, and
the pair uncertainty. This macro is still under
development.
32
Macros tk_mraQDC_TableBuilder
tk_mraQDC_TableBuilder output is the QDC
probability for agreement of the column lab value
with the row lab value within the confidence
interval created by the claimed (k2) uncertainty
of the row lab and the pair uncertainty.
33
Hints and tips
Each data set can be summarized using each of the
available table builder macros, to provide you
with the maximum amount of information and
flexibility. Different tables can be created on
the same sheet. Simply select a new output
anchor cell and run a different macro.
34
Multiple Tables on a Single Page
Running each of the table builder macros on a
single page provides maximum information
regarding your data set. Impressive!
35
Comments and Other Resources
To read more about QDE and and its application to
the interpretation of comparison results we are
making various publications (in pdf format) and
several presentations (PowerPoint and pdf format)
available. Further documentation of the table
builder macros and the other statistical
functions is expected soon. More complicated
examples with different types of correlations
should also be available soon.
36
Correlations
A great deal has and will be written about
correlations as it concerns comparison results.
The implication of correlations on the use of the
table builder macros is surprisingly little.
You must identify the correlation effects. You
must state how the effects are to be accounted
for between laboratories and between one
laboratory and the KCRV. If the effect is fully
correlated in all labs then it is easily
incorporated into the input data. Known
correlations between pairs of laboratories is
also addressable but its macro is not yet ready
for distribution. We are not providing automated
tools for identifying and assessing correlation
effectsthat is still your job!
37
Need More Information?
For more information please do not hesitate to
contact us. QDE Mailing List qde_at_nrc.ca Barry
Wood barry.wood_at_nrc.ca Rob Douglas rob.douglas_at_
nrc.ca Alan Steele alan.steele_at_nrc.ca We intend
to maintain a single distribution list of people
using the Toolkit. Send us your email address
and we will make sure you are kept up to date
with corrections, enhancements, and supporting
documentation.