Contents
It is important to understand that Excel is not only a program for creating databases, but also a professional statistical tool. And in statistics there are many ways to handle numerical values. One of them is regression analysis. It is closely related to correlations. Before you figure out how to implement it in practice in Excel, you must first understand what regression analysis is and how it differs from correlation analysis.
The term “correlation” is familiar to many, even those who are not particularly well versed in statistics. It has already become so popular that it can often be heard in everyday life. And it means a very simple phenomenon – the relationship between two variables, when a change in one causes a change in the other.
It is important to understand that correlation itself establishes a pattern, but it does not indicate the nature of this pattern. That is, one variable can affect another, or maybe they have some third variable, a change in which entails a change in both at once. That is, correlation makes it possible to establish the relationship between phenomena, but not the influence of one on the other.
Linear regression allows you to just establish the type of this relationship, so that it becomes possible to predict the dependent variable depending on how the independent variable changes. Now let’s take a closer look at how you can feel like a visionary without closing an Excel document.
How to connect the analysis package in Excel
Right out of the box, regression analysis is not available to the user. It must first be enabled. Only in this case the user will be able to use these tools. To activate the regression analysis function, you must perform the following steps:
- Open the “File” menu. To do this, click on the button of the same name to the left of the “Home” tab.
- Next, we will open the file settings menu. We are interested in the “Parameters” tab.
- Now we have the opportunity to customize the Excel settings. Then go to the add-ons menu, set the Excel add-ins in the list below and click on “Go”.
- After that, a window appears in which you can manage existing add-ons. We are interested in the “Analysis Package” option. You need to check the box next to it and click on “OK”.
Now we have a new block of tools on the “Data” tab, in which the “Data Analysis” button has appeared.
And now we will describe in more detail what types of regression analysis are and how to implement it in Excel.
What are the types of regression analysis
There are several types of regressions:
- Parabolic.
- Power.
- Logarithmic.
- Exponential.
- Demonstrative.
- Hyperbolic.
- Linear regression.
Let’s take a closer look at the last variety in the Excel spreadsheet program.
Linear Regression in Excel
Let’s take a small example. Let’s say we have a file with a data range containing information about the average air temperature outside the window in a certain time period and how many buyers there were on the same day. To do this, you need to use regression analysis, having figured out exactly how climatic conditions (that is, air temperature) affect how this trading establishment is visited. To do this, we need to write a regression equation that looks like this: Y u0d a1 + a1xXNUMX + … + akhk. Let’s break down this data a bit.
- Y. Denotes a variable that is dependent on certain factors. That is what we need to analyze. In our example, this variable is the number of buyers.
- x is a set of factors that can change the value of a variable. In this case, it is the air temperature. But other values may be included that can be measured mathematically.
- a is the regression coefficient. It is necessary so that the formula can determine not only the presence of the factor itself, but also the degree of its influence on the variable Y.
- k is the total number of all factors that are currently available.
To perform a linear regression analysis, the following steps must be followed:
- Click on the “Data Analysis” button that appears after adding the corresponding add-on. It is located on the “Data” tab in the “Analysis” group.
- After that, a tiny dialog box will appear. But despite this, it contains a sufficient amount of information about which analysis tools can be used. We are also interested in regression. The corresponding item must be selected. Once it is highlighted, you can click the OK button.
- After that, we need to set up the regression. In the corresponding dialog box, it is mandatory to fill in the X and Y input intervals. If left blank, the default settings will be applied to the remaining parameters. In the field with the input interval Y, we write down the range in which the variables are located, for which we are trying to establish the influence of the available factors. In simple words, the total number of buyers. There are several ways to enter the address: from the keyboard or directly select them with the mouse. Naturally, the first option is simpler in most cases, but if a person knows the blind printing method and remembers the address of the range exactly, then manually it will still be easier for him.
Next, we enter the factors (more precisely, cells containing information about them) in the “Input interval X” field. As mentioned earlier, we are faced with the task of understanding how air temperature affects the number of customers. To do this, you need to write down the addresses of the cells included in the “Temperature” column. How to do it? That is exactly the same as with the previous field: enter manually or select the appropriate range with the mouse. 
As for other settings, they make it possible to set labels, the level of reliability of indicators, a constant-zero, and also set a number of other parameters. But in the vast majority of situations, there is no need to adjust these settings. The only thing to do is to set the correct radio button for the output option. According to the standard, the totals are displayed on another sheet, but the user can, if he so desires, output to the same sheet as the table with the original data. It is also possible to output the results to a separate book. Finally, after completing the settings, you need to click the “OK” button, after which the program will perform all the remaining actions on its own.
How to interpret the results of the analysis
You can get acquainted with the results of the regression analysis in the place that was specified in the parameters. It looks roughly like this.
The most important value that we will focus on is the R-square. It records the quality of the model used. The higher it is, the higher it is. If it is less than 0,5, then the dependence is considered bad, if it is higher, then it is already better. The closer to 1 the better. Accordingly, the maximum coefficient is 1.
You also need to pay attention to another important indicator. It can be found in the cell located at the junction of the Y-intersection row and the “Coefficients” column. Here you can see the value of Y, which will be zero under certain conditions. You can also understand how much our dependent variable is dependent on the factors. To do this, you need to see what figure is at the intersection of the column Variable X1 “and” Coefficients “. The higher the ratio, the better.
We see that the Microsoft Excel program opens up wide opportunities for regression analysis. But of course, additional training is needed to read these results. But if you already understand statistics, it will be much easier. Now let’s give some simple examples to make it clearer how linear regression works in practice.
Regression Analysis Example #1
And now it’s time to analyze practical cases of how linear regression can be used. Let’s say we have a set of data on the cost of TV advertising, Internet promotion, and how much it was possible to sell the goods in the national currency. All this data is packed into a table. Our task is to determine the regression coefficients for independent variables (that is, in our case, they are the cost of advertising on TV and on the Internet, since both values affect the volume of goods sold). The sequence of actions is as follows:
- Open a worksheet and enter data.
- Activate the regression tool as described above.
- In the dialog box that appears, you need to set the input interval X, Y, set labels
- Also, do not forget to enter the output interval. To complete this task, you must also specify options such as Normal Probability Plot and Residual Plot.
We see that for this case we do not need to fundamentally deviate from the scheme described above. Linear regression in this case allows you to reduce advertising costs and increase the return on it. That is, in marketing language, to increase ROMI – the ratio of return on investment in marketing.
Regression Analysis Example #2
The second case in which regression analysis can be carried out is the need to find the maximum model for distributing the costs of different types of advertising in order to get the highest profit. And such a marketing task could well be solved by ordinary Excel, who would have thought?
Suppose the maximum advertising budget that an organization can spend is 170000 rubles. This limitation cannot be provided by the standard means described above. Here you need to use a completely different add-on called “Search for a solution”. There is an opportunity to find it in the same section as the one described by us. And similarly to the analysis pack, we need to enable this add-on in the same menu.
What is the “Search for a Solution” tool? This is an add-on that allows you to find the best way to solve a specific problem. It has two main parameters: objective function and constraints. Thus, the user can find the optimal amount of expenses for advertising in certain conditions. This is one of the main advantages of this tool.
Just like in the case of the analysis package, the solution search tool requires a mathematical model. It is the objective function. In our case, it is as follows: Y = 2102438,6 + 6,4004 X1 – 54,068 X2 > max. The following expression is used as constraints: X1 + X2 <= 170000, X1>= 0, X2 >=0.
After using the Search for a Solution tool, it turns out that, given the given parameters and restrictions, it is optimal to spend money on advertising on television, since this can provide maximum profit. How to use this tool in practice? To do this, follow these simple steps.
- To get started, click “Excel Options”, then go to the “Add-ins” category.
- After that, in the “Management” field, find “Excel Add-ins” and click on “Go”.
- After that, in the list of add-ons, activate “Search for a solution”.
After pressing OK, the add-in is successfully activated. Then, simply click on the appropriate button on the “Data” tab in the same group as the analysis package and set the appropriate parameters. After that, the program will do everything on its own. Thus, using regression in Excel is a very simple thing. Much easier than it might seem at first glance, since most of the actions are performed by the program. It is enough just to drive in the correct settings, and then you can relax. And yes, you still need to interpret the results correctly. But it’s not a problem. Good luck.