Users Guide to the QDE Toolkit Pro

1
Users Guide to the QDE Toolkit Pro
Ch 12 149
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 12. Automated Candidates for the
Reference Value
In this chapter we describe how to automatically
insert the Simple Mean of the pooled Labs as an
RV. The Median is discussed as an RV. The
insertion of a Weighted Mean of the pooled Labs
is also discussed, with its defaults
(inverse-squares of the standard uncertainties)
and the facilities for editing the weights. The
median and the simple and weighted means can be
automatically inserted with their effective
degrees of freedom and with their correlation
coefficients with respect to the contributing
Labs. The proper use (and potential abuse) of the
correlation coefficients is discussed.
2
Ch 12 150
QDE Toolkit Pro - adding a simple mean RV
We run the macro QDE2.xls!tk_RVisMean_AddToWorkshe
et to create a new reference value that is the
simple mean of the current in-pool labs. Note
an extra row has been inserted with the simple
means statistics, and rows beneath are shifted
down.

3
Ch 12 151
QDE Toolkit Pro - adding a Median RV
With some care in setting up the DLLs, we can run
the macro QDE2.xls!tk_RVisMedian_AddToWorksheet
to create a new reference value that is the
median of the current in-pool labs, and
evaluate variances and covariances with a FORTRAN
Monte Carlo DLL.

4
Ch 12 152
QDE Toolkit Pro - adding a Median RV
The macro QDE2.xls!tk_RVisMedian_AddToWorksheet
routinealso keeps track of the fraction of
resampled comparisons that have each Lab as the
median (for comparisons with an odd number of
participants), or part of the median (if the
number of participants is even).This is written
out on the worksheet TK Title of the active
workbook.

5
Ch 12 153
QDE Toolkit Pro - adding a Median RV
With version 2.07 of the QDE Toolkit Pro, a
second median macro joins QDE2.xls!tk_RVisMedian_A
ddToWorksheet . The new macro, QDE2.xls!tk_RVisMed
ianHAddToWorksheet , creates a new reference
value that is the median of the current in-pool
labs, and evaluates variances and covariances
with a FORTRAN Monte Carlo DLL that also handles
degrees of freedom and inter-Lab correlations.
In most other ways, the two macros are the same,
except that the new macro also generates Monte
Carlo histograms (hence the H)

6
QDE Toolkit Pro - adding a Median RV - Student
variate
Ch 12 154

• Start with a high quality linear congruent
  uniform random number generator
• Transform from uniform to Student probability
  density distribution 1 ((x-x0)/?)2 /
  n-(n1)/2 via its integral, the cumulative
  distribution
• Example shows a Monte Carlo histogram, recovering
  a Student distribution centred at -1, with
  standard uncertainty 2,and degrees of freedom 4.

7
QDE Toolkit Pro - Student variate and ISO Guide
Ch 12 155

• Student Cumulative Distribution Functions for
  different degrees of freedom (?1, n 210)
• Note that the line at 97.5 cumulative
  probability crosses each curve at the coverage
  factor, k, appropriate for a 95 confidence
  interval the Student random variate is the basis
  for coverage factors recommended by the ISO Guide
  to the Expression of Uncertainty in Measurement

8
Ch 12 156
QDE Toolkit Pro - adding a Median RV
The new macro, QDE2.xls!tk_RVisMedianHAddToWorkshe
et , also produces the histogram of the median
distribution as determined by the Monte Carlo
resampling. It is plotted on a new worksheet,
named Histograms, that is added to the active
Excel workbook.

Also plotted are the histograms for the simple
mean, the inverse variance weighted mean, and the
Average Reference Value (or ARV) histogram the
average of the first three methods RVs. The
pooled resampling of measurements, and pair
differences, of all Labs from each comparison
also have their histograms presented. The
histograms range is centred on the weighted
mean, and they are all plotted without shift
except the all-pairs difference which is exactly
symmetric about zero, but is plotted here to be
symmetric about the weighted mean.
9
Ch 12 157
QDE Toolkit Pro - adding a Median RV
We can run the macro QDE2.xls!tk_RVisMedian_AddToW
orksheet to create a new reference value that is
the median of the current in-pool labs, and
evaluate variances and covariances with a FORTRAN
Monte Carlo DLL.

