Construct a linear trend. Building a trend function in Excel. Quick forecast without seasonality

Most often trend seems linear dependence of the type being studied

where y is the variable of interest (for example, productivity) or the dependent variable;
x is a number that determines the position (second, third, etc.) of the year in the forecasting period or an independent variable.

When linearly approximating the relationship between two parameters, the least squares method is most often used to find the empirical coefficients of a linear function. The essence of the method is that linear function“best fit” passes through the points of the graph corresponding to the minimum of the sum of squared deviations of the measured parameter. This condition looks like:

where n is the volume of the population under study (the number of observation units).

Rice. 5.3. Building a trend using the least squares method

The values ​​of the constants b and a or the coefficient of the variable X and the free term of the equation are determined by the formula:

In table 5.1 shows an example of calculating a linear trend from data.

Table 5.1. Linear trend calculation

Methods for smoothing oscillations.

If there are strong discrepancies between neighboring values, the trend obtained by the regression method is difficult to analyze. When forecasting, when a series contains data with a large spread of fluctuations in neighboring values, you should smooth them out according to certain rules, and then look for the meaning in the forecast. To the method of smoothing oscillations
include: moving average method (n-point average is calculated), exponential smoothing method. Let's look at them.

Moving Average Method (MAM).

MSS allows you to smooth out a series of values ​​in order to highlight a trend. This method takes the average (usually the arithmetic mean) of a fixed number of values. For example, a three-point moving average. The first three values, compiled from data for January, February and March (10 + 12 + 13), are taken and the average is determined to be 35: 3 = 11.67.

The resulting value of 11.67 is placed in the center of the range, i.e. according to the February line. Then we “slide by one month” and take the second three numbers, starting from February to April (12 + 13 + 16), and calculate the average equal to 41: 3 = 13.67, and in this way we process the data for the entire series. The resulting averages represent new row data for constructing a trend and its approximation. The more points are taken to calculate the moving average, the stronger the smoothing of fluctuations occurs. An example from MBA of trend construction is given in table. 5.2 and in Fig. 5.4.

Table 5.2 Trend calculation using the three-point moving average method

The nature of fluctuations in the original data and data obtained by the moving average method is illustrated in Fig. 5.4. From a comparison of the graphs of the series of initial values ​​(series 3) and three-point moving averages (series 4), it is clear that the fluctuations can be smoothed out. How larger number points will be involved in the range of calculation of the moving average, the more clearly the trend will emerge (row 1). But the procedure of enlarging the range leads to a reduction in the number of final values ​​and this reduces the accuracy of the forecast.

Forecasts should be made based on estimates of the regression line based on the values ​​of the initial data or moving averages.

Rice. 5.4. The nature of changes in sales volume by month of the year:
initial data (row 3); moving averages (row 4); exponential smoothing (row 2); trend constructed by regression method (row 1)

Exponential smoothing method.

An alternative approach to reducing the spread of series values ​​is to use the exponential smoothing method. The method is called “exponential smoothing” due to the fact that each value of periods going into the past is reduced by a factor (1 – α).

Each smoothed value is calculated using a formula of the form:

St =aYt +(1−α)St−1,

where St is the current smoothed value;
Yt – current value of the time series; St – 1 – previous smoothed value; α is a smoothing constant, 0 ≤ α ≤ 1.

The smaller the value of the constant α, the less sensitive it is to trend changes in a given time series.

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, solution differential equations etc.;
• if it is necessary to 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 predicting.

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 regression analysis).

Excel has two options for building regressions.

1. 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);
2. Using the worker's built-in statistical functions Excel sheet, allowing you to obtain regressions (trend lines) directly based on 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.

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 angle of inclination linear regression to the abscissa 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 the 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 . The power trend line gives good results, if the values ​​of the dependence 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 line Excel trend 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 appeared context menu 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.

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.

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.

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 that can be used for this purpose, but all of them only allow for 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.

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.

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 specific examples show the capabilities of the Excel package when solving approximation problems; demonstrate what effective tools Excel is used 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 consider solving specific problems using the listed tools in the Excel package.

Problem 1

With a table of data on the profit of a motor transport enterprise for 1995-2002. you need to do the following:

1. Build a diagram.
2. Add linear and polynomial (quadratic and cubic) trend lines to the chart.
3. Using the trend line equations, obtain tabular data on enterprise profits for each trend line for 1995-2004.
4. Make a forecast for the enterprise's profit for 2003 and 2004.

Problem solution

