Weighted Linear Least-Squares Fit - A Need? Monte Carlo Simulation Gives the Answer

Spreadsheet computer simulations can identify the influencing factors for the set-up of a calibration function such as the number of calibration points and their distribution or the position of the experimental points. A simple linear least-squares fit (linear regression) is not always allowed from theory but a weighted fit may be needed. By using a Monte Carlo approach, that is, by generating a large number of calibration functions and associated sample data points (for example 1000 for each set of simulations), the quality of the experimental results (bias and standard deviation) can be studied under different conditions. This article presents a spreadsheet for the simulation of unweighted and weighted linear least-squares fit. In practice, weighted fitting is only needed if all of the following conditions are fulfilled: a large calibration range; an equal distribution of the calibration points; the absolute standard deviations of the calibration points are not constant; and a sample result is at the lower end of the calibration range.