User’s Guide to the ‘QDE Toolkit Pro’

National Research Conseil nationalCouncil Canada de recherches

Excel Tools for Presenting Metrological Comparisons by

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

Excel Tools for Presenting Metrological Comparisons by

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

Chapter 2. Before You Start, Getting StartedChapter 2. Before You Start, Getting StartedThis chapter discusses prerequisites for getting started, andhow to get started with the QDE Toolkit Pro.This chapter discusses prerequisites for getting started, andhow to get started with the QDE Toolkit Pro.

Sept 5, 2003 Sept 5, 2003

Ch 2:16Ch 2:16

Using the Toolkit

Okay, the background and introduction is over…let’s get on with explaining the Toolkit Pro, and how you can use it to simplify your tasks.

Okay, the background and introduction is over…let’s get on with explaining the Toolkit Pro, and how you can use it to simplify your tasks.

Only For Excel Experts?

This User’s 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.

Only For Excel Experts?

This User’s 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.

Ch 2: 2Ch 2: 2

Excel checklist for QDE ‘Toolkit Pro’

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.

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.

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).

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).

Ch 2: 3Ch 2: 3

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.

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.

Ch 2: 4Ch 2: 4

What do you need to run the QDE Toolkit Pro?

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.)

A color display: we recommend least a 1024x768 display.

Specific Fonts: Arial, Tahoma, Times New Roman, Symbol, in addition to any that you wish to use.

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/ .

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 (>20 MB for .ppt), individual chapter files are also available.

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.)

A color display: we recommend least a 1024x768 display.

Specific Fonts: Arial, Tahoma, Times New Roman, Symbol, in addition to any that you wish to use.

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/ .

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 (>20 MB for .ppt), individual chapter files are also available.

Ch 2: 5Ch 2: 5

What is in the QDE Toolkit?

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.

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 User’s Guide.

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.

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 User’s Guide.

Ch 2: 6Ch 2: 6

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.

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.

Ch 2: 7Ch 2: 7

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.

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.

Ch 2: 8Ch 2: 8

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.

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.

Ch 2: 9Ch 2: 9

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).

Coverage factor evaluation for an effective degrees of freedom that is not necessarily an integer (numerically integrated for degrees of freedom >0.5).

Confidence calculation in an interval centered on the expectation of equivalence.

Estimators 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.

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).

Coverage factor evaluation for an effective degrees of freedom that is not necessarily an integer (numerically integrated for degrees of freedom >0.5).

Confidence calculation in an interval centered on the expectation of equivalence.

Estimators 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.

Ch 2: 10Ch 2: 10

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 User’s 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.

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 User’s 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.

Ch 2: 11Ch 2: 11

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.

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.

Ch 2: 12Ch 2: 12

Getting Started with QDE Toolkit Pro: Files

The starting files areQDE2.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.pdf (~10 MB) viewed with Adobe Acrobat

Chapter files of QDE2_Guide.ppt are also available

The starting files areQDE2.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.pdf (~10 MB) viewed with Adobe Acrobat

Chapter files of QDE2_Guide.ppt are also available

Ch 2: 13Ch 2: 13

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 Tools|Macro|Macros, 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.)

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 Tools|Macro|Macros, 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.)

Ch 2: 14Ch 2: 14