Training and consultancy for testing laboratories.

Archive for June, 2017

Sharing an Excel calculation on Monte Carlo simulation

monte-carlo-simulation-Excel-example

A top down MU approach by the Monte Carlo simulation technique

Monte Carlo Casino A

A Top down approach by Monte Carlo simulation

Practical way to calculate combined uncertainty by MS Excel spreadsheet (PDF)

Practical way to calculate combined uncertainty by Excel spreadsheet

Practical way to calculate combined uncertainty by MS Excel spreadsheet

Using an ordinary spreadsheet to calculating a combined uncertainty saves a great deal of time and is particularly useful for checking and updating with new data whenever available.

The Eurachem/CITAC Guide (3rd edition) Quantifying Uncertainty in Analytical Measurement has given a number of spreadsheet calculation examples. The approach using the MS Excel spreadsheet is summarized as described below.

Assume that the test result y is a function of three input parameters p, q and r, i.e. y = f (p, q, r), such as: y=(p x q)/r.

Let’s set up a basic spreadsheet as in Figure 1:

In Figure 1, enter the values of p, q, r and the formula for calculating y in cell A6 of the spreadsheet and the respective values of standard uncertainties u(p), u(q) and u(r) in column B. Copy cell A6 across to C6:E6.

Figure 1: Entries of raw data
Sheet0

Add u(p) to p in cell C2, u(q) to q in cell D3, and u(r) to r in cell E4 as in Figure 2 below. Upon recalculating the spreadsheet automatically by the software, cell C6 then becomes f(p+u(p),q, r) [denoted by f(p’,q,r)], and so are the others.

Figure 2: Entries of individual standard uncertainty contributions
Sheet1

The row 8 of Figure 2 calculates the difference between the calculated y values in columns C, D and E individually and the originally calculated y value in A6. The row 9 shows the squared differences of columns C, D and E.

To obtain the combined standard uncertainty in y, i.e. u(y), just sum up all these individual squared differences in row 9 and take a square root of the sum, as shown in Figure 3.

Figure 3: Calculation of combined standard uncertainty
Sheet2
In fact, the contents of the cells C8, D8 and E8 show the contributions ui(y)=ciu(xi) of the individual uncertainty components to the uncertainty in y, making it easy to see which components are significant. It may be noted that ci is called the sensitivity coefficients of standard uncertainty u(xi).

A screen picture captured from the Eurachem/CITAC Guide on an example of the calculation table is shown below.
Eurachem table

Comments on the last MU worked example

Quality control chart

Comments on the last article

How to use Excel on AD statistic test for data normality?

A-D Calculation on 25 data Chloride

A worked example on top down MU evaluation method by precision, accuracy & trueness

A worked example on MU evaluation by precision accuracy and trueness