Where y x is the calculated value from Eq.
The average approximation error of up to 15% indicates a well-fitted equation model.
For seven territories of the Ural region for 199X, the values of two characteristics are known.
Required:1. To characterize the dependence of y on x, calculate the parameters of the following functions:
a) linear;
b) power;
c) demonstrative;
d) an equilateral hyperbola (you also need to figure out how to pre-linearize this model).
2. Evaluate each model through average approximation error A cf and Fisher's F-test.
We solve the problem using the online calculator Linear regression equation.
a) linear regression equation;
Using the graphical method.
This method is used to visually depict the form of connection between the studied economic indicators. To do this, a graph is drawn in a rectangular coordinate system, the individual values of the resultant characteristic Y are plotted along the ordinate axis, and the individual values of the factor characteristic X are plotted along the abscissa axis.
The set of points of the resultant and factor characteristics is called correlation field.
Based on the correlation field, we can hypothesize (for the population) that the relationship between all possible values of X and Y is linear.
The linear regression equation is y = bx + a + ε
Here ε is a random error (deviation, disturbance).
Reasons for the existence of a random error:
1. Failure to include significant explanatory variables in the regression model;
2. Aggregation of variables. For example, the total consumption function is an attempt to express generally the aggregate of individual spending decisions. This is only an approximation of individual relations that have different parameters.
3. Incorrect description of the model structure;
4. Incorrect functional specification;
5. Measurement errors.
Since deviations ε i for each specific observation i are random and their values in the sample are unknown, then:
1) from observations x i and y i only estimates of parameters α and β can be obtained
2) The estimates of the parameters α and β of the regression model are the values a and b, respectively, which are random in nature, because correspond to a random sample;
Then the estimating regression equation (constructed from sample data) will have the form y = bx + a + ε, where e i are the observed values (estimates) of the errors ε i , and a and b are, respectively, estimates of the parameters α and β of the regression model that should be found.
To estimate the parameters α and β - the least squares method (least squares method) is used.
We get b = -0.35, a = 76.88
Regression equation:
y = -0.35 x + 76.88
| x | y | x 2 | y 2 | x y | y(x) | (y i -y cp) 2 | (y-y(x)) 2 | |y - y x |:y |
| 45,1 | 68,8 | 2034,01 | 4733,44 | 3102,88 | 61,28 | 119,12 | 56,61 | 0,1094 |
| 59 | 61,2 | 3481 | 3745,44 | 3610,8 | 56,47 | 10,98 | 22,4 | 0,0773 |
| 57,2 | 59,9 | 3271,84 | 3588,01 | 3426,28 | 57,09 | 4,06 | 7,9 | 0,0469 |
| 61,8 | 56,7 | 3819,24 | 3214,89 | 3504,06 | 55,5 | 1,41 | 1,44 | 0,0212 |
| 58,8 | 55 | 3457,44 | 3025 | 3234 | 56,54 | 8,33 | 2,36 | 0,0279 |
| 47,2 | 54,3 | 2227,84 | 2948,49 | 2562,96 | 60,55 | 12,86 | 39,05 | 0,1151 |
| 55,2 | 49,3 | 3047,04 | 2430,49 | 2721,36 | 57,78 | 73,71 | 71,94 | 0,172 |
| 384,3 | 405,2 | 21338,41 | 23685,76 | 22162,34 | 405,2 | 230,47 | 201,71 | 0,5699 |
Note: the values of y(x) are found from the resulting regression equation:
y(45.1) = -0.35*45.1 + 76.88 = 61.28
y(59) = -0.35*59 + 76.88 = 56.47
... ... ...
Approximation error
Let us evaluate the quality of the regression equation using the error of absolute approximation. Average approximation error- average deviation of calculated values from actual ones:
Since the error is less than 15%, this equation can be used as regression.
F-statistics. Fisher criterion.
3. The tabulated value is determined from the Fisher distribution tables for a given significance level, taking into account that the number of degrees of freedom for the total sum of squares (larger variance) is 1 and the number of degrees of freedom for the residual sum of squares (smaller variance) in linear regression is n-2 .
4. If the actual value of the F-test is less than the table value, then they say that there is no reason to reject the null hypothesis.
Otherwise, the null hypothesis is rejected and the alternative hypothesis about the statistical significance of the equation as a whole is accepted with probability (1-α).
< Fkp, то коэффициент детерминации статистически не значим (Найденная оценка уравнения регрессии статистически не надежна).
b) power regression;
The solution is carried out using the Nonlinear Regression service. When selecting, specify Power y = ax b
c) exponential regression;
d) model of an equilateral hyperbola.
System of normal equations.
For our data, the system of equations has the form
7a + 0.1291b = 405.2
0.1291a + 0.0024b = 7.51
From the first equation we express a and substitute it into the second equation
We get b = 1054.67, a = 38.44
Regression equation:
y = 1054.67 / x + 38.44
Approximation error.
Let us evaluate the quality of the regression equation using the error of absolute approximation.
Since the error is less than 15%, this equation can be used as regression.
Fisher criterion.
Testing the significance of a regression model is carried out using Fisher's F test, the calculated value of which is found as the ratio of the variance of the original series of observations of the indicator being studied and the unbiased estimate of the variance of the residual sequence for this model.
If the calculated value with k1=(m) and k2=(n-m-1) degrees of freedom is greater than the tabulated value at a given significance level, then the model is considered significant.
where m is the number of factors in the model.
The statistical significance of paired linear regression is assessed using the following algorithm:
1. A null hypothesis is put forward that the equation as a whole is statistically insignificant: H 0: R 2 =0 at the significance level α.
2. Next, determine the actual value of the F-criterion:
where m=1 for pairwise regression.
Table value of the criterion with degrees of freedom k1=1 and k2=5, Fkp = 6.61
Since the actual value of F< Fkp, то коэффициент детерминации статистически не значим (Найденная оценка уравнения регрессии статистически не надежна).
Recall that regression analysis is a type of statistical analysis used for forecasting. Regression analysis allows you to estimate the strength of the relationship between variables by offering a mechanism for calculating the expected value of a variable from several already known values.
Trend lines can complement data series presented in unnormalized area charts, bar charts, histograms, graphs, stock charts, scatter charts, and bubble charts. The use of a trend line of one kind or another is determined by the type of data. You cannot add trend lines to data series in 3-D charts, normal charts, radar charts, pie charts, or donut charts.
The smoothed curve shows the pattern in the development of data more clearly. It is built on the points of a moving average, where a moving average means a sequence of average numbers, each of which is calculated from a certain subset of the data series.
Adding a trend line or moving average to data series
Excel uses six different types of trend lines (fitting and smoothing) that can be added to a chart (Figure 18.11):
- Linear approximation(Linear) is a straight line that best describes the data set. The equation of a straight line is y=ax+b, where a is the tangent of the angle of inclination, b is the point of intersection of the straight line with the y-axis. Linear approximation is used for variables that increase or decrease at a constant rate.
- Logarithmic approximation(Logarithmic) describes well both positive and negative quantities, which initially increase or decrease rapidly, and then gradually stabilize. The logarithmic approximation uses the equation y=c*lnx+b, where c and b are constants, In is the natural logarithm.
- Polynomial approximation(Polynomial) is used to describe quantities that alternately increase and decrease. It is advisable to use it for analyzing a large data set of unstable magnitude. The degree of the polynomial is determined by the number of extrema (maxima and minima) of the curve. A second degree polynomial can describe only one maximum or minimum. A third degree polynomial has one or two extrema. A fourth-degree polynomial can have no more than three extrema. The polynomial approximation is described by the equation y=a+ciXi+C2X2++Cigx18, where a, Cj-Cjg are constants. The required degree of the polynomial is specified in the Degree field (Fig.). The maximum degree value is 18.
- Power approximation(Power) gives good results if the dependence contained in the data is characterized by a constant growth rate. An example of such a relationship is the acceleration graph of a car. If the data contains zero or negative values, the power-law approximation cannot be used. The power-law approximation is described by the equation y=a * xn, where a and n are constants.
- Exponential approximation(Exponential) should be used if the rate of data change is continuously increasing. However, for data that contains zero or negative values, this type of approximation is not applicable. The exponential approximation is described by the equation y = a ebx, where a and b are constants.
- Linear filtering(Moving average) allows you to smooth out data fluctuations and thus more clearly show the nature of the dependence. Such a trend line is constructed from a certain number of points (it is specified by the Period parameter). The data elements are averaged, and the resulting result is used as the average value for the approximation. Thus, if the Tonki parameter is equal to 2, the first point of the smoothing curve is defined as the average of the first two data elements, the second point is the average of the next two elements, and so on. To calculate the moving average, the equation y = (Aj+Aj_i++Aj_n+i)/n is used.
Adding a trendline to data series
To add a trendline to a data series, follow these steps:
- select the data series to which you want to add a trend line or moving average;
- select team Add a trend line(Add Trendline) in the menu Diagram(Chart). On the tab Type(Type) select the desired type of regression trend line or moving average line (Fig. 18.11);
- when choosing a type Polynomial(Polynomial) enter in the field Degree(Order) the highest degree for the independent variable;
- when choosing a type Moving average(Moving Average) enter in the field Points(Period) number of points used to calculate the moving average.
Rice. 11/18. Selecting a trend line
DEPENDENCIES
Excel has tools that allow you to predict processes. The approximation problem arises when it is necessary to analytically describe phenomena that take place in life and are given in the form of tables containing the values of the argument (arguments) and functions. If the dependence can be found, it is possible to make a prediction about the behavior of the system under study in the future and, possibly, choose the optimal direction for its development. Such an analytical function (also called a trend) can have different forms and different levels of complexity depending on the complexity of the system and the desired accuracy of the representation.
10.1. Linear regression
The simplest and most popular is straight line approximation - linear regression.
Let us have actual information about profit levels Y depending on the size of X investment - Y(X). In Fig. Figure 10.1-1 shows four such points M(Y,X). Let us also have reason to assume that this dependence is linear, i.e. looks like Y=A+BX. If we were able to find the coefficients A and B and use them to construct a straight line (for example, like the one in the figure), in the future we could make informed assumptions about the dynamics of the business and the possible commercial state of the enterprise in the future. Obviously, we would be satisfied with a straight line located as close as possible to the known points M(Y,X), i.e. having a minimum sum of deviations or sum of errors (in the figure, deviations are shown by dotted lines). It is known that there is only one such line.
|
|
To solve this problem, the least squares error method is used. The difference (error) between the known value Y1 of point M1(Y1,X1) and the value Y(X1) calculated using the straight line equation for the same value X1 will be
D1 = Y1 – A – B X1.
Same difference
for X=X2 will be D2 = Y2 – A – B X2;
for X=X3 D3 = Y3 – A – B X3;
and for X=X4 D4 = Y4 – A – B X4.
Let us write an expression for the sum of squares of these errors
Ф(A,В)=(Y1–A–B X1) 2 +(Y2–A–B X2) 2 +(Y3–A–B X3) 2 +(Y4–A–B X4) 2
or abbreviated Ф(B,A) = å(Yi – A – BXi) 2.
Here we know all X and Y and unknown coefficients A and B. Let us draw the desired straight line in such a way (i.e., choose A and B such) that this sum of squared errors Ф(A,B) is minimal. The conditions for minimality are the known relations
¶Ф(A,B)/¶A=0 and ¶Ф(A,B)/¶B=0.
Let us derive these expressions (we omit the subscripts at the sum sign):
¶[å(Yi–A–B Xi) 2 ]/¶A = å(Yi–A–B Xi)(–1)
¶[å(Yi–A–B Xi) 2 ]/¶B = å(Yi–A–B Xi)(–Xi).
Let us transform the resulting formulas and equate them to zero
Theoretical information
In practice, when modeling various processes - in particular, economic, physical, technical, social - one or another method of calculating approximate values of functions from their known values at certain fixed points is widely used.
This kind of function approximation problem often arises:
- when constructing approximate formulas for calculating the values of characteristic quantities of the process under study using tabular data obtained as a result of the experiment;
- in numerical integration, differentiation, solving differential equations, etc.;
- if necessary, calculate the values of functions at intermediate points of the considered interval;
- when determining the values of characteristic quantities of a process outside the considered interval, in particular when forecasting.
If, to model a certain process specified by a table, we construct a function that approximately describes this process based on the least squares method, it will be called an approximating function (regression), and the task of constructing approximating functions itself will be called an approximation problem.
This article discusses the capabilities of the MS Excel package for solving this type of problem, in addition, it provides methods and techniques for constructing (creating) regressions for tabulated functions (which is the basis of regression analysis).
Excel has two options for building regressions.
- Adding selected regressions (trendlines) to a diagram built on the basis of a data table for the process characteristic under study (available only if a diagram has been constructed);
- Using the built-in statistical functions of the Excel worksheet, allowing you to obtain regressions (trend lines) directly from the source data table.
Adding trend lines to a chart
For a table of data that describes a process and is represented by a diagram, Excel has an effective regression analysis tool that allows you to:
- build on the basis of the least squares method and add five types of regressions to the diagram, which model the process under study with varying degrees of accuracy;
- add the constructed regression equation to the diagram;
- determine the degree of correspondence of the selected regression to the data displayed on the chart.
Based on chart data, Excel allows you to obtain linear, polynomial, logarithmic, power, exponential types of regressions, which are specified by the equation:
y = y(x)
where x is an independent variable that often takes the values of a sequence of natural numbers (1; 2; 3; ...) and produces, for example, a countdown of the time of the process under study (characteristics).
1 . Linear regression is good for modeling characteristics whose values increase or decrease at a constant rate. This is the simplest model to construct for the process under study. She
y = mx + b
where m is the tangent of the linear regression slope to the x-axis; b - coordinate of the point of intersection of linear regression with the ordinate axis.
2 . A polynomial trend line is useful for describing characteristics that have several distinct extremes (maxima and minima). The choice of polynomial degree is determined by the number of extrema of the characteristic under study. Thus, a second-degree polynomial can well describe a process that has only one maximum or minimum; polynomial of the third degree - no more than two extrema; polynomial of the fourth degree - no more than three extrema, etc.
In this case, the trend line is constructed in accordance with the equation:
y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6
where coefficients c0, c1, c2,... c6 are constants whose values are determined during construction.
3 . The logarithmic trend line is successfully used when modeling characteristics whose values initially change rapidly and then gradually stabilize.
Constructed in accordance with the equation:
y = c ln(x) + b
4 . A power-law trend line gives good results if the values of the relationship under study are characterized by a constant change in the growth rate. An example of such a dependence is the graph of uniformly accelerated motion of a car. If there are zero or negative values in the data, you cannot use a power trend line.
Constructed in accordance with the equation:
y = c xb
where coefficients b, c are constants.
5 . An exponential trend line should be used when the rate of change in the data is continuously increasing. For data containing zero or negative values, this type of approximation is also not applicable.
Constructed in accordance with the equation:
y = c ebx
where coefficients b, c are constants.
When selecting a trend line, Excel automatically calculates the value of R2, which characterizes the reliability of the approximation: the closer the R2 value is to unity, the more reliably the trend line approximates the process under study. If necessary, the R2 value can always be displayed on the chart.
Determined by the formula:
To add a trend line to a data series:
- activate a chart based on a series of data, i.e. click within the chart area. The Diagram item will appear in the main menu;
- after clicking on this item, a menu will appear on the screen in which you should select the Add trend line command.
The same actions can be easily implemented by moving the mouse pointer over the graph corresponding to one of the data series and right-clicking; In the context menu that appears, select the Add trend line command. The Trendline dialog box will appear on the screen with the Type tab opened (Fig. 1).
After this you need:
Select the required trend line type on the Type tab (the Linear type is selected by default). For the Polynomial type, in the Degree field, specify the degree of the selected polynomial.
1 . The Built on series field lists all data series in the chart in question. To add a trend line to a specific data series, select its name in the Built on series field.
If necessary, by going to the Parameters tab (Fig. 2), you can set the following parameters for the trend line:
- change the name of the trend line in the Name of the approximating (smoothed) curve field.
- set the number of periods (forward or backward) for the forecast in the Forecast field;
- display the equation of the trend line in the diagram area, for which you should enable the show equation on the diagram checkbox;
- display the approximation reliability value R2 in the diagram area, for which you should enable the Place the approximation reliability value on the diagram (R^2) checkbox;
- set the intersection point of the trend line with the Y axis, for which you should enable the checkbox for the intersection of the curve with the Y axis at a point;
- Click the OK button to close the dialog box.
In order to start editing an already drawn trend line, there are three ways:
use the Selected trend line command from the Format menu, having previously selected the trend line;The Trend Line Format dialog box will appear on the screen (Fig. 3), containing three tabs: View, Type, Parameters, and the contents of the last two completely coincide with the similar tabs of the Trend Line dialog box (Fig. 1-2). On the View tab, you can set the line type, its color and thickness.
To delete a trend line that has already been drawn, select the trend line to be deleted and press the Delete key.
The advantages of the considered regression analysis tool are:
- the relative ease of constructing a trend line on charts without creating a data table for it;
- a fairly wide list of types of proposed trend lines, and this list includes the most commonly used types of regression;
- the ability to predict the behavior of the process under study by an arbitrary (within the limits of common sense) number of steps forward and also backward;
- the ability to obtain the trend line equation in analytical form;
- the possibility, if necessary, of obtaining an assessment of the reliability of the approximation.
The disadvantages include the following:
the construction of a trend line is carried out only if there is a diagram built on a series of data;Trend lines can be used to supplement data series presented on charts such as graph, histogram, flat non-standardized area charts, bar charts, scatter charts, bubble charts, and stock charts.
You cannot add trend lines to data series in 3D, normalized, radar, pie, and donut charts.
Using Excel's built-in functions
Excel also has a regression analysis tool for plotting trend lines outside the chart area. There are a number of statistical worksheet functions you can use for this purpose, but all of them only allow you to build linear or exponential regressions.
Excel has several functions for constructing linear regression, in particular:
- TREND;
- LINEST;
- SLOPE and CUT.
As well as several functions for constructing an exponential trend line, in particular:
- HEIGHT;
- LGRFPRIBL.
It should be noted that the techniques for constructing regressions using the TREND and GROWTH functions are almost the same. The same can be said about the pair of functions LINEST and LGRFPRIBL. For these four functions, creating a table of values uses Excel features such as array formulas, which somewhat clutters the process of building regressions. Let us also note that the construction of linear regression, in our opinion, is most easily accomplished using the SLOPE and INTERCEPT functions, where the first of them determines the slope of the linear regression, and the second determines the segment intercepted by the regression on the y-axis.
The advantages of the built-in functions tool for regression analysis are:
- a fairly simple, uniform process of generating data series of the characteristic under study for all built-in statistical functions that define trend lines;
- standard methodology for constructing trend lines based on generated data series;
- the ability to predict the behavior of the process under study by the required number of steps forward or backward.
The disadvantages include the fact that Excel does not have built-in functions for creating other (except linear and exponential) types of trend lines. This circumstance often does not allow choosing a sufficiently accurate model of the process under study, as well as obtaining forecasts that are close to reality. In addition, when using the TREND and GROWTH functions, the equations of the trend lines are not known.
It should be noted that the authors did not set out to present the course of regression analysis with any degree of completeness. Its main task is to show, using specific examples, the capabilities of the Excel package when solving approximation problems; demonstrate what effective tools Excel has for building regressions and forecasting; illustrate how such problems can be solved relatively easily even by a user who does not have extensive knowledge of regression analysis.
Examples of solving specific problems
Let's look at solving specific problems using the listed Excel tools.
Problem 1
With a table of data on the profit of a motor transport enterprise for 1995-2002. you need to do the following:
- Build a diagram.
- Add linear and polynomial (quadratic and cubic) trend lines to the chart.
- Using the trend line equations, obtain tabular data on enterprise profits for each trend line for 1995-2004.
- Make a forecast for the enterprise's profit for 2003 and 2004.
The solution of the problem
- In the range of cells A4:C11 of the Excel worksheet, enter the worksheet shown in Fig. 4.
- Having selected the range of cells B4:C11, we build a diagram.
- We activate the constructed diagram and, according to the method described above, after selecting the type of trend line in the Trend Line dialog box (see Fig. 1), we alternately add linear, quadratic and cubic trend lines to the diagram. In the same dialog box, open the Parameters tab (see Fig. 2), in the Name of the approximating (smoothed) curve field, enter the name of the trend being added, and in the Forecast forward for: periods field, set the value 2, since it is planned to make a profit forecast for two years ahead. To display the regression equation and the approximation reliability value R2 in the diagram area, enable the show equation on the screen checkboxes and place the approximation reliability value (R^2) on the diagram. For better visual perception, we change the type, color and thickness of the constructed trend lines, for which we use the View tab of the Trend Line Format dialog box (see Fig. 3). The resulting diagram with added trend lines is shown in Fig. 5.
- To obtain tabular data on enterprise profits for each trend line for 1995-2004. Let's use the trend line equations presented in Fig. 5. To do this, in the cells of the range D3:F3, enter text information about the type of the selected trend line: Linear trend, Quadratic trend, Cubic trend. Next, enter the linear regression formula in cell D4 and, using the fill marker, copy this formula with relative references to the cell range D5:D13. It should be noted that each cell with a linear regression formula from the range of cells D4:D13 has as an argument a corresponding cell from the range A4:A13. Similarly, for quadratic regression, fill the range of cells E4:E13, and for cubic regression, fill the range of cells F4:F13. Thus, a forecast for the enterprise's profit for 2003 and 2004 has been compiled. using three trends. The resulting table of values is shown in Fig. 6.
Problem 2
- Build a diagram.
- Add logarithmic, power and exponential trend lines to the chart.
- Derive the equations of the obtained trend lines, as well as the reliability values of the approximation R2 for each of them.
- Using the trend line equations, obtain tabular data on the enterprise's profit for each trend line for 1995-2002.
- Make a forecast of the company's profit for 2003 and 2004 using these trend lines.
The solution of the problem
Following the methodology given in solving problem 1, we obtain a diagram with logarithmic, power and exponential trend lines added to it (Fig. 7). Next, using the obtained trend line equations, we fill out a table of values for the enterprise’s profit, including the predicted values for 2003 and 2004. (Fig. 8).
In Fig. 5 and fig. it can be seen that the model with a logarithmic trend corresponds to the lowest value of approximation reliability
R2 = 0.8659
The highest values of R2 correspond to models with a polynomial trend: quadratic (R2 = 0.9263) and cubic (R2 = 0.933).
Problem 3
With the table of data on the profit of a motor transport enterprise for 1995-2002, given in task 1, you must perform the following steps.
- Obtain data series for linear and exponential trend lines using the TREND and GROW functions.
- Using the TREND and GROWTH functions, make a forecast of the enterprise’s profit for 2003 and 2004.
- Construct a diagram for the original data and the resulting data series.
The solution of the problem
Let's use the worksheet for Problem 1 (see Fig. 4). Let's start with the TREND function:
- select the range of cells D4:D11, which should be filled with the values of the TREND function corresponding to the known data on the profit of the enterprise;
- Call the Function command from the Insert menu. In the Function Wizard dialog box that appears, select the TREND function from the Statistical category, and then click the OK button. The same operation can be performed by clicking the (Insert Function) button on the standard toolbar.
- In the Function Arguments dialog box that appears, enter the range of cells C4:C11 in the Known_values_y field; in the Known_values_x field - the range of cells B4:B11;
- To make the entered formula become an array formula, use the key combination + + .
The formula we entered in the formula bar will look like: =(TREND(C4:C11,B4:B11)).
As a result, the range of cells D4:D11 is filled with the corresponding values of the TREND function (Fig. 9).
To make a forecast of the enterprise's profit for 2003 and 2004. necessary:
- select the range of cells D12:D13 where the values predicted by the TREND function will be entered.
- call the TREND function and in the Function Arguments dialog box that appears, enter in the Known_values_y field - the range of cells C4:C11; in the Known_values_x field - the range of cells B4:B11; and in the New_values_x field - the range of cells B12:B13.
- turn this formula into an array formula using the key combination Ctrl + Shift + Enter.
- The entered formula will look like: =(TREND(C4:C11;B4:B11;B12:B13)), and the range of cells D12:D13 will be filled with the predicted values of the TREND function (see Fig. 9).
The data series is similarly filled in using the GROWTH function, which is used in the analysis of nonlinear dependencies and works in exactly the same way as its linear counterpart TREND.
Figure 10 shows the table in formula display mode.
For the initial data and the obtained data series, the diagram shown in Fig. eleven.
Problem 4
With the table of data on the receipt of applications for services by the dispatch service of a motor transport enterprise for the period from the 1st to the 11th of the current month, you must perform the following actions.
- Get data series for linear regression:using the SLOPE and CUT functions; using the LINEST function.
- Obtain a series of data for exponential regression using the LGRFPRIBL function.
- Using the above functions, make a forecast about the receipt of applications to the dispatch service for the period from the 12th to the 14th of the current month.
- Create a diagram for the original and received data series.
The solution of the problem
Note that, unlike the TREND and GROWTH functions, none of the functions listed above (SLOPE, INTERCEPT, LINEST, LGRFPRIB) are regression. These functions play only a supporting role, determining the necessary regression parameters.
For linear and exponential regressions built using the functions SLOPE, INTERCEPT, LINEST, LGRFPRIB, the appearance of their equations is always known, in contrast to linear and exponential regressions corresponding to the TREND and GROWTH functions.
1 . Let's build a linear regression with the equation:
y = mx+b
using the SLOPE and INTERCEPT functions, with the regression slope m determined by the SLOPE function, and the free term b by the INTERCEPT function.
To do this, we carry out the following actions:
- enter the original table into the cell range A4:B14;
- the value of parameter m will be determined in cell C19. Select the Slope function from the Statistical category; enter the range of cells B4:B14 in the known_values_y field and the range of cells A4:A14 in the known_values_x field. The formula will be entered in cell C19: =SLOPE(B4:B14,A4:A14);
- Using a similar technique, the value of parameter b in cell D19 is determined. And its contents will look like: =SEGMENT(B4:B14,A4:A14).Thus, the values of the parameters m and b required for constructing a linear regression will be stored in cells C19, D19, respectively;
- Next, enter the linear regression formula in cell C4 in the form: =$C*A4+$D. In this formula, cells C19 and D19 are written with absolute references (the cell address should not change during possible copying). The absolute reference sign $ can be typed either from the keyboard or using the F4 key, after placing the cursor on the cell address. Using the fill handle, copy this formula into the range of cells C4:C17. We obtain the required data series (Fig. 12). Due to the fact that the number of requests is an integer, you should set the number format with the number of decimal places to 0 on the Number tab of the Cell Format window.
2 . Now let's build a linear regression given by the equation:
y = mx+b
using the LINEST function.
For this:
- Enter the LINEST function as an array formula in the cell range C20:D20: =(LINEST(B4:B14,A4:A14)). As a result, we obtain the value of parameter m in cell C20, and the value of parameter b in cell D20;
- enter the formula in cell D4: =$C*A4+$D;
- copy this formula using the fill marker into the cell range D4:D17 and get the desired data series.
3 . We build an exponential regression with the equation:
y = bmx
using the LGRFPRIBL function it is performed similarly:
In the cell range C21:D21 we enter the LGRFPRIBL function as an array formula: =( LGRFPRIBL (B4:B14,A4:A14)). In this case, the value of parameter m will be determined in cell C21, and the value of parameter b will be determined in cell D21;
In Fig. Figure 13 shows a table where you can see the functions we use with the required cell ranges, as well as formulas.
For the initial data and the obtained data series, the diagram shown in Fig. 14.