Least Squares Method in Excel. Regression analysis

The least squares method (least squares) is in the field of regression analysis. It has many uses, as 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 values ​​from the results of measurements of others containing random errors. In this article, you'll learn how to implement least squares calculations in Excel.

Statement of the problem on a concrete example

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

, X — , , Y — , .

, (Y) , . , Y = f (X) , , .

,

, , n .

, - , 5-6 . , «» . , , «».

M₁ (x₁, y₁), … M_n (x_n, y_n). y = f (x), , M_1, M_{2, ..}M_n.

, , , , , . = ax + b, , a , - a b.

. e_i () x_i, . . e_i = y_i - f (x_i).

, , . . X Y , e_i . , , .

, . . , ( Excel ), .

Excel, , , , . , (e₁² + e₂² + e₃²+ ... e_n²).

:

, :

, , X Y, :

a b, , 2- :

, 2 , :

, , , a^* b^*. , . . , , y = a^*x + b^*, , . , , , .

Excel

"" . : «» (. Y; . X; X; .). Excel .

, Excel, «=» «». , :

• Y ( );
• x₁, …x_n, . . ;
• , x, ( . ).

, «». 1, , , , b = 0.

x, «», «Shift» + «Control»+ «Enter» («»).

. Excel — «» — , . . :

• y , () x .
• «» x, Excel , , y.
• «» , .
• x, «» . , 1; 2; 3; 4;…, y.
• , x , y. .
• x . , , x y . , y .

«»

Excel . «». «», . . . X, Y.

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