1. In the range of cells A4:C11 of the Excel worksheet, enter the worksheet shown in Fig. 4.
2. Having selected the range of cells B4:C11, we build a diagram.
3. 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.
4. 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, enter in the cells of the range D3:F3 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 c relative links 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

1. Build a diagram.
2. Add logarithmic, power and exponential trend lines to the chart.
3. Derive the equations of the obtained trend lines, as well as the reliability values ​​of the approximation R2 for each of them.
4. Using the trend line equations, obtain tabular data on the enterprise's profit for each trend line for 1995-2002.
5. Make a forecast of the company's profit for 2003 and 2004 using these trend lines.

Problem solution

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.

1. Obtain data series for linear and exponential trend lines using the TREND and GROW functions.
2. Using the TREND and GROWTH functions, make a forecast of the enterprise’s profit for 2003 and 2004.
3. Construct a diagram for the original data and the resulting data series.

Problem solution

Let's use the worksheet for Problem 1 (see Fig. 4). Let's start with the TREND function:

1. 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;
2. 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 pressing the button (Insert function) standard panel tools.
3. 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;
4. To make the entered formula become an array formula, use the key combination + + .

As a result, the range of cells D4:D11 is filled with the corresponding values ​​of the TREND function (Fig. 9).

1. select the range of cells D12:D13 where the values ​​predicted by the TREND function will be entered.
2. 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.
3. turn this formula into an array formula using the combination Ctrl keys+ Shift + Enter.
4. 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. 11.

Problem 4

With a 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 current month you need to do the following:

1. Get data series for linear regression:using the SLOPE and CUT functions; using the LINEST function.
2. Get a series of data for exponential regression using the LGRFPRIBL function.
3. 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.
4. Create a diagram for the original and received data series.

Problem solution

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, appearance their equations are 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

To do this, we carry out the following actions:

1. enter the original table into the cell range A4:B14;
2. 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);
3. 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;
4. 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 applications 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.

y = mx+b

To do this:

