The weighted error values for each point are squared, so the weights are also squared, but the standard definition for weighted least squares applies the weight to the squared errors (see weighted linear least squares).Linest can be used with weighted data by applying the weights to both the X and Y data, but to return the correct results the following points are important: Note that Linest returns the coefficients in reverse order, so our equation is given by: In the second formula Const is entered as False, so an additional column is required with X = 1. For this case the X range only requires the X1 and X2 values (columns B and C). In the first formula, the optional Const value is omitted, so Const is set to True, and the equation constant value (A) is calculated. The first two results use the Linest function with no weighting.
That fits the given Y values as closely as possible. We wish to find a linear equation of the form: The screen shot below shows some sample data with two known variables (X1 and X2) and an associated value Y. The Alglib and Python downloads also include wide variety of other functions. The other two both use Python and require Python and xlwings to be installed.
Excel linear regression least squares code#
All the code used is free and open source, and may be downloaded from: This post looks at various options, including using Linest with modified input, VBA user defined functions (UDFS) and UDFs using the Alglib and Python Scipy libraries. However, if we want to use weighted data (give the values at some points more importance than others), there are no built in functions to do the job.
That is, they find the coefficients of a straight line (or higher dimension shape) so that the sum of the squares of the distances of each data point from the line is a minimum. That’s what the Linest and Trend functions do. Least squares linear regression in Excel is easy.