1 / 28

User’s Guide to the ‘QDE Toolkit Pro’

Ch 12: 149. User’s Guide to the ‘QDE Toolkit Pro’. Sept 5, 2003. National Research Conseil national Council Canada de recherches. Excel Tools for Presenting Metrological Comparisons by B.M. Wood, R.J. Douglas & A.G. Steele. Chapter 12. Automated Candidates for the Reference Value .

emelda
Download Presentation

User’s Guide to the ‘QDE Toolkit Pro’

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Ch 12: 149 User’s Guide to the ‘QDE Toolkit Pro’ 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_AddToWorksheet 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_AddToWorksheet . The new macro, QDE2.xls!tk_RVisMedianHAddToWorksheet, 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. Ch 12: 154 QDE Toolkit Pro - adding a Median RV - Student variate • Start with a high quality linear congruent uniform random number generator • Transform from uniform to Student probability density distribution[1+ ((x-x0)/)2 / n]-(n+1)/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. Ch 12: 155 QDE Toolkit Pro - Student variate and ISO Guide • Student Cumulative Distribution Functions for different degrees of freedom (=1, n = 2…10) • 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_RVisMedianHAddToWorksheet, 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_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. 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 fromQDE2.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 macro’s VBA code. It is set up to do NPTS=106 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_AddToWorksheethas 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 don’t do this, the only problem created is that this one macro will not run. Similarly, the Excel macro module containing tk_RVisMedianHAddToWorksheethas 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 don’t 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/fortran/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 programVFRUN66BI.exe (Visual Fortran Run-time version 6.6) fromhttp://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_AddToWorksheet, 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_AddToWorksheet. 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_AddToWorksheet 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 the“relative 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 macroQDE2.xls!tk_RVisWtMean_AddToWorksheetthe % 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 RV’s 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), Edit|Delete) 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 coefficientui / (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: <(x-x0)(y-y0)>) ui2 / N but the correlation coefficient has changed to ui / (u’(KCRV) N)

  24. Ch 12: 172 QDE Toolkit Pro - philosophical reflections on RV’s • 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 RV’s • 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 slide’s 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.

More Related