Which trend line to choose in excel. Building a trend function in Excel. Quick forecast without seasonality

Looking at any set of data distributed over time (time series), we can visually determine the rise and fall of the indicators it contains. A pattern of rises and falls is called a trend, which can tell us whether our data is increasing or decreasing.

Perhaps I will start the series of articles on forecasting with the simplest thing - constructing a trend function. For example, let's take sales data and build a model that describes the dependence of sales on time.

Basic Concepts

I think everyone has been familiar with the linear function since school; it is precisely what underlies the trend:

Y(t) = a0 + a1*t + E

Y is the sales volume, the variable that we will explain by time and on which it depends, that is, Y(t);

t — period number ( serial number month), which explains the sales plan Y;

a0 is the zero regression coefficient, which shows the value of Y(t), in the absence of the influence of the explanatory factor (t=0);

a1 is the regression coefficient, which shows how much the studied sales indicator Y depends on the influencing factor t;

E are random disturbances that reflect the influence of other factors not taken into account in the model, except for time t.

Model building

So, we know the sales volume for the past 9 months. This is what our sign looks like:

The next thing we need to do is determine the coefficients a0 And a1 to forecast sales volume for the 10th month.

Determining Model Coefficients

We are building a schedule. Horizontally we see the deferred months, vertically the sales volume:

In Google Sheets we select Chart editor -> Additional and put a tick next to Trend lines. In the settings we select Label — Equation And Show R^2.

If you do everything in MS Excel, then right-click on the chart and select “Add trend line” from the drop-down menu.

By default, a linear function is built. On the right, select “Show equation on diagram” and “Value of approximation reliability R^2”.

Here's what happened:

On the graph we see the equation of the function:

y = 4856*x + 105104

It describes the sales volume depending on the month number for which we want to forecast these sales. Nearby we see the coefficient of determination R^2, which indicates the quality of the model and how well it describes our sales (Y). The closer to 1, the better.

I have R^2 = 0.75. This is an average indicator, it indicates that the model does not take into account any other significant factors besides time t, for example, it may be seasonality.

We predict

y = 4856*10 + 105104

We get 153664 sales in next month. If we add new point on the graph, we immediately see that R^2 has improved.

In this way, you can forecast data several months in advance, but without taking other factors into account, your forecast will lie on the trend line and will not be as informative as you would like. In addition, a long-term forecast made in this way will be very approximate.

You can improve the accuracy of the model by adding seasonality to the trend function, which we will do in the next article.

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 to find empirical coefficients linear function The most commonly used method is the least squares method. The essence of the method is that the linear “best fit” function passes through the graph points corresponding to the minimum 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 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 changes in the trend in a given time series.

Graphing

Regression analysis

Regression equation Y from X called functional dependence y=f(x), and its graph is a regression line.

Excel allows you to create charts and graphs of fairly acceptable quality. Excel available special remedy- Chart Wizard, which guides the user through all four stages of the process of creating a chart or graph.

As a rule, plotting begins by selecting a range containing the data on which it should be plotted. This start simplifies the further progress of plotting. However, the range with the original data can be divided at the second stage of the dialogue with DIAGRAM MASTER. In Excel 2003 DIAGRAM MASTER located in the menu as a button or a diagram can be created by clicking on the tab INSERT and in the list that opens find the item DIAGRAM. In Excel 2007 we also find the tab INSERT(Fig. 31).

Rice. 31. DIAGRAM MASTER in Excel 2007

The easiest way is to select a range of source data in which this data is located in adjacent rows (columns or rows) - you need to click on the upper left cell of the range and then drag the mouse pointer to the lower right cell of the range. When selecting data located in non-adjacent rows, drag the mouse pointer along the selected rows while pressing the Ctrl key. If one of the data series has a cell with a name, the remaining selected series must also have a corresponding cell, even if it is empty.

To carry out regression analysis It is best to use a Scatter diagram (Fig. 30). When building it, Excel perceives the first row of the selected range of source data as a set of argument values ​​of the functions whose graphs need to be plotted (the same set for all functions). The following rows are perceived as sets of values ​​of the functions themselves (each row contains the values ​​of one of the functions corresponding given values argument located in the first row of the selected range).

In Excel 2007, the axis names are placed in the menu tab LAYOUT(Fig. 32).

Rice. 32. Setting the names of graph axes in Excel 2007

To obtain a mathematical model, it is necessary to draw a trend line on the graph. In Excel 2003 and 2007, you need to right-click on the graph points. Then in Excel 2003 a tab will appear with a list of items from which we select ADD TREND LINE(Fig. 33).

Rice. 33. ADD TREND LINE

After clicking on the item ADD TREND LINE a window will appear TREND LINE(Fig. 34). In the TYPE tab, you can select the following line types: linear, logarithmic, exponential, power, polynomial, linear filtering.

Rice. 34. Window TREND LINE in Excel 2003

