Users Guide to the QDE Toolkit Pro

1
Users Guide to the QDE Toolkit Pro
Ch 216
Sept 5, 2003
National Research Conseil national Council
Canada de recherches
Excel Tools for Presenting Metrological
Comparisons byB.M. Wood, R.J. Douglas A.G.
Steele
Chapter 2. Before You Start, Getting Started
This chapter discusses prerequisites for getting
started, andhow to get started with the QDE
Toolkit Pro.
2
Ch 2 17
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 Pro is
intended to become 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 the Toolkit
Pro, and how you can use it to simplify your
tasks.
3
Ch 2 18
Excel checklist for QDE Toolkit Pro
Excel Skills helpful for V2.05 V2.05 uses a
separate Excel workbook for each comparison, with
at least two worksheets. Navigating different
worksheets finding, selecting, creating,
renaming and deleting them are all useful
skills. V2.05 can make graphs that you will want
to manipulate. Changing axis ranges, colors,
linewidths are more important skills, as is
knowing how to freeze a graph with many data
points into a permanent picture. V2.05 adds
comments to many, many cells (hundreds of them),
that are intended to pop up when your mouse
hovers over that cell. Excel can force all these
comments to appear simultaneously and you will
want to know how to undo this feature quickly
(from the Excel pulldown lists, select View
Comments).
The QDE Toolkit Pro V2.07 (and subsequent
versions) expects the user to be familiar with a
few more elements of Excel than did V1.14.
4
Ch 2 19
What is the QDE Toolkit?
The QDE Toolkit is a collection of Microsoft
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 or graph.
Only its macros are explained in this Guide. The
QDE Toolkit Pro functions and macros are made
available by including five Visual Basic Modules
in an Excel Workbook. Two optional macros, which
evaluate Median variances, use FORTRAN DLLs that
must be set up on each computer. The QDE Toolkit
Pro is already included within the example Excel
Workbook, QDE2.xls. The Workbook has 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. As long as QDE2.xls
is open in your Excel application, you can use
its capabilities in your other open
workbooks. Visual Basic programmers can add other
functions and macros to the QDE Toolkit, using
the supplied Visual Basic Modules as a template.
5
Ch 2 20
What do you need to run the QDE Toolkit Pro?
q A computer that runs Microsoft Excel(Excel 95
might work if the .xls file were back-converted
to Excel 95 format, Excel 97 has been tested
extensively, Excel 2000 and 2002 (Excel XP) a bit
less. Microsoft Excel for Apple computers has not
been tested, the Monte Carlo median DLLs are for
Win32 only.) q A color display we recommend
least a 1024x768 display. q Specific Fonts
Arial, Tahoma, Times New Roman, Symbol, in
addition to any that you wish to use. q The QDE
Toolkit Pro example file QDE2.xls. When you open
this in Excel, all the toolkit functions and
macros are available to you in all other open
Excel workbooks. To use the Monte Carlo modules,
MCMedianH.dll MCMedian.dll and DFORRT.DLL,
DFORMD.DLL and MSVCRT.DLL should be in directory
C\QDE2\ on your computer (Win32). The
self-extracting zip file QDE2.exe can do this
setup. You may also have to download and run the
FORTRAN run-time setup VFrun66BI.exe from
http//h18009.www1.hp.com/fortran/visual/ . q You
will probably also want this Guide to be loaded
on your computer. It is available as a Powerpoint
.ppt file, or as an Acrobat .pdf file. Because of
its size (gt20 MB for .ppt), individual chapter
files are also available.
6
Ch 2 21
What is in the QDE Toolkit?
q Excel Functions 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 in
this User Guide, other than their Excel hints,
and the comments in the VBA source code. q
Excel Macros QDE Toolkit macros automatically
generate equivalence tables or graphs from lists
of laboratory results of deviations and
uncertainties. The use of these macros will be
explained and demonstrated in this Users Guide.
7
Ch 2 22
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 can simplify
statistical work such as evaluating
rlab,KCRV. E.g. 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, CCEM. Whatever the definition, we
think 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. The
Toolkit Pro can manipulate multiple candidate
KCRVs with ease, and has analysis tools that work
as well without a KCRV as with a KCRV.
8
Ch 2 23
Functions for KCRV UKCRV (Excel 97)
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. A short explanation of each function
appears when it is selected on the Function
name list.
9
Ch 2 24
Functions for KCRV UKCRV (Excel XP)
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 Insert Function. Function
category is User Defined, where several
specialized routines appear in the Function Name
list. A short explanation of each function
appears when it is selected on the Select a
function list.
10
Ch 2 25
New Excel Functions for QDE Toolkit Pro
New Excel Functions in QDE Toolkit Pro Version
2.07(that were not included in the Version 1.14
Toolkit) Welch-Satterthwaite combination of
effective degrees of freedom for a pair
uncertainty (with and without correlations). Cove
rage factor evaluation for an effective degrees
of freedom that is not necessarily an integer
(numerically integrated for degrees of freedom
gt0.5). Confidence calculation in an interval
centered on the expectation of equivalence. Estim
ators for the effective degrees of freedom that
are aimed at estimating a coverage factor for a
95 confidence interval. The traditional
variance-based estimators are provided, as well
as an improved version that can harness expert
opinion about the correct inverse-chi
distribution that underlies the Student
distributions.
11
Ch 2 26
New Excel Macros for QDE Toolkit Pro
Some Excel Macros in the QDE Toolkit Pro Version
2.07 were not in the Version 1.14 Toolkit.Their
use is illustrated in Chapters 4-13 of the Users
Guide. Version 1.14 Macros were all updated to
handle degrees of freedom and inter-laboratory
correlation coefficients Distribution Plotting -
plots Lab and RV PDFs and pools of PDFs RV and
Outlier handling Macros are available. Monte
Carlo simulation is used for describing the
properties of the median RV. Macros can create a
Lab View of the comparison, either retaining
the uncertainty of the reference Lab, or
attributing it rigorously to the uncertainty in
the difference between Labs.
12
Ch 2 27
New Excel Macro for QDE Toolkit Pro
One Excel Macro is new in the QDE Toolkit Pro
Version 2.07 it was not in the Version 2.06
Toolkit. Its Monte Carlo simulation now handles
Student variates and correlations rigorously for
describing the properties of the median RV. It
also plots histograms of the Monte Carlo
resamples of the median, the simple mean, the
inverse-variance weighted mean and the ARV (the
average of the other three reference values).
The resampled Monte Carlo remeasurements from
all Labs are pooled in another histogram, and all
the pair differences from each resampled
comparison are also presented in a histogram.
13
Ch 2 28
Getting Started with QDE Toolkit Pro Files
The starting files are QDE2.zip (1 MB) unzipped
with WinZip (for example) to give QDE2.xls (1.3
MB) and the DLLs. QDE2.exe is a self-extracting
zip that can place these in C\QDE2\. QDE2.xls
runs with Excel to give access to the functions
and macros of the QDE Toolkit Pro. It should be
placed in, and run from, C\QDE2\MCMedian.dll
and MCMedianH.dll are Monte Carlo modules to find
the properties of the median RV. These compiled
FORTRAN DLLs should be in C\QDE2\ DFORRT.DLL,
DFORMD.DLL and MSVCRT.DLL are redistributable
FORTRAN Run-time DLLs for Compaq (now HP) Visual
Fortran. (Install the latest from
http//h18009.www1.hp.com/fortran/visual/). They
should also be in C\QDE2\ QDE2_Guide.ppt (20
MB) viewed with Microsoft Powerpoint QDE2_Guide.p
df (10 MB) viewed with Adobe Acrobat Chapter
files of QDE2_Guide.ppt are also available
14
Ch 2 29
Getting Started with QDE Toolkit Pro Simple
Steps
You can unzip QDE2.zip to get Excel file QDE2.xls
and the DLLs MCMedian.dll, MCMedianH.dll,
DFORRT.DLL , DFORMD.DLL and MSVCRT.DLL. To
evaluate median RVs, you should have these Monte
Carlo DLLs in C\QDE2\ 1). Start Excel 2). In
Excel, Open QDE2.xls - Excel will warn you that
the workbook uses macros. You must Enable
Macros to be able to run the Toolkit. We believe
it to be virus-free, but we still recommend that
you check for viruses. 3). Open a new workbook,
name one of the worksheets, and enter your
Comparison Data Starting in Row 2, put the Lab
name in Col A, the difference from some
reference in Col B, and the standard uncertainty
in Col C. Optionally, degrees of freedom may be
given in Col D, and the square array of
correlation coefficients may be given after
that. 4). In Excel, to run a Table Builder
macro, first select the anchor (top left) cell
for the Table. Run macros by ToolsMacroMacros,
select the desired macro, click Run.Follow
instructions about entering title, units,
outliers and Run again. Plotting macros do not
need an anchor cell. Other macros use the
selected cell on input to choose a particular Lab
for action (promotion to an RV, or to change
outlier status). (Pictures of Step 4 are in
the next Chapter.)