Application of the method of least squares in Excel. Least squares method in Excel

The method of least squares (OLS) belongs to the field of regression analysis. It has many applications, since it allows an approximate representation of a given function by other simpler ones. OLS can be extremely useful in processing observations, and it is actively used to estimate some quantities from the results of measurements of others that contain random errors. This article will show you how to implement least squares calculations in Excel.

Statement of the problem using a specific example

Suppose there are two indicators X and Y. And Y depends on X. Since OLS is of interest to us from the point of view of regression analysis (in Excel, its methods are implemented using built-in functions), then you should immediately go on to consider a specific problem.

So, let X be the retail space of a grocery store, measured in square meters, and Y is the annual turnover, defined in millions of rubles.

It is required to make a forecast of what turnover (Y) the store will have if it has a particular retail space. Obviously, the function Y = f (X) is increasing, since the hypermarket sells more goods than the stall.

A few words about the correctness of the initial data used for prediction

Let's say we have a table built from data for n stores.

According to mathematical statistics, the results will be more or less correct if the data on at least 5-6 objects are examined. In addition, you cannot use "abnormal" results. In particular, an elite small boutique can have a turnover many times greater than the turnover of large retail outlets of the "masmarket" class.

Method essence

The table data can be displayed on the Cartesian plane as points M 1 (x 1, y 1),… M n (x n, y n). Now the solution of the problem will be reduced to the selection of an approximating function y = f (x) with a graph passing as close as possible to the points M 1, M 2, .. M n.

Of course, you can use the polynomial high degree, but this option is not only difficult to implement, but also simply incorrect, since it will not reflect the main trend that needs to be detected. The most reasonable solution is to find the straight line y = ax + b, which best approximates the experimental data, or rather, the coefficients - a and b.

Accuracy assessment

For any approximation, an assessment of its accuracy is of particular importance. Let us denote by e i the difference (deviation) between the functional and experimental values for the point x i, that is, e i = y i - f (x i).

Obviously, to estimate the accuracy of the approximation, the sum of deviations can be used, i.e., when choosing a straight line for an approximate representation of the dependence of X on Y, one should give preference to the one in which smallest value the sum e i at all points under consideration. However, not everything is so simple, since along with positive deviations, negative deviations will practically be present.

The problem can be solved using the moduli of deviations or their squares. The last method is the most widely used. It is used in many areas, including regression analysis (Excel implements two built-in functions), and has long proven its worth.

Least square method

