I described an alternative linear regression approach in Deming Regression. Whereas the standard linear regression techniques assume that all measurement error in the calculated model is in the dependent variable Y, the Deming Regression technique considers that both X and Y variable measurements may contain errors. Pairs of X-Y coordinates are used to estimate the variances of the X and Y data.
Excel includes linear regression among its built-in functions, but there is no built-in Deming Regression capability. Users who wanted this feature had to purchase third party packages, or perform their calculations in specialized statistics programs outside of Excel.
Deming Regression Utility
I have created a new utility to address this functionality gap. It is a simple Excel add-in, with a simple user interface. The calculations are based on algorithms found in the references at the end of this article.
When installed, the utility has a simple menu item on the PTS Charts menu
or button on the Add-Ins tab
The PTS Deming Regression Utility provides two methods for calculating Deming Regression parameters. You can use custom worksheet formulas, or you can use a dialog-driven interface.
Worksheet Formulas for Deming Regression
This option does not require use of the menu or ribbon buttons. The user calculates Deming Regression parameters using simple worksheet formulas.
Referring to the regression results in the previous post
The slope, intercept, and correlation calculations are each the result of a single worksheet formula:
The Deming Regression results are returned in a three-element array formula with this syntax:
=DEMING(Y-values, X-values, Horizontal)
Y-values and X-values are required arguments which reference ranges. Each range must be one row or column only, with an even number of cells, and the two ranges must be the same size. The Horizontal argument is an optional True or False indicating the orientation of the output. The default is TRUE, and the results will be output in a row.
Don’t forget, the entire range containing the array formula is selected, the formula is typed, and it is entered by holding down CTRL+SHIFT and pressing ENTER.
Here is the row output form of the analysis:
Here is the column output form of the analysis; note the FALSE argument in the third position:
The worksheet formulas are easy to use, but if you send the workbook to a colleague, they also need the utility installed to see the results. Without the utility, all calculations are replaced by #NAME? errors.
Dialog-Driven Deming Regression
The utility features a simple dialog that helps to calculate the Deming Regression results. To use the dialog, click on the Deming Regression item in the menu or ribbon tab, shown above. A simple dialog appears.
Select the appropriate ranges for the inputs and outputs, press OK, and the program fills in the results.
Calculations made using the dialog are not lost when viewed on a machine that does not have the utility installed.
Download and Install the Utility
Click this PTS Deming Regression Utility link to download the utility. The download is a simple Excel add-in. Save it to any convenient directory, then install it following the instructions in Installing an Excel Add-In or Installing an Add-In in Excel 2007.
Regression and Trendline Articles in this Blog
- Trendlines and Chart Types in Excel
- Add One Trendline for Multiple Series
- Trendline Calculator for Multiple Series
- Trendline Fitting Errors
- Choosing a Trendline Type
- Plot Two Time Series and Trendlines with Different Dates
- Polynomial Fit vs. Statistical Process Control
- Moving Averages
- Stacked Column Chart with Stacked Trendlines
- Deming Regression
- LOESS Smoothing in Excel
- LOESS Utility for Excel
, Excel for Chemists: A Comprehensive Guide, 2nd Edition. by E. Joseph Billo, Copyright 2001 by John Wiley & Sons, Inc.
Incorrect Least-Squares Regression Coefficients in Method-Comparison Analysis by P. Joanne Cornbleet and Nathan Gochman, Clin. Chem. 25/3, 432-438 (1979).