1. Enter the LINEST function into the range of cells C20:D20 as an array formula: =(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;
2. enter the formula in cell D4: =$C*A4+$D;
3. copy this formula using the fill marker into the cell range D4:D17 and get the desired data series.

y = bmx

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.

What should I do if there are no timing measurements for certain volumes/sizes of products? Or is the number of measurements insufficient, and additional observations cannot be made in the near future? The best way The solution to this problem is to build calculated dependencies (regression equations) using trend lines in MS Excel.

Let's consider a real situation: in a warehouse, in order to establish the amount of labor costs for box picking of an order, timing observations. The results of these observations are presented in Table 1 below.

Subsequently, the need arose to determine the time required to select 0.6 and 0.9 m3 of goods/order. Due to the impossibility of conducting additional timing studies, the time spent on selecting these order volumes was calculated using regression equations in MS Excel. To achieve this, Table 1 was converted to Table 2.

Selecting a scatter plot, fig. 1

Next step: the mouse cursor was placed on one of the points on the chart and the context menu was called up using the right mouse button, in which the item was selected: “add trend line” (Fig. 2).

Adding a trend line, fig. 2

In the window that appears for setting the trend line format (Fig. 3) were sequentially selected: linear/power line type and the following items were checked: “show the equation on the diagram” and “place the approximation reliability value (R^2) on the diagram” (coefficient of determination).

Trend line format, fig. 3

As a result, the graphs presented in Fig. 1 were obtained. 4 and 5.

Linear calculated dependence, Fig. 4

Power-law calculated dependence, Fig. 5

A visual analysis of the graphs clearly indicates the closeness of the obtained dependencies. In addition, the approximation reliability value (R^2), which is also called the coefficient of determination, in the case of both dependencies is the same value of 0.97. It is known that the closer the coefficient of determination is to 1, the more the trend line corresponds to reality. It can also be stated that 97% of the change in time spent on order processing is explained by a change in the quantity of goods. Therefore in in this case not important: which calculated dependence should be chosen as the main one for the subsequent calculation of time costs.

Let us take the linear calculated dependence as the main one. Then the values ​​of time spent depending on the quantity of goods will be determined by the formula: y = 54.511x + 0.1489. The results of these calculations for the quantity of goods for which timing observations were previously carried out are presented in Table 3 below.

Let's determine the average deviation of the time spent calculated using the regression equation from the time spent calculated from time-keeping observations: (-0.05+0.10-0.05+0.01)/4=0.0019. Thus, the time expenditure calculated using the regression equation differs from the time expenditure calculated from time-keeping data by only 0,19%. The discrepancy between the data is negligible.

Using the formula: y = 54.511x + 0.1489, we will establish the time costs for the quantity of goods for which timing observations have not previously been carried out (Table 4).

Thus, constructing calculated dependencies using trend lines in MS Excel - This great way establishing the time spent on operations that, for various reasons, were not covered by time-keeping observations.

• petal;
• circular;
• surface;
• annular;
• volume;
• with accumulation.

Linear approximation

We get the result:

y = 4.503x + 6.1333

• 6.1333 – displacements;
• x – period number.

y = 7.6403е^-0.084x

For example:

One of the important components of any analysis is to determine the main trend of events. With this data you can make a forecast further development situations. This is especially clearly seen in the example of a trend line on a chart. Let's find out how in the program Microsoft Excel it can be built.

Trend line in Excel

The Excel application provides the ability to plot a trend line using a graph. In this case, the initial data for its formation are taken from a pre-prepared table.

Plotting a graph

In order to build a graph, you need to have a ready-made table on the basis of which it will be formed. As an example, let's take data on the value of the dollar in rubles for a certain period of time.

1. We are building a table where one column will contain time periods (in our case, dates), and the other will contain a value whose dynamics will be displayed in a graph.
2. Select this table. Go to the “Insert” tab. There, on the ribbon in the “Diagrams” tool block, click on the “Graph” button. From the list presented, select the very first option.
3. After this, the schedule will be built, but it still needs to be finalized. Let's create a title for the graph. To do this, click on it. In the “Working with Charts” tab group that appears, go to the “Layout” tab. In it, click on the “Chart Name” button. In the list that opens, select “Above the diagram”.
4. In the field that appears above the graph, enter the name that we consider appropriate.
5. Then we label the axes. In the same “Layout” tab, click on the button on the “Axes Titles” ribbon. We sequentially go through the items “Name of the main horizontal axis” and “Name under the axis”.
6. In the field that appears, enter the name of the horizontal axis, according to the context of the data located on it.
7. In order to assign a name to the vertical axis, we also use the “Layout” tab. Click on the “Axis names” button. We sequentially move through the pop-up menu items “Name of the main vertical axis” and “Rotated name”. It is this type of location of the axis name that will be most convenient for our type of charts.
8. In the vertical axis name field that appears, enter the desired name.

Lesson: How to make a graph in Excel

Creating a trend line

Now we need to directly add a trend line.

1. While in the “Layout” tab, click on the “Trend Line” button, which is located in the “Analysis” tool block. From the list that opens, select “Exponential approximation” or “Linear approximation”.
2. After this, a trend line is added to the chart. By default it is black.

Setting up a trend line

There is a possibility additional settings lines.

1. Sequentially go to the “Layout” tab under the menu items “Analysis”, “Trend line” and “ Additional options trend lines..."
2. The parameters window opens, you can make various settings. For example, you can change the type of smoothing and approximation by selecting one of six options:
   • Polynomial;
   • Linear;
   • Power;
   • Logarithmic;
   • Exponential;
   • Linear filtering.

   In order to determine the reliability of our model, check the box next to the item “Place the approximation reliability value on the diagram.” To view the result, click on the “Close” button.

   If this indicator is equal to 1, then the model is as reliable as possible. The further the level is from one, the lower the reliability.

If you are not satisfied with the level of confidence, you can return to the parameters again and change the type of smoothing and approximation. Then, form the coefficient again.

Forecasting

The main task of a trend line is the ability to make a forecast of further developments from it.

1. Let's go to the parameters again. In the “Forecast” settings block, in the appropriate fields we indicate how many periods forward or backward we need to continue the trend line for forecasting. Click on the “Close” button.
2. Let's go back to the schedule again. It shows that the line is lengthened. Now you can use it to determine what approximate indicator is predicted for a certain date if the current trend continues.

As you can see, it is not difficult to build a trend line in Excel. The program provides tools so that it can be configured to display indicators as correctly as possible. Based on the graph, you can make a forecast for a specific time period.

We are glad that we were able to help you solve the problem.

Ask your question in the comments, describing the essence of the problem in detail. Our specialists will try to answer as quickly as possible.

Did this article help you?

Why are diagrams needed? To “make it beautiful”? Not at all - the main task of the diagram is to allow you to present obscure numbers in an easy-to-digest graphical form. So that the state of affairs is clear at a glance, and there is no need to waste time studying dry statistics.

Another huge advantage of diagrams is that with their help it is much easier to show trends, that is, to make a forecast for the future. In fact, if things have been going well all year, there is no reason to think that the picture will suddenly reverse in the next quarter.

How charts and graphs deceive us

However, diagrams (especially when it comes to visually representing large amounts of data), although extremely easy to understand, are not always obvious.

I will illustrate my words with a simple example:

A diagram based on a table in MS Excel

This table shows the average number of visitors to a certain site per day by month, as well as the number of page views per visitor. It is logical that there should always be more page views than visitors, since one user can view several pages at once.

It is no less logical that than more pages the visitor views, the better the site - it captures the user's attention and forces him to go deeper into reading.

What does the site owner see from our diagram? That things are going well for him! There was a seasonal decline in interest in the summer months, but in the fall the numbers returned and even exceeded those of the spring. Conclusions? We continue in the same spirit and will soon achieve success!

Is the diagram clear? Quite. But is it obvious? Let's figure it out.

Understanding trends in MS Excel

A big mistake on the part of the site owner would be to take the diagram as is. Yes, the naked eye can see that the blue and orange columns of “autumn” have grown compared to “spring” and even more so “summer”. However, not only the numbers and the size of the bars are important, but also the relationship between them. That is, ideally, with overall growth, the “orange” columns of views should grow much stronger than the “blue” ones, which would mean that the site not only attracts more readers, but also becomes larger and more interesting.

What do we see on the graph? The orange columns of “autumn” are at least no more than “spring” ones, if not less. This does not indicate success, but rather the opposite - visitors arrive, but read less on average and do not stay on the site!

It's time to sound the alarm and... get acquainted with such a thing as trend line.

Why is a trend line needed?

A trend line “in simple terms” is a continuous line drawn up on the basis of values ​​averaged based on special algorithms from which our chart is constructed. In other words, if our data “jumps” over three reporting points from “-5” to “0”, and then to “+5”, we will end up with an almost flat line: the “pros” of the situation obviously balance the “cons”.

Based on the direction of the trend line it is much easier to see real situation affairs and see those same trends, and therefore make forecasts for the future. Well, now, let's get to work!

How to draw a trend line in MS Excel

Adding to the diagram in MS Excel line trend

Right-click on one of the “blue” columns and select the item from the context menu "Add trend line".

The chart sheet now displays dotted line trend. As you can see, it does not coincide 100% with the values ​​of the diagram - built using weighted averages, it only roughly repeats its direction. However, this does not prevent us from seeing a steady increase in the number of visits to the site - even the “summer” drawdown does not affect the overall result.

Trend line for the Visitors column

Now let's repeat the same trick with the “orange” columns and build a second trend line. As I said before: the situation here is not so good. The trend clearly shows that during the calculation period the number of views not only did not increase, but even began to fall - slowly but steadily.

Another trend line helps clarify the situation

Mentally continuing the trend line for future months, we will come to a disappointing conclusion - the number of interested visitors will continue to decline. Since users do not stay here, a drop in interest in the site in the near future will inevitably cause a drop in traffic.

Consequently, the project owner urgently needs to remember what he did in the summer (“in the spring” everything was quite normal, judging by the schedule), and urgently take measures to correct the situation.

To predict an event based on existing data, if there is no time, you can use a trend line. Using it, you can visually understand what dynamics the data from which the graph is built has. The Microsoft software package has a wonderful Excel feature that will help you create a fairly accurate forecast using this tool - a trend line in Excel. Building this analysis tool is quite simple, below is given detailed description process and types of trend lines.

Trend line in Excel. Construction process

Trend line is one of the main data analysis tools

To form a trend line, it is necessary to complete three stages, namely:
1. Create a table;
2. Construct a diagram;
3. Select the type of trend line.

After collecting all necessary information, you can proceed directly to the steps towards obtaining the final result.

First, you should create a table with the source data. Next, select the required range and, going to the “Insert” tab, select the “Graph” function. After construction, you can apply to the final result additional features, in the form of headings and captions. To do this, simply left-click on the chart, select the tab called “Designer” and select “Layout”. The next thing you need to do is simply enter the title.

The next step is to construct the trend line itself. So, to do this, you need to highlight the chart again and select the “Layout” tab on the task ribbon. Next in this menu you need to click on the “Trend Line” button and select “linear approximation” or “exponential approximation”.

Various l variationstrend lines

Depending on the characteristics of the data entered by the user, it is worth choosing one of the presented options; below is a description of the types of trend lines
Exponential approximation. If the rate of change of the input data increases, and continuously, then it is this line will be most useful. However, if the data that was entered into the table contains zero or negative characteristics, this type unacceptable.

Linear approximation. This line is straight in nature, and is usually used in elementary cases when the function increases or decreases at approximately constant speed.

Logarithmic approximation. If the value initially grows correctly and quickly, or, on the contrary, decreases, but then, after some values, it stabilizes, then this trend line will come in handy.

Polynomial approximation. Variable increase and decrease are the characteristics that are characteristic of this line. Moreover, the degree of the polynomials (polynomials) themselves is determined by the number of maxima and minimums.

Power approximation. Characterizes the monotonic increase and decrease of a value, but its application is impossible if the data has negative and zero values.

Moving average. It is used to clearly show the direct dependence of one on the other, by smoothing all oscillation points. This is achieved by taking the average value between two adjacent points. Thus, the graph is averaged, and the number of points is reduced to the value that was selected in the “Points” menu by the user.

How is it used? To forecast economic options, it is a polynomial line that is used, the degree of the polynomial of which is determined on the basis of several principles: maximizing the coefficient of determination, as well as the economic dynamics of the indicator during the period for which the forecast is required.

By following all the stages of formation and understanding the features, you can build just a primary trend line, which only vaguely corresponds to real forecasts. But after setting the parameters, we can already talk about a more realistic picture of the forecast.

Trend line in Excel. Parameter settingsin the functional line

By clicking on the “Trend Line” button, select the required menu called “Advanced options”. In the window that appears, click on “Format trend line”, and then check the box next to the value “place the approximation reliability value R^2 on the diagram”. After that, close the menu by clicking on the corresponding button. On the diagram itself, the coefficient R^2 = 0.6442 appears.

After this, we cancel the changes made. By selecting the chart and clicking on the “Layout” tab, then click on “Trend Line” and click on “No”. Next, going to the “Trend Line Format” function, click on the polynomial line and try to achieve the value R^2 = 0.8321 by changing the degree.

To view formulas or create other variations of forecasts that differ from standard ones, it is enough not to be afraid to experiment with values, and especially with polynomials. Thus, using only one Excel program, you can create a fairly accurate forecast based on the input data.

(Visited 10,510 times, 27 visits today)

Trend line in Excel on different charts

To visually illustrate price trends, a trend line is used. The element of technical analysis is geometric image average values ​​of the analyzed indicator.

Let's look at how to add a trend line to a chart in Excel.

Adding a trend line to a chart

For example, let's take average oil prices since 2000 from open sources. Let's enter the data for analysis into the table:

1. Let's build a graph based on the table. Select the range and go to the “Insert” tab. From the proposed chart types, choose a simple graph. Horizontal – year, vertical – price.
2. Right-click on the chart itself. Click “Add trend line”.
3. A window opens to configure line parameters. Let's choose the linear type and place the approximation reliability value on the graph.
4. An oblique line appears on the graph.

A trend line in Excel is a graph of a fitting function. Why is it needed - to make forecasts based on statistical data. For this purpose, it is necessary to extend the line and determine its values.

If R2 = 1, then the approximation error is zero. In our example, the choice of linear approximation gave low reliability and poor results. The forecast will be inaccurate.

Attention!!! Trend line cannot be added the following types graphs and diagrams:

• petal;
• circular;
• surface;
• annular;
• volume;
• with accumulation.

Trendline equation in Excel

In the above example, linear approximation was chosen only to illustrate the algorithm. As the reliability value showed, the choice was not entirely successful.

You should choose the display type that most accurately illustrates the trend in user input. Let's look at the options.

Linear approximation

Its geometric image is a straight line. Therefore, linear approximation is used to illustrate an indicator that increases or decreases at a constant rate.

Let's consider the conditional number of contracts concluded by the manager over 10 months:

Based on data in Excel spreadsheet Let's build a scatter plot (it will help illustrate the linear type):

Select the chart - “add trend line”. In the parameters, select linear type. Add the approximation confidence value and the trend line equation in Excel (just check the boxes at the bottom of the “Parameters” window).

We get the result:

Pay attention! With the linear type of approximation, the data points are located as close as possible to the straight line. This view uses the following equation:

y = 4.503x + 6.1333

• where 4.503 is the slope index;
• 6.1333 – displacements;
• y – sequence of values,
• x – period number.

The straight line on the graph shows a steady increase in the quality of the manager’s work. The approximation reliability value is 0.9929, which indicates a good agreement between the calculated line and the original data. Forecasts must be accurate.

To predict the number of contracts concluded, for example, in period 11, you need to substitute the number 11 instead of x into the equation. During the calculations, we find out that in the 11th period this manager will conclude 55-56 contracts.

Exponential trend line

This type is useful if the input values ​​change at a continuously increasing rate. Exponential fitting is not used when there are zero or negative characteristics.

Let's build an exponential trend line in Excel. Let’s take, for example, the conditional values ​​of productive electricity supply in region X:

We are building a schedule. Add an exponential line.

The equation looks like this:

y = 7.6403е^-0.084x

• where 7.6403 and -0.084 are constants;
• e is the base of the natural logarithm.

The approximation reliability indicator was 0.938 – the curve corresponds to the data, the error is minimal, the forecasts will be accurate.

Logarithmic trend line in Excel

It is used for the following changes in the indicator: first, rapid growth or decrease, then relative stability. The optimized curve adapts well to this “behavior” of the quantity. The logarithmic trend is suitable for forecasting sales of a new product that is just being introduced to the market.

On initial stage The manufacturer’s task is to increase the customer base. When a product has its own buyer, it needs to be retained and served.

Let's build a graph and add a logarithmic trend line to forecast sales of a conditional product:

R2 is close in value to 1 (0.9633), which indicates a minimal approximation error. Let's forecast sales volumes in subsequent periods. To do this, you need to substitute the period number in the equation instead of x.

For example:

To calculate the forecast figures, a formula of the form was used: =272.14*LN(B18)+287.21. Where B18 is the period number.

Polynomial trend line in Excel

This curve is characterized by variable increases and decreases. For polynomials (polynomials), the degree is determined (by the number of maximum and minimum values). For example, one extremum (minimum and maximum) is the second degree, two extrema are the third degree, three are the fourth.

Polynomial trend in Excel is used to analyze a large set of data about an unstable quantity. Let's look at the example of the first set of values ​​(oil prices).

To obtain such a value of approximation reliability (0.9256), it was necessary to set it to degree 6.

Download examples of charts with a trend line

But this trend allows us to make more or less accurate forecasts.

Greetings, dear comrades! Today we will look at one of the subjective trading methods - trading using trend lines. Let's look at the following questions:

1) What is a trend (this is important as a starting point)
2) Drawing trend lines
3) Use in practical trading
4) Subjectivity of the method