In Excel, as you know, there is a built-in autosum function that allows you to calculate the values of all values located in the selected range. Thus, nothing prevents us from calculating the value of the expression (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

In mathematical notation, it looks like:

Since the decision was initially made to approximate using a straight line, we have:

Thus, the problem of finding the straight line that best describes the specific dependence of the quantities X and Y is reduced to calculating the minimum of a function of two variables:

This requires equating to zero the partial derivatives with respect to the new variables a and b, and solving a primitive system consisting of two equations with 2 unknowns of the form:

After some simple transformations, including dividing by 2 and manipulating the sums, we get:

Solving it, for example, by Cramer's method, we obtain a stationary point with some coefficients a * and b *. This is the minimum, that is, to predict what turnover the store will have for a certain area, the straight line y = a * x + b *, which is a regression model for the example in question, is suitable. Of course, it will not allow you to find the exact result, but it will help you get an idea of whether the purchase on credit for a store of a particular area will pay off.

How to implement least squares method in Excel

Excel has a function for calculating the OLS value. It has the following form: "TREND" (known Y values; known X values; new X values; const.). Let's apply the formula for calculating the OLS in Excel to our table.

To do this, in the cell in which the result of the calculation by the least squares method in Excel should be displayed, enter the "=" sign and select the "TREND" function. In the window that opens, fill in the appropriate fields, highlighting:

the range of known values for Y (in this case, data for turnover);
range x 1,… x n, ie the size of the retail space;
both known and unknown values of x, for which you need to find out the size of the turnover (see below for information on their location on the worksheet).

In addition, the formula contains the Boolean variable "Const". If you enter 1 in the corresponding field, this will mean that calculations should be performed, assuming that b = 0.

If you need to know the forecast for more than one value of x, then after entering the formula, you should not press "Enter", but you need to type on the keyboard the combination "Shift" + "Control" + "Enter" ("Enter").

Some features

Regression analysis can even be accessed by teapots. The Excel formula for predicting the value of an array of unknown variables - "TREND" - can be used even by those who have never heard of the method of least squares. It is enough just to know some of the features of her work. In particular:

If you arrange the range of known values of the y variable in one row or column, then each row (column) with known x values will be perceived by the program as a separate variable.
If the "TREND" window does not contain a range with known x, then if the function is used in Excel, the program will consider it as an array consisting of integers, the number of which corresponds to the range with the given values of the y variable.
To get an array of “predicted” values as an output, the trend expression must be entered as an array formula.
If new x values are not specified, then the TREND function considers them to be equal to known. If they are not specified, then array 1 is taken as an argument; 2; 3; 4;…, which is commensurate with the range with the already given parameters y.
The range containing the new x-values must be the same or more rows or columns as the range with the given y-values. In other words, it should be commensurate with the independent variables.
An array with known x values can contain multiple variables. However, if we are talking only about one, then it is required that the ranges with the given values of x and y are commensurate. In the case of multiple variables, you want the range with the given y values to fit in one column or one row.

FORECAST function

Regression analysis in Excel is implemented using several functions. One of them is called "FORECAST". It is similar to "TREND", that is, it gives the result of calculations using the least squares method. However, only for one X, for which the Y value is unknown.

Now you know the formulas in Excel for dummies that allow you to predict the future value of a given indicator according to a linear trend.

Well, at work they reported to the inspection, the article was written at home for the conference - now you can write to the blog. While I was processing my data, I realized that I could not help but write about a very cool and necessary add-in in Excel, which is called. So the article will be devoted to this particular add-in, and I will tell you about it using an example of use least squares method(OLS) to search for unknown equation coefficients when describing experimental data.

How to enable the Find Solution add-in

First, let's figure out how to enable this add-in.

1. Go to the "File" menu and select "Excel Options"

2. In the window that appears, select "Search for a solution" and click "go".

3. In the next window, put a tick in front of the item "search for a solution" and click "OK".

4. The add-on is activated - now it can be found in the "Data" menu item.

Least square method

Now briefly about least squares method (OLS) and where it can be applied.

Let's say we have a dataset after we did some experiment where we studied the effect of the X value on the Y value.

We want to describe this influence mathematically, so that later we can use this formula and know that if we change the value of X by so much, we get the value of Y so and so ...

I'll take a super-simple example (see fig.).

It’s clear that the points are located one after the other as if in a straight line, and therefore we safely assume that our dependence is described by a linear function y = kx + b. At the same time, we are definitely sure that when X is equal to zero, the value of Y is also equal to zero. This means that the function describing the dependence will be even simpler: y = kx (remember the school curriculum).

In general, we have to find the coefficient k. This is what we will do with OLS using the "search for a solution" add-in.

The method consists in the fact that (here - attention: you need to think about it) the sum of the squares of the differences between the experimentally obtained and the corresponding calculated values was minimal. That is, when X1 = 1 the actually measured value Y1 = 4.6, and the calculated y1 = f (x1) is 4, the square of the difference will be (y1-Y1) ^ 2 = (4-4.6) ^ 2 = 0.36 ... With the following the same: when X2 = 2, the actually measured value Y2 = 8.1, and the calculated y2 is 8, the square of the difference will be (y2-Y2) ^ 2 = (8-8.1) ^ 2 = 0.01. And the sum of all these squares should be as small as possible.

So, let's start training on using the OLS and Find Solution Excel Add-ins .

Applying the solution search add-in

1. If you have not turned on the "search for a solution" add-on, then go back to point How to enable the search for a solution add-in and enable 🙂

2. In cell A1, enter the value "1". This unit will be the first approximation to the real value of the coefficient (k) of our functional dependence y = kx.

3. In column B, we have the values of the X parameter, in column C - the values of the Y parameter. In the cells of the D column, we enter the formula: "coefficient k multiplied by the value of X". For example, in cell D1 we enter "= A1 * B1", in cell D2 we enter "= A1 * B2", and so on.

4. We believe that the coefficient k is equal to one and the function f (x) = y = 1 * x is the first approximation to our solution. We can calculate the sum of the squares of the differences between the measured values of Y and those calculated by the formula y = 1 * x. We can do all this manually by driving in the appropriate cell references into the formula: "= (D2-C2) ^ 2 + (D3-C3) ^ 2 + (D4-C4) ^ 2 ... etc. Finally we are mistaken and understand that we have lost a lot of time. In Excel, for calculating the sum of squares of differences, there is a special formula, "SUMKVRAZN", which will do everything for us. Enter it in cell A2 and set the initial data: the range of measured values Y (column C) and the range of calculated Y values (column D).

4. The sum of the differences of the squares has been calculated - now we go to the “Data” tab and select “Search for a solution”.

5. In the menu that appears, select cell A1 (the one with the coefficient k) as the cell to be changed.

6. Select cell A2 as the target and set the condition “set equal to the minimum value”. Remember that this is the cell where we calculate the sum of the squares of the differences between the calculated and measured values, and this sum should be minimal. Click "execute".

7. Coefficient k is selected. Now you can verify that the calculated values are now very close to the measured ones.

P.S.

In general, of course, to approximate experimental data in Excel, there are special tools that allow you to describe data using a linear, exponential, power and polynomial function, so you can often do without n solution search add-ons... I talked about all these approximation methods in my mine, so if you're interested, take a look. But when it comes to some exotic function with one unknown coefficient or optimization problems, here superstructure very opportunely.

Find solution add-in can be used for other tasks, the main thing is to understand the essence: there is a cell where we select a value, and there is a target cell in which a condition is set for selecting an unknown parameter.
That's all! In the next article I will tell you a fairy tale about a vacation, so in order not to miss the article,

4.1. Using built-in functions

Calculation regression coefficients carried out using the function

LINEST(Values_y; X-values; Konst; statistics),

Values_y- an array of y values,

X-values- optional array of values x if array X is omitted, then it is assumed that it is an array (1; 2; 3; ...) the same size as Values_y,

Konst- a boolean value that indicates whether the constant is required b was equal to 0. If Konst has the meaning TRUE or omitted, then b is calculated in the usual way. If the argument Konst is FALSE, then b is set equal to 0 and the values a are selected so that the relation y = ax.

Statistics- a boolean value that indicates whether to return additional regression statistics. If the argument Statistics has the meaning TRUE, then the function LINEST returns additional regression statistics. If the argument Statistics has the meaning LYING or omitted, then the function LINEST only returns the coefficient a and constant b.

It must be remembered that the result of the functions LINEST () is a set of values - an array.

For calculation correlation coefficient function is used

CORREL(Array1;Array2),

returning the values of the correlation coefficient, where Array1- array of values y, Array2- array of values x. Array1 and Array2 must be of the same dimension.

EXAMPLE 1... Addiction y(x) is presented in the table. Build regression line and calculate correlation coefficient.

y		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

Let's enter a table of values into a MS Excel sheet and build a scatter plot. The worksheet will take the form shown in fig. 2.

To calculate the values of the regression coefficients a and b allocate cells A7: B7, turn to the function wizard and in the category Statistical choose the function LINEST... Fill in the appeared dialog box as shown in Fig. 3 and press Ok.

As a result, the calculated value will appear only in the cell A6(fig. 4). In order for the value to appear in the cell B6 it is necessary to enter the editing mode (key F2) and then press the key combination CTRL + SHIFT + ENTER.

To calculate the value of the correlation coefficient per cell C6 the following formula was introduced:

C7 = CORREL (B3: J3; B2: J2).

Knowing the regression coefficients a and b calculate the values of the function y=ax+b for given x... To do this, we introduce the formula

B5 = $ A $ 7 * B2 + $ B $ 7

and copy it to the range C5: J5(fig. 5).

Let's draw the regression line on the diagram. Let's highlight experimental points on the chart, right-click and select the command Initial data... In the dialog box that appears (Fig. 5), select the tab Row and click on the button Add... Fill in the input fields, as shown in Fig. 6 and press the button OK... A regression line is added to the experimental data plot. By default, its graph will be displayed as points not connected by smoothing lines.

To change the appearance of the regression line, follow these steps. Right-click on the points representing the line graph, select the command Chart type and set the view of the scatter plot, as shown in Fig. 7.

You can change the line type, color and thickness as follows. Select a line in the diagram, right-click and select the command Data series format ... Next, make settings, for example, as shown in Fig. eight.

As a result of all transformations, we will receive a graph of experimental data and a regression line in one graphical area (Fig. 9).

4.2. Using a trend line.

The construction of various approximating dependencies in MS Excel is implemented as a chart property - trend line.

EXAMPLE 2... As a result of the experiment, some tabular dependence was determined.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Select and build an approximating dependence. Build graphs of tabular and selected analytical dependence.

The solution to the problem can be broken down into the following steps: inputting initial data, building a dot plot and adding a trend line to this plot.

Let's consider this process in detail. Let's enter the raw data into the worksheet and plot the experimental data. Next, select the experimental points on the graph, right-click and use the command Add l trend initiative(fig. 10).

The dialog box that appears allows you to build an approximating dependence.

The first tab (Fig. 11) of this window indicates the type of the approximating dependence.

On the second (Fig. 12), the construction parameters are determined:

· The name of the approximating dependence;

Forecast forward (backward) by n units (this parameter determines how many units forward (backward) it is necessary to extend the trend line);

Whether to show the point of intersection of a curve with a straight line y = const;

· Show the approximating function on the diagram or not (the option to show the equation on the diagram);

· Whether to place the value of the standard deviation on the diagram or not (place the parameter on the diagram the value of the approximation reliability).

Let us choose a polynomial of the second degree as an approximating dependence (Fig. 11) and derive the equation describing this polynomial on the graph (Fig. 12). The resulting diagram is shown in Fig. thirteen.

Similarly, using trend lines you can choose the parameters of such dependencies as

Linear y=a ∙ x+b,

Logarithmic y=a ∙ ln(x)+b,

Exponential y=a ∙ e b,

Power-law y=a ∙ x b,

Polynomial y=a ∙ x 2 +b ∙ x+c, y=a ∙ x 3 +b ∙ x 2 +c ∙ x + d and so on, up to a polynomial of the 6th degree inclusive,

· Linear filtration.

4.3. Using the Option Analysis Tool: Finding a Solution.

Of considerable interest is the implementation in MS Excel of the selection of the parameters of the functional dependence by the least squares method using the option analysis tool: Search for a solution. This technique allows you to choose the parameters of a function of any kind. Let's consider this possibility using the example of the following problem.

EXAMPLE 3... As a result of the experiment, the dependence z (t) was obtained, presented in the table

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Select dependency coefficients Z (t) = At 4 + Bt 3 + Ct 2 + Dt + K the least squares method.

This problem is equivalent to the problem of finding the minimum of a function of five variables

Let's consider the process of solving the optimization problem (Fig. 14).

Let the values A, V, WITH, D and TO stored in cells A7: E7... Let's calculate the theoretical values of the function Z(t)=At 4 + Bt 3 + Ct 2 + Dt + K for given t(B2: J2). To do this, in the cell B4 enter the value of the function at the first point (cell B2):

B4 = $ A $ 7 * B2 ^ 4 + $ B $ 7 * B2 ^ 3 + $ C $ 7 * B2 ^ 2 + $ D $ 7 * B2 + $ E $ 7.

Let's copy this formula to the range C4: J4 and get the expected value of the function at the points, the abscissas of which are stored in the cells B2: J2.

Into the cell B5 we introduce a formula that calculates the square of the difference between the experimental and calculated points:

B5 = (B4-B3) ^ 2,

and copy it to the range C5: J5... In a cell F7 we will store the total squared error (10). To do this, we introduce the formula:

F7 = SUM (B5: J5).

Let's use the command Service®Search for a solution and solve the optimization problem without restrictions. Fill in the input fields in the dialog box shown in Fig. 14 and press the button Execute... If a solution is found, then the window shown in Fig. 15.

The result of the work of the decision block will be output to cells A7: E7parameter values functions Z(t)=At 4 + Bt 3 + Ct 2 + Dt + K... In cells B4: J4 get expected function value at the starting points. In a cell F7 will be kept total squared error.

You can draw experimental points and a fitted line in the same graphics area by selecting a range B2: J4, call Chart Wizard and then format appearance the resulting graphs.

Rice. 17 displays the MS Excel worksheet after the calculations are done.

4.1. Using built-in functions

Calculation regression coefficients carried out using the function

LINEST(Values_y; X-values; Konst; statistics),

Values_y- an array of y values,

X-values- optional array of values x if array X is omitted, then it is assumed that it is an array (1; 2; 3; ...) the same size as Values_y,

It must be remembered that the result of the functions LINEST () is a set of values - an array.

For calculation correlation coefficient function is used

CORREL(Array1;Array2),

returning the values of the correlation coefficient, where Array1- array of values y, Array2- array of values x. Array1 and Array2 must be of the same dimension.

EXAMPLE 1... Addiction y(x) is presented in the table. Build regression line and calculate correlation coefficient.

y		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

Let's enter a table of values into a MS Excel sheet and build a scatter plot. The worksheet will take the form shown in fig. 2.

To calculate the value of the correlation coefficient per cell C6 the following formula was introduced:

C7 = CORREL (B3: J3; B2: J2).

Knowing the regression coefficients a and b calculate the values of the function y=ax+b for given x... To do this, we introduce the formula

B5 = $ A $ 7 * B2 + $ B $ 7

and copy it to the range C5: J5(fig. 5).

Let's draw the regression line on the diagram. Select the experimental points on the graph, right-click and select the command Initial data... In the dialog box that appears (Fig. 5), select the tab Row and click on the button Add... Fill in the input fields, as shown in Fig. 6 and press the button OK... A regression line is added to the experimental data plot. By default, its graph will be displayed as points not connected by smoothing lines.

Rice. 6

As a result of all transformations, we will receive a graph of experimental data and a regression line in one graphical area (Fig. 9).

4.2. Using a trend line.

The construction of various approximating dependencies in MS Excel is implemented as a chart property - trend line.

EXAMPLE 2... As a result of the experiment, some tabular dependence was determined.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Select and build an approximating dependence. Build graphs of tabular and selected analytical dependence.

The solution to the problem can be broken down into the following steps: inputting initial data, building a dot plot and adding a trend line to this plot.

The dialog box that appears allows you to build an approximating dependence.

The first tab (Fig. 11) of this window indicates the type of the approximating dependence.

On the second (Fig. 12), the construction parameters are determined:

· The name of the approximating dependence;

Forecast forward (backward) by n units (this parameter determines how many units forward (backward) it is necessary to extend the trend line);

Whether to show the point of intersection of a curve with a straight line y = const;

· Show the approximating function on the diagram or not (the option to show the equation on the diagram);

· Whether to place the value of the standard deviation on the diagram or not (place the parameter on the diagram the value of the approximation reliability).

Similarly, using trend lines you can choose the parameters of such dependencies as

Linear y=a ∙ x+b,

Logarithmic y=a ∙ ln(x)+b,

Exponential y=a ∙ e b,

Power-law y=a ∙ x b,

Polynomial y=a ∙ x 2 +b ∙ x+c, y=a ∙ x 3 +b ∙ x 2 +c ∙ x + d and so on, up to a polynomial of the 6th degree inclusive,

· Linear filtration.

4.3. Using a decision block

Of considerable interest is the implementation in MS Excel of the selection of parameters by the least squares method using a decision block. This technique allows you to choose the parameters of a function of any kind. Let's consider this possibility using the example of the following problem.

EXAMPLE 3... As a result of the experiment, the dependence z (t) was obtained, presented in the table

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Select dependency coefficients Z (t) = At 4 + Bt 3 + Ct 2 + Dt + K the least squares method.

This problem is equivalent to the problem of finding the minimum of a function of five variables

Let's consider the process of solving the optimization problem (Fig. 14).

B4 = $ A $ 7 * B2 ^ 4 + $ B $ 7 * B2 ^ 3 + $ C $ 7 * B2 ^ 2 + $ D $ 7 * B2 + $ E $ 7.

Let's copy this formula to the range C4: J4 and get the expected value of the function at the points, the abscissas of which are stored in the cells B2: J2.

Into the cell B5 we introduce a formula that calculates the square of the difference between the experimental and calculated points:

B5 = (B4-B3) ^ 2,

and copy it to the range C5: J5... In a cell F7 we will store the total squared error (10). To do this, we introduce the formula:

F7 = SUM (B5: J5).

You can draw experimental points and a fitted line in the same graphics area by selecting a range B2: J4, call Chart Wizard and then format the appearance of the resulting graphs.

Rice. 17 displays the MS Excel worksheet after the calculations are done.

5. REFERENCES

1. Alekseev ER, Chesnokova OV, Solution of problems of computational mathematics in the packages Mathcad12, MATLAB7, Maple9. - NT Press, 2006. – 596s. : ill. - (Tutorial)

2. Alekseev E.R., Chesnokova O.V., E.A. Rudchenko, Scilab, solving engineering and mathematical problems. –M., BINOM, 2008. – 260s.

3. Berezin IS, Zhidkov NP, Methods of computation.-Moscow: Nauka, 1966.-632p.

4. Garnaev A.Yu., Using MS EXCEL and VBA in economics and finance. - SPb .: BHV - Petersburg, 1999. – 332s.

5. Demidovich BP, Maron I A., Shuvalova VZ, Numerical methods of analysis. –M.: Nauka, 1967. – 368p.

6. Korn G., Korn T., Handbook of mathematics for scientists and engineers. –M., 1970, 720p.

7. Alekseev E.R., Chesnokova O.V. Methodical instructions to laboratory work in MS EXCEL. For students of all specialties. Donetsk, DonNTU, 2004.112 p.

The least squares method is a mathematical procedure for constructing linear equation which would most closely match the set of two rows of numbers. The purpose of this method is to minimize the total squared error. Excel has tools that you can use to apply this method when calculating. Let's see how this is done.

The method of least squares (OLS) is a mathematical description of the dependence of one variable on the other. It can be used in forecasting.

Enabling the Solver add-in

In order to use OLS in Excel, you need to enable the add-in "Search for a solution" which is disabled by default.

Now the function Finding a solution in Excel is activated, and its tools have appeared on the ribbon.

Conditions of the problem

Let us describe the application of the OLS with a specific example. We have two rows of numbers x and y , the sequence of which is shown in the image below.

The function can most accurately describe this dependence:

Moreover, it is known that for x = 0 y is also equal 0 ... So given equation can be described as dependency y = nx .

We have to find the minimum sum of the squares of the difference.

Solution

Let's move on to describing the direct application of the method.

As you can see, the application of the least squares method is a rather complicated mathematical procedure. We showed it in action using the simplest example, and there are much more difficult cases... However, the toolkit Microsoft Excel is designed to simplify the calculations performed as much as possible.