Regression analysis is a widely used statistical calculation. We often do this type of calculation according to our desire. In Excel, we can perform multiple types of Regression analysis. In this article, we will demonstrate how to do Logistic Regression in Excel. If you are also interested to learn this analysis, download the practice workbook and follow us.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

What Is Logistic Regression Analysis?

Logistic regression analysis is a statistical learning algorithm that uses to predict the value of a dependent variable based on some independent criteria. It helps a person to get the result from a large dataset based on his desired category. Logistic regression analysis mainly three types:

1. Binary Logistic Regression
2. Multinomial Logistic Regression
3. Ordinal Logistic Regression

Binary Logistic Regression: In the binary regression analysis model, we define a category by only two cases. Yes/No or Positive/Negative.

Multinomial Logistic Regression: Multinominal logistic analysis works with three or more classifications. If we have more than two classified sections to categorize our data, then we can use this regression analysis model.

Ordinal Logistic Regression: This regression analysis model works for more than two categories. However, in this model, we need a predetermined order to categorize them.

Step-by-Step Procedure to Do Logistic Regression in Excel

In this article, we will perform the binary logistical regression analysis. This type of analysis provides us with a prediction value of the desired variable. To perform the analysis, we consider a dataset of 10 machines from an industry. The machine’s availability can be positive or negative. The binary digits 1=positive, and 0=negative, and these values are shown in column B. The age of those machines is in column C and the average duty hours of them per week is in column D. So, our dataset is in the range of cells B5:D14. The values initial regression solver variable are in the range of cells C16:D18. The whole analysis procedure is explained below step by step:

Step 1: Input Your Dataset

In this step, we are going to import your dataset:

• First of all, input your dataset accurately into Excel. For our analysis, we input the dataset in the range of cells B5:D14.

• Then, input your Solver Decision Varibles’ We input them in the range of cells D16:D18.
• We are assuming all the variables’ values as 0.01.

Step 2: Evaluate Logit Value

In this step, we are going to calculate the Logit value for our dataset. We define the Logit value as X in our calculation. The formula of Logit value is:

Here, b0, b1, and b2 are regression variables.

• Write down the following formula in cell E5. Use the Absolute sign to freeze the cell value of variables. If you don’t know how to input the Absolute Cell Reference sign, you can input it in several ways.

• Then, press the Enter key on your keyboard.

• After that, double-click on the Fill Handle icon to copy the formula up to cell E14.

• You will get all the values of X.

Step 3: Determine Exponential of Logit for Each Data

Here, we will calculate the exponential value of logit value, For that, we are going to use the EXP function:

• To determine the exponential value of X, write down the following formula in cell F5:

• Similarly, double-click on the Fill Handle icon to copy the formula like the previous step. You will all the exponential values of X.

Step 4: Calculate Probability Value of P(X)

P(X) is the probability value for occurring the X event. The probability of event X can define as:

• To calculate it, write down the following formula in cell G5.

• Press the Enter key.
• Now, drag the Fill Handle icon up to G15 to get the value for all values.

Step 5: Evaluate Sum of Log-Likelihood Value

In the following steps, we are going to evaluate the value of Log-Likelihood. After that we will use the SUM function to add all the data:

• To calculate the Log-Likelihood value, we are going to use the LN function in our dataset. In cell H5, write done the following formula:

• Now, press the Enter key on the keyboard.

• Then, double-click on the Fill Handle icon to determine all log-likelihood values.

• After that, in cell H15, write down the following formula to sum all the values.

Step 6: Use Solver Analysis Command for Final Analysis

Now, we will conduct the final regression analysis. We will perform the analysis through the Solver command. If you don’t see it in the Data tab, you have to enable the Solver from the Excel Add-ins.

• To enable it, select File > Options.

• As a result, a dialog box called Excel Options will appear.
• In this dialog box, select the Add-ins option.
• Now, choose the Excel Add-ins option in the Manage section and click Go.

• A small dialog box titled Add-ins will appear.
• Then, check the Solver Add-in option and click OK.

• After that, go to the Data tab, and you will find the Solver command in the Analysis group.

• Now, click the Solver command.
• A new dialog box entitled Solver Parameters will appear.
• In the Set Objective box, choose the cell $H$15 with your mouse. You can also write the cell reference on your keyboard. Make sure you use the Absolute Cell Reference sign here.
• Next, in the By Changing Variable Cells option select the range of cells $D$16:$D$18.
• Then, uncheck the Make Unconstrained Variables Non-Negative to get the negative values if it is already showing as checked.
• Finally, click the Solve button.

• As a result, the Solver Result box will appear in front of you.
• Now, choose the Keep Solver Solution This box will also show you whether your regression analysis converged or diverged.
• Click OK to close the box.

• At last, you will see the values of the variable in the range of cells D16:D18 is changed. Besides it, you will also see the values of columns E, F, G, and H are also showing differences from the previous steps.

Illustration of Binay Regression Analysis Result

After the completion of the binary logistic regression analysis in Excel, you will see that our assumed regression variable value is substituted with the new analysis value and these values are the correct regression variable value of our dataset. We can consider the result of any specific data, like the machine which has an age of 68 months and 4 avg. no shift per week. The value of P(X) is 0.67. It illustrates to us that if we look for the machine in working condition the possibility of that event is about 67%.

We can also show it separately, using the final values of the regression variable.

Thus, we can say that our working procedure worked successfully and we are able to do binary logistic regression analysis.

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to do logistic regression in Excel. Please share any further queries or recommendations with us in the comments section below.

Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!

The post How to Do Logistic Regression in Excel (with Quick Steps) appeared first on ExcelDemy.