1) What is a trend
_________________
Before moving on to constructing a trend line, you need to understand the trend itself. We will not go into academic disputes and for simplicity we will accept the following formula:

A trend (upward) is a sequence of increasing highs and lows, with each subsequent high (and low) being higher than the previous ones.

A trend (downward) is a sequence of falling (decreasing) highs and lows, where each subsequent low (and high) is LOWER than the previous one.

A trend line is a line drawn between two highs (if the trend is downward) or two lows (if the trend is upward). That is, in essence, the trend line shows us that there is a trend on the chart! But it may not exist (in the case of a flat).

2) Drawing trend lines
____________________________

This is the most difficult question! I have seen discussions that lasted many pages just about HOW TO Draw a trend line CORRECTLY! But we need not only to build, but also to trade on it...

To build a trend line, you must have at least two maximums (downward trend) or two minimums (upward trend). We must connect these extreme points with a line.

It is important to follow the following rules when constructing lines:

The angle of the trend line is important. The steeper the angle of inclination, the less reliable it is.
- It is optimal to build a line using two points. If you build on three or more points, the reliability of the trend line decreases (its breakdown is likely).
- Do not try to build a line in any conditions. If you can’t draw it, then most likely there is no trend. Therefore, this instrument is not suitable for use in current market conditions.