This macro uses Student variates, with degrees of
freedom read from column D, and converted to the
(smaller) degrees of freedom for the independent
random variate using the Welch-Satterthwaite
approximation. The inter-Lab correlation
coefficients are read from the upper triangle of
the square matrix (in green), to obtain the
parameters of the normal distribution of the
covariant random variates, Remember that the
Toolkit can automatically set up Column D and the
correlation coefficient matrix to the usual
defaults of normal and uncorrelated.
10
Ch 12 158
QDE Toolkit Pro - adding a Median RV
A Comment in Column A tells which Labs were used
to calculate the instance median given in Column
B. The Monte Carlo calculation of the square root
of the variance (from the instance median given
in Column B) is given in Column C as the
within-method uncertainty.

Recall that this variance is larger than the
variance about the mean of the distribution of
medians, by the square of the difference between
them. The mean of the median distribution is
given in a Comment in Column B. The median of the
means is not quite the same as the mean of the
medians!
For an odd number of non-outlier Labs, the median
is taken as the central value (sorted by the VBA
subprogram Sorti) For an even number of
non-outlier Labs, the median is the mean of the
two central values.
11
Ch 12 159
QDE Toolkit Pro - normal approximation for the
Median RV
The use of the calculated variance and
covariances in this way leads to a normal
approximation. In fact the covariant
distributions have subtleties beyond the general
bivariate normal distribution...

12
Ch 12 160
QDE Toolkit Pro - normal approximation for the
Median RV
The covariant distributions have subtleties
beyond the general bivariate normal distribution
there is the obvious blade covariance (note the
different slopes), and a more subtle intercepting
covariance this kind of phrenology might be fun,
but usually the correlation coefficients main
role will be to quantify the unimportance of the
correlations, and detailed shapes are not crucial.

13
Ch 12 161
QDE Toolkit Pro better covariances
QDE2.xls!tk_RVisMedianHAddToWorksheet is improved
from QDE2.xls!tk_RVisMedian_AddToWorksheet in
another subtle way The new routine uses the
first part of its run (10) to determine a better
estimate for the mean of the resampled median
distribution, and uses this improved estimate for
the calculation of the covariances using the
remaining 90 of therun. The new routine uses
large lookup Tables (10000 values per random
variate, total memory allocation of some 20MB) to
give rapid random number generation that give
Student random variates with the appropriate
degrees of freedom (which does not have to be an
integer), or a normal random variate if the
degrees of freedom is greater than 40000 (or less
than 0.7). With a modern (2003) computer, 107
resamples of the comparison can often be done in
10-100 seconds. In the VBA module, the number of
resamples, NPTS, is set to 107 but can be edited
to smaller or larger values as desired, but the
number of random variates generated should remain
less than 4x109.

14
Ch 12 162
QDE Toolkit Pro - Monte Carlo setup for Median RV

The variance and covariance calculations, in
either of these macros, are performed by a Monte
Carlo subprogram, written in FORTRAN and compiled
into a DLL (Dynamic Linked Library) that is
called by the Excel macros VBA code. It is set
up to do NPTS106 or 107 random sets of data,
which will usually take less than a minute or so
on most modern Win32 computers. The random number
generator is seeded by the tic (1/18 s) of day,
so that repeat runs can easily tell you about the
precision of the Monte Carlo averages. The
pseudo-random number generator sequence is
limited by the 32-bit integers used - usually 108
repeats is a limit safe from wrap-around for NPTS
in the VBA call to MEDIANcalc. The Excel macro
module containing tk_RVisMedian_AddToWorksheet
has to find the files MCMedian.dll (the Monte
Carlo routines), DFORRT.DLL, DFORMD.DLL and
MSVCRT.DLL (three Visual FORTRAN Run-Time DLLs
that Compaq (now HP) allows to be distributed
freely, called by the Monte Carlo code). If you
dont do this, the only problem created is that
this one macro will not run. Similarly, the Excel
macro module containing tk_RVisMedianHAddToWorkshe
et has to find the files MCMedianH.dll (the
Monte Carlo routines), DFORRT.DLL, DFORMD.DLL and
MSVCRT.DLL (three Visual FORTRAN Run-Time DLLs
that Compaq (now HP) allows to be distributed
freely, called by the Monte Carlo code). If you
dont do this, the only problem created is that
this one macro will not run.

