As in excel to construct a trend

MS Excel, even without being a full-fledged statistics package, has a rather large range of possibilities in forecasting events based on existing data. One of the most simple, at first glance, the method of prediction is the construction of the trend line.

Instruction

The easiest way to graph the trend immediately after any existing data in the array. On the sheet with the data table select at least two cells of the range for which the chart will be built, and immediately after that insert the chart. You can use these types of diagrams as graph, scatter, histogram, bubble, stock. Other types of charts do not support trending.

In the menu "Chart", select "Add trendline". In the window that opens on the tab "Type" select the type of trendline that in the mathematical equivalent means and also method of data fitting. When using the described method you will have to do it "by eye", since no mathematical calculations to plot you are carried out.

So just think about what type of function best describes the graph of the available data: linear, logarithmic, exponential, power law or another. If you are in doubt about choosing the type of approximation, can construct several lines, and for greater forecast accuracy on the Parameters tab of the same window check the "place on the chart the value of reliability approximation (R^2)".

Comparing the values of R^2 for different lines, you will be able to choose the type of graph that characterizes your data most accurately, and therefore builds the most reliable forecast. The closer the value of R^2 is to unity, the more precisely you choose the type of line. Here, on the Settings tab, you must specify the period for which the forecast.

This method of trend construction is very rough, so it is better to produce at least the most primitive statistical processing of available data. This will help to build a forecast more accurately.

If you assume that the available data are described by a linear equation, simply highlight them with the cursor and generate auto-complete for the desired number of periods or the number of cells. In this case there is no need to find the value of R^2, because you previously adjusted the forecast to the equation of the line.

If you think that the known value of the variable can best be described by exponential equations, and also select source range and make the autocomplete of the required number of cells, hold down the right mouse button. With autocomplete you will not be able to build other types of lines, in addition to these two.

Therefore, for most accurate forecasting you will have to use one of several aggregate functions: "forecast", "TREND", "GROWING", "linear" or "lehrprobe". In this case, you will have to calculate the value for each subsequent forecast period manually. If you need to make a more complex regression analysis of the data, you will need the add-in "analysis ToolPak", which is not included in the standard installation of MS Office.

Is the advice useful?