These rules will help you build trend lines correctly!

3) Trading along trend lines
____________________________

We have two fundamentally different possibilities:
A) Use the line as a support (resistance) level to enter along it in the direction of the trend
B) Use the Forex trend line to play for a breakout (reversal) of the trend.

Both methods are good if you know how to “cook them correctly.”

So, we have built a line using two points. As soon as the price touches the line, we must enter the market in the direction of the existing trend. To enter, we use orders of the “buy limit or sell limit” type.

Everything here is simple and clear. The only thing you need to remember is that the more often the price tests the trend line, starting from it, the higher the likelihood that the next touch will be a breakout of the line!

If we want to play to break the trend line, then we need to act a little differently:
1) Wait for the line to touch
2) Waiting for a rebound
3) Place a buy stop order (or sell stop) on the resulting checkbox.
Pay attention to the picture.

We waited for the checkmark to appear and placed a buy stop order at its maximum.

After some time, the order was triggered and we entered the market.
Arises logical question– why couldn’t you enter the market right away?
The point is that we do not know whether testing the trend line will be successful or not. And by waiting for the “tick” we dramatically increase our chances of success (we weed out false signals).

4) Subjectivity of the method
_________________________

Does everything seem simple? In fact, using this method, we will face the following difficulties:
A) Line slope angle (you can always construct trend lines with different slopes.
B) What is considered a breakout of a trend line (how many points or percentage points should the price “break” the line to consider it a breakout)?
Q) When is a line considered “obsolete” and a new one built?

