In this tutorial, you will learn how to perform logistic regression in Excel.
A frequently employed statistical calculation is regression analysis. We frequently make calculations of this kind based on our desires. We may run various kinds of regression analysis in Excel.
Once you are ready, we can get started by using real-life scenarios to help you understand how to perform logistic regression in Excel.
Table of Contents
Perform Logistic Regression
Before we begin we will need a group of data to be used to perform logistic regression in Excel.
First, you need to have a clean and tidy group of data to work with.
In this example, using a dataset that indicates whether or not these pianists may be accepted into music school (Entry: 0 = no, 1 = yes) based on their average score and sight reading in the prior exam, we want to run logistic regression in Excel.
We will make cells for the two regression coefficients plus one for the intercept in the model since there are two explanatory variables (average score and sight reading) in the model. We will set the values for each of them to 0.001, but we will optimise for them afterwards.
The logit, elogit, probability, and log likelihood are a few extra columns that must be created in order to optimise for these regression coefficients.
Here is the formula to calculate the following:-
- Logit =$B$9+$B$10*B2+$B$11*C2+$B$12*D2
- eLogit =EXP(E2)
- Probability =IF(A2=1,F2/(1+F2),1-(F2/(1+F2)))
- Log likelihood =LN(G2)
Then we will calculate the sum of the log likelihood. You can insert this formula =SUM(G2:G11).
Then we will select ‘Data’ and select ‘Solver’. The solver parameters box will pop up and we will input as per these settings to calculate the final regression analysis. We then select ‘Keep Solver Solution’ and press ‘OK’.
Once we are done, your data will look like this.
The regression coefficients can be used to determine the likelihood that draught = 0 by default. However, in logistic regression, the likelihood that the response variable equals one is often of relevance. Hence, we can easily change the signs of each regression coefficient like this.