Least Squares in Excel

Excel is a powerful spreadsheet editor that allows you to perform a huge number of different operations and tasks. In this article, we will analyze how you can apply the least squares method (LSM), which is used to solve various problems with minimizing the sum of squared deviations of some functions from the desired variables.

Preparatory stage: activate the “Solution Search” add-on

Before proceeding with the solution of the main task, you will need to activate the add-on “Searching for a Solution” in a programme.

1. Go to the menu “file”.
2. Select an item from the list on the left “Parameters”.
3. On the right side of the subsection “Add-ons” select for parameter “Control” option “Excel add-ins” and click “Go”.
4. A window will appear to select the desired add-ons. Set the checkbox in front of the item “Searching for a Solution” and click OK.

Stage 1: initial data

Let’s break down the application of the least squares method by solving a specific example. Let’s say we have two series of numerical values ​​− X и Y.

This relationship can be described by the equation below:

Y=A+NX

Also, we know that if X=0, then Y=0. So this equation can be written as follows:

Y=NX

Let’s start our task, which is to find the sum of the squares of the difference.

Stage 2: solve the problem using LSM

1. The column to the left of X, set the name N write a number “1” (approximate value of the first coefficient. N) opposite the first value of the series X.
2. Column to the right of Y set the name NX. Then in the topmost cell (opposite the first values ​​of the rows X и Y) we write the formula for the product of the coefficients. N to the corresponding value from the column X. In this case, the address of the cell with the coefficient must be made so that it does not change when the formula is copied. Press when ready Enter.
3. Move the mouse pointer over the cell with the result. As soon as a black plus sign (fill marker) appears, hold down the left mouse button and drag it down to the last line of the table.
4. We get the results of calculations in each cell of the column NX.
5. Now you need to calculate the sum of the differences of the squares of the values Y и NX. We get up in the topmost cell of the column to the right of NX (not counting the table header) and click on the icon “Insert function” (fx).
6. In the insert function window, select a category “Mathematical”, we find the operator “SUMMKRAZN” and click OK.
7. Now you need to fill in the function arguments:
   • in the field “Array_x”  specify the coordinates of the range of cells in the column Y (no hat). Cell addresses can be specified both manually by typing them from the keyboard, and by selecting with the left mouse button pressed in the table itself.
   • in the field “Array_y” specify a range of column cells NX.
   • we press Enterwhen everything is ready.
8. Switch to tab “Data”. In a group “Analysis” click on function “Searching for a Solution”.
9. We have to fill in the search parameters for the solution:
   • in the field “Optimize objective function” you must specify a reference to the cell with the function “SUMMKRAZN”. You can do this manually or by selecting an element in the table.
   • for option “Before” select option – “Minimum”.
   • in the field “Changing Variable Cells” you need to specify the coordinates of the cell in which the corresponding value of the coefficient is located. N.
   • press when ready “Find a solution”.
10. After the function is executed, a window with the results of the solution search will appear and the value in the column will be replaced N. The value found is the least square of the function. Click OKif the result is satisfactory.

Conclusion

So, we have just taken a practical example of how to apply the least squares method in Excel. In practice, more complex tasks may occur, however, in general, the logic of actions is similar to what we described.