Pay attention to the picture.

The red line indicates one of the design options. An inexperienced trader could draw the line this way (and pay for it).

Practical experience is important in this matter. That is, it is not possible to reduce everything to a few simple rules construction. This is why there is no trend line indicator. More precisely, it may exist, but it builds them “crookedly” and incorrectly. This technique was initially “tailored” to the experience and skill of the trader.

Personally, I rarely use trend lines as independent instrument. But, nevertheless, I am talking about them for one simple reason. The fact is that many other traders use them. Therefore, we (you and I) must be aware of our competitors' techniques.

Whether this tool is needed in your trading is up to you to decide!

Good luck and happy trading. Arthur.
blog-forex.org

Related posts:

Trend trading concept (video)

Trending models (figures)

Video on this topic:

Part 10. Selection of formulas according to the schedule. Trend line

For the problems discussed above, it was possible to construct an equation or system of equations.

But in many cases, when solving practical problems, there is only experimental (measurement results, statistical, reference, experimental) data. Using them, with a certain degree of proximity, they try to reconstruct an empirical formula (equation), which can be used to find a solution, model, evaluate solutions, and make forecasts.

The process of selecting an empirical formula P(x) for experienced addiction F(x) called approximation(smoothing). For dependencies with one unknown, Excel uses graphs, and for dependencies with many unknowns, pairs of functions from the group Statistical LINEAR and TREND, LGRFPRIBL and GROWTH.