15
Ch 12 163
QDE Toolkit Pro - Monte Carlo setup for Median RV

The easiest setup is to run the self-extracting
Zip archive QDE2.exe. It will create a directory
C\QDE2\, and place these DLL files there
(MCMedian.dll, MCMedian.dll, DFORRT.DLL ,
DFORMD.DLL and MSVCRT.DLL ). An alternative is
to manually extract these files from the Zip
archive QDE2.zip. Again the simplest place to put
these is C\QDE2\... The VBA code near the top
of module QDE_Toolkit_RVs specifies the directory
where Excel will expect to find them you can
edit this to any other directory Declare Sub
MEDIANcalc Lib "C\QDE2\MCMedian.dll" _ (D1 As
Single, u1 As Single, NLabs1 As Long, NPTS1 As
Long, idum1 As Long, vMed1 As Single, uMed1 As
Single, Fmed1 As Single, RHO1 As Single) ' The
above must point to the Monte Carlo DLL, with the
run-time DLLs DFORRT.DLL, DFORMD.DLL and
MSVCRT.DLL Declare Sub DISTcalc Lib "C\QDE2\
MCMedianH.dll" _ (D1 As Single, u1 As Single,
dof1 As Single, rho1 As Single, deltaX1 As
Single, NLabs1 As Long, NPTS1 As Long, idum1 As
Long, vMed1 As Single, uMed1 As Single, Fmed1 As
Single, rhoLabMedian1 As Single, iPDF1 As
Long) 'The above must point to the Monte Carlo
DLL, with the FORTRAN run-time DLLs DFORRT.DLL,
DFORMD.DLL and MSVCRT.DLL The other .DLLs should
be in the same directory, or else in some other
path that is searched. http//h18009.www1.hp.com/f
ortran/visual/ now has the latest version of the
Visual Fortran Redistributables kit, an installer
program that can prepare your system to run our
Fortran DLLs, installing and registering
DFORRT.DLL, DFORMD.DLL and MSVCRT.DLL.

16
Ch 12 164
QDE Toolkit Pro - error 53 or 48 with Median
RV
If the Excel macro module containing
tk_RVisMedian_AddToWorksheet cannot find the all
files it needs (MCMedian.dll - the Monte Carlo
routines, DFORRT.DLL, DFORMD.DLL and MSVCRT.DLL)
it will give you one of these cryptic error
messages when you try to run this one macro, and
tk_RVisMedianHAddToWorksheet.will do the same
(except it needs MCMedianH.dll rather than
MCMedian.dll). If the easiest setup does not
work on your computer (presumably because
incompatible versions of the same-name DLLs are
preempting the search order we want to use), you
should try downloading and running the program
VFRUN66BI.exe (Visual Fortran Run-time version
6.6) from http//h18009.www1.hp.com/fortran/visual
/ . This is an installer program designed to cope
with the subtleties of Windows DLLs as it
prepares your system to run our Fortran DLL. It
will step through several screens and tell you
which files it wants to load. In our limited
experience to date, the macro can usually be
connected with all its DLLs after a bit of
fiddling around (see the Tip on the following
page). Neither the simple method nor this
installer always works by itself, but in
combination they have worked on even quite
cluttered systems that we have tried (Windows 95,
Windows 98, Windows 98 SE, Windows NT 4.0,
Windows 2000 Windows XP).

17
Ch 12 165
QDE Toolkit Pro - error 53 or 48 with Median
RV

