**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.

**Table of Contents**hide

## Download Practice Workbook

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

## What Is Logistic Regression?

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:

- Binary Logistic Regression
- Multinomial Logistic Regression
- 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**.

**Read More:** **Multiple Linear Regression on Excel Data Sets (2 Methods)**

### 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.

`=$D$16+$D$17*C5+$D$18*D5`

- 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**.

**Read More:** **How to Do Simple Linear Regression in Excel (4 Simple Methods)**

### Step 3: Determine Exponential of Logit for Each Data

Here, we will calculate the exponential value of the 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**:

`=EXP(E5)`

- 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

**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**.

`=F5/(1+F5)`

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

**Read More:** **How to Calculate P Value in Linear Regression in Excel (3 Ways)**

### 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:

`=(B5*LN(G5))+((1-B5)*LN(1-G5))`

- 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.

`=SUM(H5:H14)`

**🔍 Breakdown of the Formula**

We are doing this breakdown for cell **H5**.

`👉`

**LN(G5):** This function returns **-0.384.**

`👉`

**LN(1-G5):** This function returns **-1.144.**

** 👉 (B5*LN(G5))+((1-B5)*LN(1-G5)): **This function returns

**-0.384.**

### Step 6: Use Solver Analysis Tool 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 Binary 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!

Hi! How do you assume what b0, b1 and b2 are? Are there always 3 or is it one for the dependent+1 for each indicator you are testing?

Dear Ella,

Thank you for your comment. I will try to provide a proper answer to your question.

The first answer is, to set the value for b0, b1, and b2, you can assume any values. Then, you can optimize the assumed values.

The answer to the second question is, since we have 2 independent and one dependent variable, we took 3 initial problem-solver variables. One for the dependent variable, and then for each independent variable, we took 1 additional problem solver variable.

I hope this will help you.

Regard,

Afia Aziz Kona

Thank you for this post. Very instructive.

What if I have more than 2 independent variables (5 to be specific), how do i use the Log-Likelihood formula, :

=(B5*LN(G5))+((1-B5)*LN(1-G5))

Does it remain as is or does it change based on the number of independent variables?

Hello

SEUN OLALEYE,Hope you are doing all well. Let’s get into your query first.

In this case, the

Log-Likelihoodformula doesn’t rely on the number of independent variables. In the case of5independent variables, the formula would be the same. The change will happen in the formula of theLogitvalue (X).`X = b0 + (b1 * ind var 1) + (b2 * ind var 2) + (b3 * ind var 3) + (b4 * ind var 4) + (b5 * ind var 5)`

The only change will happen here. All the remaining formulas will be the same.

For a better understanding, please go through the entire article again. Happy Excelling.

Regards,

SHAHRIAR ABRAR RAFIDTeam ExcelDemy

Hello, Im having an issue. Sometimes after pressing the Solve button, it comes back with an error stating “one of the cells in the worksheet became an error value when Solver tried certain values for the Variable Cells”. When I go back to the worksheet, there is a #NUM! error in one or 2 cells in the Exponential Value column.

Any ideas as to why this is happening? Any help would be greatly appreciated.

Thanks, MIKE M for your question. The problem you are stating indicates that the Solver could not find a solution that satisfied the optimization constraints you specified. This can happen for several reasons, including Incorrect input data, Incorrect model specification, Insufficient or incorrect constraints, Numerical instability, etc. Try to rectify those issues or if you need a more specific solution to your problem, it would be convenient if you provide your dataset. Thanks for being with ExcelDemy.

Regards

Mohammad Shah Miran

Team ExcelDemy