|
Here is the source code, as an Excel
spreadsheet, for the routines to perform Linear
Least Squares Fitting, with error in one or both
variables. When the data has scatter in X and in Y,
the traditional least-squares treatment can give an
incorrect characterization of the data. The excel
spreadsheet attached below uses the 'goal seek'
function of excel to do least-squares linear fits
with uncertainty in both variables, in a
straightforward and explanatory way.
This code
accompanies the tutorial Custom Excel
Functions on this web page.
(about this code)
|
|
|
|
|
A spreadsheet with the simplest possible VBA custom function: HelloWorld().
|
|
HelloWorld.xls |
|
A spreadsheet with some simple VBA functions. They demonstrate looping,
optional arguments, and other features. They may not intrinsically useful but may
prove helpful for learning VBA.
|
|
SimpleFunctions.xls |
|
A spreadsheet with a custom function to do weighted least squares linear
fits in Excel. Using the routines in this spreadsheet, you can do fits to data with
uncertainties, with a call to one function!
The procedures in this spreadsheet can do weighted and unweighted fits. There is
also a set of iterative routines to do linear fits to data with error in both coordinates.
|
|
LeastSquares.xls |
|
An Excel 97/2000 Add-In for Weighted Least Squares fits.
Download the LeastSquaresAddin.xla and copy
it into your MSOffice/Office/Library directory. The next time you start Excel, look
under Tools/Add-Ins; you should be able to add the Weighted Least Squares routines.
|
|
LeastSquaresAddin.xla
LeastSquaresAddin.xls
|
|
A Mathematica notebook which shows
how to do least squares fitting, with uncertainties, in Mathematica.
(The functions to do this -- and much more -- are standard in Mathematica.
However, they have a decidedly Byzantine interface; this function should make it
much easier.) |
|
LeastSquares.nb |
These routines were written for the UT-Austin
Modern Physics Lab. The work was partially supported by a grant from National
Instruments.
The technique is based on the paper
"Linear
least-squares fits with errors in both coordinates", Am.
J. Phys 57 #7 p. 642 (1989) by B.C. Reed (local copy of PDF).
(Also see the erratum in Am. J. Phys 58 #2 p.189 (1990),
linked from the above
paper.)
The algorithm was first developed by York, Can. J. Phys. 44 (1966) p.1079 : "Least
Squares Fitting with Errors in Both Coordinates." (no pdf available online)
This code is freely available and distributed under the Gnu
Public License. You may use this code in any programs you write, but you must not resell
this code and you must include the editable source to these routines when you
distribute your application. You must also credit me for this functionality:
please cite as something like
Philip (Flip) Kromer, Least Squares Fitting in Excel with Error in Both Variables (http://vizsage.com/other/leastsquaresexcel/).
If you modify it in any interesting way please let me
know.
mail Flip if you have problems downloading these
programs.
|
|