• Tip If you get Visual Basic Error 53 File not
  found MCMedian.dll when you try to run the
  Excel QDE Toolkit Pro macro tk_RVisMedian_AddToWor
  ksheet, and if QDE2.xls, MCMedian.dll,
  DFORRT.DLL, DFORMD.DLL and MSVCRT.DLL are all in
  C\QDE2, try this
• close all Excel workbooks
• re-open C\QDE2\QDE2.xls in Excel from the Open
  menu (or Control O) and not from the list of
  recently used Excel files, and then
• from QDE2.xls run one VBA macro (such as
  tk_RVisMean_AddToWorksheet)
• from QDE2.xls run the macro tk_RVisMedian_AddToWo
  rksheet. The .dlls will likely all be found and
  subsequently the macro can be invoked from this
  or any other workbook as long as this .dll
  association lasts (typically as long as this
  instance of Excel keeps open this copy of the
  file QDE2.xls).If you find a better
  workaround, please let us know!

18
Ch 12 166
QDE Toolkit Pro - adding a weighted mean RV
We run the macro QDE2.xls!tk_RVisWtMean_AddToWorks
heet to create a new reference value that is the
weighted mean of the current in-pool Labs.
Note again an extra row has been inserted, with
the weighted means statistics weights default
to inverse-square standard uncertainties for each
Lab. The weights (on worksheet TK Title) are
editable...

19
Ch 12 167
QDE Toolkit Pro - editing a weighted mean RV
On worksheet TK Title are recorded the weights
for the most recent simple and weighted mean for
a particular worksheet - a separate table is kept
for each worksheet. N.B. This is the block for
worksheet 1000-10. The relative weights
column of the weighted mean is editable (note the
double underline is a reminder of editability)

20
Ch 12 168
QDE Toolkit Pro - editing a weighted mean RV
Here, we have just edited therelative weights
column of the weighted mean in this case edited
so that the weights of the three largest weights
have been equalized. In this example, we switch
back to worksheet 1000-100 (in a large
workbook, some care is needed, since TK Title
can have many tables one for each worksheet
using pooling) and re-run the macro
QDE2.xls!tk_RVisWtMean_AddToWorksheet the
Weight column will update, and a new row will be
added to worksheet 1000-100.

21
Ch 12 169
QDE Toolkit Pro - another Weighted Mean RV
Oops! We now have two RVs with the same name.
Unlike the case for a redundant Lab name, this
is not a critical problem. The comment field even
identifies how each was obtained. However, the
Equivalence Tables and Graphs we create from now
on will have ambiguous labels, so we really
should delete one row (select entire row (the row
number), EditDelete) or, rename one or both RVs
now! of course we could have dealt with this
before re-running the macro.
22
Ch 12 170
QDE Toolkit Pro - correlations between Labs and
an RV
For the candidate RVs (a mean or weighted mean
of the Lab values), the QDE Toolkit Pro also
determines the determines uncertainty, degrees of
freedom and the correlation coefficients that are
required for evaluating the pair uncertainties of
(Labi - RV) using u2(Labi - RV) u2(Labi)
u2(RV) - 2 ri,RV u(Labi) u(RV) this form is
really required for judging the goodness-of-fit
of the claimed uncertainties in any En,
chi-square or APV test. However, this same
uncertainty can have the misleading property of
an uncertainty, relative to the KCRV, that is
less than the uncertainty of the Lab with respect
to the SI. Depending on the possibility of later
realizing this same KCRV, this shrunken
uncertainty could be imbued with different
proportions of uselessness, incompleteness and
untruth. We recommend that it be used with
extreme caution.
23
Ch 12 171
QDE Toolkit Pro - covariances between Labs and an
RV
Warning a covariance estimate is often invariant
with respect to changes of estimated RV variance
the correlation coefficient is not
invariant. Suppose the KCRV is a simple mean of N
participating Labs. The within-method uncertainty
in the simple mean can be calculated from the Lab
uncertainties and correlation coefficients. If
the Labs uncertainties are independent, then the
covariance of the simple mean with Labi is
ui2 / N with correlation coefficient ui /
(u(KCRV) N) If the within-method uncertainty of
this KCRV is to be replaced by the experimental
standard deviation of the mean, u(KCRV), then
the already-identified covariance is still just
the within-method covariance (the correlation
properties of the other terms are unknown -
remember the x0 and y0 in the covariance
lt(x-x0)(y-y0)gt) ui2 / N but the correlation
coefficient has changed to ui / (u(KCRV) N)
24
Ch 12 172
QDE Toolkit Pro - philosophical reflections on
RVs

