Uncertainty Calculations That Excel!

The other day I read an article titled “Five easy steps for adding measurement uncertainties to your calibration data.”  It described a very easy process of copying cells from one Microsoft Excel® file to another, then editing the data in the cells.  As I continued to read, the article concluded with how this copy and paste operation took a fraction of the time when compared to other software tools built for metrology.
As a software engineer this was counterintuitive to everything I was ever taught as a programmer.   “Copy & Paste” was bad mojo, very bad, extremely bad!  Don’t copy & paste I was always told, it just creates more problems than it’s worth. Reading this article caught me by surprise; how could it be such a time saver when compared to tools built for metrology? If copy and paste is such a bad idea for programmers, why didn’t the same rule not apply to metrology and uncertainties?

So first some background:  As a junior programmer, copy & paste was considered bad programming for two major reasons.  First, copying code created multiple copies of the same code in memory.  This was highly inefficient when computers have limited memory.  So as it was explained to me, create a function and call it instead of duplicating code.  Anytime you think you need to copy something, first think of how you can make it a reusable function.

The second and most important problem with copy and pasting code was the potential of duplicating errors.  If there is an error in the code that was copied, that error would be duplicated.  If there is an error, now it exists in multiple places, and you have no idea where or how many times the error was duplicated.  If the error existed in a single function then the error and its effects on the rest of the solution are easier to support.

So it is only reasonable to assume the same problem applies to metrology and uncertainty calculations! If there is an error or problem in the cells of the first spread sheet, they will have been propagated to an unknown number of other Excel files.  Just like in software, errors that are found later present a huge cost in fixing them when compared to the initial cost of development.  That is why copy and paste is frowned upon in software development.

But for metrology the problem is bigger!  We know different requirements require different uncertainty calculations.  And there are some major changes on the horizon with 17025.   Updating a single function or a set of functions will prove to be much easier than updating all those Excel files.
Now understand, I use the hell out of Excel.  I would rather open a new spreadsheet to make a quick calculation than use the calculator application.  I have tons and tons of Excel files.  And yes, I have used it to calculate uncertainties.

What I don’t like about Excel, when it comes to using it as a metrology based uncertainty calculator or for calibration data collection, is that it mixes data and function in a single file.  At first it seems like a good idea, but it is not.  Excel is a Band-Aid® that puts both data and formulas in an unstructured file.  If the whole goal is to create a file that you will never use again, then Excel is a good fit.  But if you want to have data that you can use in the future, Excel—when it comes to metrology—is more problem than solution because it is unstructured.

If your company is serious about metrology and has a focus on using the data that is collected during a calibration, then I suggest you invest in a database.  Putting your calibration results in a data table will allow you to recall that data and use it for other things like interval and reliability analysis.  Placing your uncertainty calculations inside of your data collection tools is also very bad practice.  There is a reason we call them Estimated Measurement Uncertainties; they are estimates and those estimates change. And you never have to re-run a calibration because your estimated uncertainties are part of the data collection process.  Data collecting is data collection and uncertainty calculations are something that should be done post process, but that is a topic for another day.

I would like to conclude, there are some amazing tools out there for calculating measurement uncertainties—from the simple to the complex.  Just go to Cal Lab Magazine’s web site (www.callabmag.com) and look under Metrology Related -> Freeware.   There are several free tools that I think you should check out.