In the tab PARAMETERS(Fig. 35) check the box next to the items SHOW EQUATION ON DIAGRAM, then it will appear on the graph mathematical model this dependence. We also put a checkbox next to the item SHOW ON THE DIAGRAM THE VALUE OF RELIABILITY OF THE APPROXIMATION (R^2). The closer the approximation confidence value is to 1, the closer the selected curve approaches the points on the graph. Next, click on the button OK. A trend line, the corresponding equation and the approximation reliability value will appear on the graph.

Rice. 35. Tab PARAMETERS

In Excel 2007, after we right-click on the graph points, a list of menu items appears, from which SELECT ADD TREND LINE(Fig. 36).

Rice. 36. ADD TREND LINE

Rice. 37. Tab TREND LINE PARAMETERS

Check the required boxes and press the button CLOSE.

A trend line, the corresponding equation and the approximation reliability value will appear on the graph.

A trend is a pattern that describes the rise or fall of an indicator over time. If you depict any dynamic series (statistical data that is a list of recorded values ​​of a variable indicator over time) on a graph, a certain angle is often highlighted - the curve either gradually increases or decreases, in such cases it is customary to say that the dynamic series tends (towards rise or fall, respectively).

Trend as a model

If we build a model that describes this phenomenon, it turns out to be quite simple and very handy tool for forecasting that does not require any complex calculations or time spent on checking the significance or adequacy of influencing factors.

So, what is a trend as a model? This is a set of calculated equation coefficients that express the regression dependence of the indicator (Y) on the change in time (t). That is, this is exactly the same regression as those that we considered earlier, only the influencing factor here is the time indicator.

Important!

In calculations, t usually does not mean the year, month or week number, but rather the serial number of the period in the statistical population being studied - the time series. For example, if a time series is studied over several years, and the data was recorded monthly, then using a zero-based numbering of months, from 1 to 12 and again from the beginning, is fundamentally wrong. It is also incorrect if the study of a series begins, for example, in March, to use 3 (the third month of the year) as the value of t; if this is the first value in the population being studied, then its serial number should be 1.

Linear trend model

Like any other regression, a trend can be either linear (the degree of the influencing factor t is equal to 1) or nonlinear (the degree is greater or less than one). Because linear regression is the simplest, although not always the most accurate, we will consider this type of trend in more detail.

General form of the linear trend equation:

Y(t) = a 0 + a 1 *t + Ɛ

Where a 0 is a zero regression coefficient, that is, what Y will be if the influencing factor is equal to zero, a 1 is a regression coefficient that expresses the degree of dependence of the studied indicator Y on the influencing factor t, Ɛ is a random component or standard the error is essentially the difference between the actual Y values ​​and the calculated ones. t is the only influencing factor – time.

The more pronounced the tendency for the indicator to grow or fall, the greater the coefficient a 1 will be. Accordingly, it is assumed that the constant a 0 together with the random component Ɛ reflects the remaining regression influences, in addition to time, that is, all other possible influencing factors.

You can calculate the model coefficients standard method least squares (LSS). With all these calculations Microsoft Excel copes with a bang on its own, and in order to get a linear trend model or a ready-made forecast, there are as many as five methods, which we will discuss separately below.

Graphical method of obtaining a linear trend

In this and all further examples, we will use the same dynamic series - the level of GDP, which is calculated and recorded annually; in our case, the study will take place over the period from 2004 to 2012.

Let's add one more column to the original data, which we will call t and mark in ascending numbers the serial numbers of all recorded GDP values ​​for the specified period from 2004 to 2012. – 9 years or 9 periods.

Excel will add an empty field - markup for the future graph, select this graph and activate the tab that appears in the menu bar - Constructor, looking for a button Select data, in the window that opens, press the button Add. A pop-up window will prompt you to select data to create a chart. As field value Series name select the cell that contains the text that best matches the name of the graph. In the field X values indicate the interval of cells in column t – the influencing factor. In the field Y values We indicate the interval of column cells with known values ​​of GDP (Y) - the indicator under study.

Having filled in the specified fields, press the OK button several times and get a ready-made dynamics graph. Now select the graph line itself with the right mouse button and from the appeared context menu select an item Add a trend line

A window will open to configure the parameters for constructing a trend line, where among the model types we select Linear, put a tick next to items P render an equation on a diagram And Place the approximation reliability value R2 on the diagram, this will be enough for the already constructed trend line to be displayed on the graph, as well as a mathematical version of displaying the model in the form of a ready-made equation and an indicator of the quality of the model R 2. If you are interested in displaying the forecast on a graph in order to visually assess the gap between the indicator under study, indicate in the field Forecast ahead for number of periods of interest.

Actually, that’s all about this method, you can of course add that the displayed linear trend equation is the model itself, which can be used as a formula to obtain calculated values ​​​​from the model and, accordingly, accurate forecast values ​​(the forecast displayed on the graph, can only be estimated approximately), which is what we did in the example attached to the article.

Building a linear trend using the LINEST formula

The essence of this method comes down to searching for linear trend coefficients using the function LINEST, then, substituting these influencing coefficients into the equation, we obtain a predictive model.