• If an RV is easily accessible, like UTC for time
  and frequency metrology, it can be a very useful
  addition to a field of metrology. If an RV is not
  accessible, so that comments about it are not
  testable by measurement, we believe that this RV
  should not be portrayed as part of measurement
  science.
• Any RV created from the values of N Labs in a
  comparison is not independent of the Lab values,
  and practical problems can arise when any one Lab
  has a substantial weight (30).
• A KCRV may be a useful artifice to simplify
  disseminating confidence to the widest audience,
  demanding from them the least new thinking and
  sophistication.

25
Ch 12 173
QDE Toolkit Pro - addressing some difficulties
with RVs

• There is a degree of arbitrariness in the
  selection of a KCRV as a good but not
  necessarily the best representation of the SI
  value.
• Agreement with the arbitrary KCRV can create or
  suppress a finding that a particular Lab has a
  significant unresolved difference from the
  KCRV.
• It is not surprising that this fraction (which
  should be at least 5 even if everything is
  perfect) of metrologists will argue
  energetically when they feel their careers are
  threatened by an arbitrarily chosen KCRV.
• The QDE Toolkit Pro provides some tools that do
  not depend on the choice of a KCRV, which may be
  helpful in these intense deliberations.

26
Ch 12 174
QDE Toolkit Pro - choices for KCRVs and u(KCRV)s

• The KCRV is a good but not necessarily the
  best representation of the SI value ideally it
  would be a weighted average of the Labs with
  definition-based primary standards.
• If there is an enduring SI value that will be
  accessible in the future, the KCRV can play an
  active role in future metrology. Otherwise, CCs
  might decide to use no KCRV (eg CCT K-3),
  particularly if the participants values do not
  seem to be drawn from a simple population.
• If there is no enduring value that will be
  accessible, then the CC will have to decide on a
  meaning and an unverifiable value for the
  uncertainty of the KCRV. In considering the uses
  to which the u(KCRV) will be put, the CC may
  decide to assign no u(KCRV) (eg CCT K2, K4).
  This u(KCRV)0 can be handled by the QDE
  Toolkit Pro, and is attractive when a non-zero
  value could convey more mis-information than
  zero!

27
Ch 12 175
QDE Toolkit Pro - choices for u(KCRV)s

• The standard uncertainty of the KCRV is to
  reflect the range over which the value could
  reasonably be expected to be found.
• The above can be rephrased we must include all
  known effects that can affect the KCRV it can
  only be excluded if including it is unreasonable.
  
• The QDE Toolkit Professional Version 2.07 does
  quite a good job of preparing the within method
  uncertainties for the most common indicators of
  central tendency. When the within-method
  histograms are in good agreement with each other,
  all is well. When they do not agree well, it
  seems to us to be necessary to include a between
  methods uncertainty including all reasonable
  methods and again the QDE toolkit can help.

28
Ch 12 176
QDE Toolkit Pro - choices for u(KCRV)s

• The QDE Toolkit Professional Version 2.07 does
  quite a good job of keeping track of manually
  selected outliers, but does not evaluate the
  consequences, on u(KCRV), of any particular
  outlier rejection scheme.
• The Monte Carlo method could track these
  consequences for any outlier rejection scheme
  that is algorithmic (one that can be programmed
  for automatic execution the median is an
  extreme example of outlier rejection it can
  reject all but the median Lab!)
• If the preceeding slides interpretation of
  accounting for all reasonable variation, then
  considering equivocal outlier-rejections will not
  appreciably reduce u(KCRV) but could greatly
  increase the work that is required.