This section discusses the approximation of experimental data using Excel charts: based on the data, a graph is created and selected trend line , i.e. an approximating function that approaches the experimental dependence with the maximum degree of closeness.

The degree of similarity of the selected function is estimated coefficient of determination R2 . If there are no other theoretical considerations, then choose a function with a coefficient R2 tending to 1. Note that the selection of formulas using the trend line allows us to establish both the type of the empirical formula and determine the numerical values ​​of the unknown parameters.

Excel provides 5 types of approximation functions:

1. Linear – y=cx+b. This simplest function, reflecting the growth and decline of data at a constant rate.

2. Polynomial – y=c0+c1x+c2x2+…+c6x6. The function describes alternately increasing and decreasing data. A polynomial of the 2nd degree can have one extremum (min or max), a polynomial of the 3rd degree - up to 2 extrema, a polynomial of the 4th degree - up to 3, etc.

3. Logarithmic – y=c ln x+b. This function describes rapidly increasing (decreasing) data that then stabilizes.

4. Power – y=cxb, (X>0i y>0). The function reflects data with a constantly increasing (decreasing) growth rate.

5. Exponential – y=cebx, (e– the base of the natural logarithm). The function describes rapidly growing (decreasing) data, which then stabilizes.

For all 5 types of functions, data approximation using the least squares method is used (see help on F1 “trend line”).

As example Consider the dependence of sales on advertising, given by the following statistical data for a certain company:

You need to build a function in the best possible way reflecting this dependence. In addition, it is necessary to estimate sales for advertising investments at 6 thousand rubles.

Let's get to the solution. First of all, enter this data into Excel and build a graph, as in Fig. 38. As you can see, the graph is based on the range B2:J2. Next, right-click on the chart and add a trend line as shown in Fig. 38.

To label the X axis with the corresponding advertising values ​​(as in Fig. 38), select the item in the drop-down menu (Fig. 38) AND similar data. In the window of the same name that opens, in the tab Row, in the field P X axis labels, indicate the range of cells where the X values ​​are written (here $B$1:$K$1):

In the settings window that opens (Fig. 39), on the tab Type select to approximate logarithmic trend line (according to the type of chart). On the bookmark Options Select the checkboxes that display the equation and coefficient of determination on the graph.