We will need to select two adjacent cells (in the screenshot these are cells A38 and B38), then in the formula bar at the top (highlighted in red in the screenshot above) we call the function by writing “=LINEST(”, after which Excel will display hints on what is required for this functions, namely:

1. select a range with known values ​​of the described indicator Y (in our case, GDP, in the screenshot the range is highlighted in blue) and put a semicolon
2. indicate the range of influencing factors X (in our case this is the t indicator, the serial number of periods, highlighted in green in the screenshot) and put a semicolon
3. the next required parameter for the function is determining whether the constant needs to be calculated, since we initially consider a model with a constant (coefficient a 0 ), then put either “TRUE” or “1” and a semicolon
4. Next, we need to indicate whether calculation of statistics parameters is required (if we were considering this option, we would initially have to allocate a range “for the formula” a few lines below). Indicate the need to calculate statistical parameters, namely standard error value for coefficients, coefficient of determinism, standard error for Y, Fisher criterion, degrees of freedom, etc., they only make sense when you understand what they mean, in which case we set either “TRUE” or “1”. In the case of simplified modeling, which we are trying to learn, at this stage of writing the formula, set “FALSE” or “0” and add after the closing bracket “)”
5. to “revive” the formula, that is, to make it work after specifying all the necessary parameters, it is not enough to press the Enter button, you must press three keys in sequence: Ctrl, Shift, Enter

As you can see in the screenshot above, the cells we selected for the formula were filled with the calculated values ​​of the regression coefficients for the linear trend, in the cell B38 the coefficient is found a 0 , and in the cell A38- coefficient of dependence on the parameter t (or x ), that is a 1 . We substitute the obtained values ​​into the equation of the linear function and get finished model in mathematical expression – y = 169,572.2+138,454.3*t

To get calculated values Y according to the model and, accordingly, to get a forecast, you just need to substitute the formula in an Excel cell, and instead t indicate a link to the cell with the required period number (see cell in the screenshot D25).

To compare the resulting model with real data, you can build two graphs, where as X you indicate the serial number of the period, and as Y, in one case - real GDP, and in the other - calculated (in the screenshot, the diagram on the right).

Building a linear trend using the Regression tool in the Analysis Package

The article, in fact, fully describes this method, the only difference is that in our initial data there is only one influencing factor X (period number – t ).

As you can see in the picture above, range of data with known GDP values highlighted as input interval Y, and the corresponding one range with period numbers t – as input interval X. The results of calculations using the Analysis Package are presented to separate sheet and looks like a set of tables (see figure below) in which we are interested in the cells that I painted in yellow and green colors. By analogy with the procedure described in the above article, a linear trend model is assembled from the obtained coefficients y=169 572.2+138 454.3*t, on the basis of which forecasts are made.

Forecasting using a linear trend via the TREND function

This method differs from the previous ones in that it skips the previously necessary steps of calculating the model parameters and manually substituting the obtained coefficients as a formula into a cell to obtain a forecast; this function precisely produces a ready-made calculated forecast value based on known source data.

In the target cell (the cell where we want to see the result) we put a sign equals and call the magic function by writing “ TREND(", then you need to highlight , that is, after we put a semicolon and select a range with known X values, that is, with period numbers t, which correspond to a column with known GDP values, again put a semicolon and select the cell with the number of the period for which we are making a forecast (however, in our case, the period number can be indicated not by reference to the cell, but simply by a number directly in the formula), then put another semicolon and indicate TRUE or 1 , as confirmation for calculating the coefficient a 0 finally we put closing parenthesis and press the key Enter.

The disadvantage of this method is that it does not show either the model equation or its coefficients, which is why we cannot say that based on such and such a model we received such and such a forecast, just as there is no reflection of the quality parameters of the model , the coefficient of determination by which one could say whether it makes sense to take the resulting forecast into account or not.

Forecasting using a linear trend using the FORECAST function

The essence of this function is completely identical to the previous one, the only difference is in the order in which the initial data is written in the formula and in the fact that there is no setting for the presence or absence of a coefficient a 0 (that is, the function implies that this coefficient exists in any case)

As you can see from the figure above, we write “ =PREDICTION("and then indicate cell with period number, for which it is necessary to calculate the value by linear trend, that is, the forecast, after we put a semicolon, then we highlight range of known Y values, that is column with known GDP values, then put a semicolon and highlight range with known X values, that is with period numbers t, which correspond to the column with known GDP values ​​and, finally, we set closing parenthesis and press the key Enter.

The results obtained, as in the method above, are only the finished result of calculating the predicted value using a linear trend model; it does not show any errors or the model itself in mathematical terms.

To summarize the article

We can say that each of the methods can be the most acceptable among others, depending on the current goal that we set for ourselves. The first three methods intersect with each other both in meaning and in results, and are suitable for any more or less serious work where a description of the model and its quality is necessary. In turn, the last two methods are also identical to each other and will give you an answer as quickly as possible, for example, to the question: “What is the sales forecast for next year?”

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:

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 type 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 extrema 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

Previous12345678910111213141516Next

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 schedule 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 R 2 . If there are no other theoretical considerations, then choose a function with a coefficient R 2 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=c 0 +c 1 x+c 2 x 2 +…+c 6 x 6. 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=cx b, (X>0i y>0). The function reflects data with a constantly increasing (decreasing) growth rate.

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