After clicking OK You will get the result as in Fig. 40. Coefficient of determination R2= 0.9846, which is a good degree of closeness. To confirm the correctness of the chosen function (since there are no other theoretical considerations), forecast the development of sales for 10 periods in advance. To do this, right-click on the trend line - change the format - then in the field Forecast: forward by: set to 10 (Fig.

After setting the forecast, you will see a change in the graph curve for 10 observation periods ahead, as in Fig. 42. It most likely reflects a further increase in sales with increased advertising investment.

Calculation using the resulting formula =237.96*LN(6)+5.9606 in Excel gives a value of 432 thousand rubles.

Excel has a FORECAST() function that calculates a future Y value from existing pairs of X and Y values ​​using linear regression. The Y function should be linear if possible, i.e. be described by an equation like c+bx.

The prediction function for our example will be written as follows: =PREDICTION(K1,B2:J2,B1:J1). Write it down - the value should be 643.6 thousand rubles.

Part 11. Test tasks

Previous12345678910111213141516Next

To predict an event based on existing data, if there is no time, you can use a trend line. Using it, you can visually understand what dynamics the data from which the graph is built has. The Microsoft software package has a wonderful Excel feature that will help you create a fairly accurate forecast using this tool - trend line in Excel. Building this analysis tool is quite simple; below is a detailed description of the process and types of trend lines.

Trend line in Excel. Construction process

A trend line is one of the main data analysis tools

To form a trend line, it is necessary to complete three stages, namely:
1. Create a table;
2.
3. Select the type of trend line.

After collecting all the necessary information, you can proceed directly to completing the steps towards obtaining the final result.

First, you should create a table with the source data. Next, select the required range and, going to the “Insert” tab, select the “Graph” function. After construction, additional features can be applied to the final result in the form of headings and signatures. To do this, simply left-click on the chart, select the tab called “Designer” and select “Layout”. The next thing you need to do is simply enter the title.

The next step is to construct the trend line itself. So, to do this, you need to highlight the chart again and select the “Layout” tab on the task ribbon. Next in this menu you need to click on the “Trend line” button and select “linear approximation” or “exponential approximation”.

Various l variationstrend lines

Depending on the characteristics of the data entered by the user, it is worth choosing one of the presented options; below is a description of the types of trend lines
Exponential approximation. If the rate of change in the input data increases, and continuously, then this particular line will be most useful. However, if the data that was entered into the table contains zero or negative characteristics, this type is unacceptable.

Linear approximation. This line is straight in nature, and is usually used in elementary cases when the function increases or decreases at approximately constant speed.

Logarithmic approximation. If the value initially grows correctly and quickly, or, on the contrary, decreases, but then, after some value, stabilizes, then this trend line will come in handy.

Polynomial approximation. Variable increase and decrease are the characteristics that are characteristic of this line. Moreover, the degree of the polynomials (polynomials) themselves is determined by the number of maxima and minimums.

Power approximation. Characterizes the monotonic increase and decrease of a value, but its application is impossible if the data has negative and zero values.

Moving average. It is used to clearly show the direct dependence of one on the other, by smoothing all oscillation points. This is achieved by taking the average value between two adjacent points. Thus, the graph is averaged, and the number of points is reduced to the value that was selected in the “Points” menu by the user.

How is it used? D To predict economic options, it is precisely polynomial a line whose polynomial degree is determined based on several principles: maximizing the coefficient of determination, as well as the economic dynamics of the indicator during the period for which the forecast is required.

By following all the stages of formation and understanding the features, you can build just a primary trend line, which only vaguely corresponds to real forecasts. But after setting the parameters, we can already talk about a more realistic picture of the forecast.

Trend line in Excel. Parameter settingsin the functional line

By clicking on the “Trend line” button, select the required menu called “Advanced parameters”. In the window that appears, click on “Trend line format”, and then check the box next to the value “place the approximation reliability value R^2 on the diagram”. After that, close the menu by clicking on the corresponding button. On the diagram itself, the coefficient R^2 = 0.6442 appears.

After this, we cancel the changes made. By selecting the chart and clicking on the “Layout” tab, then click on “Trend line” and click on “No”. Next, going to the “Format trend line” function, click on polynomial line and try to achieve the value R^2= 0.8321 by changing the degree.

To view formulas or create other variations of forecasts that differ from standard ones, it is enough not to be afraid to experiment with values, and especially with polynomials. Thus, using only one Excel program, you can create a fairly accurate forecast based on the input data.