**Using Newton’s Method with Summary Data**

Before turning our attention back to Example 1 of Basic Concepts of Logistic Regression, we first give some useful background.

**Property 1**: The maximum of the log-likelihood statistic (from Definition 5 of Basic Concepts of Logistic Regression) occurs when the following *k* + 1 equations occur:

Click here for a proof of Property 1, which uses calculus.

**Observation**: Thus, to find the values of the coordinates *b _{i}* we need to solve the equations

We can do this iteratively using Newton’s method (see Definition 2 of Newton’s Method and Property 2 of Newton’s Method) as described in Property 2.

**Property 2**: Let *B* = [*b _{j}*] be the (

*k*+1) × 1 column vector of logistic regression coefficients, let

*Y*= [y

*] be the*

_{i}*n*× 1 column vector of observed outcomes of the dependent variable, let

*X*be the

*n*× (

*k*+1) design matrix (see Definition 3 of Least Squares Method for Multiple Regression), let

*P*= [

*p*] be the

_{i}*n*× 1 column vector of predicted values of success and

*V*= [

*v*] be the

_{ij}*n × n*diagonal matrix where

*v*(1

_{ii}= p_{i}*– p*) on the main diagonal and zeros elsewhere. Then if

_{i}*B*is an initial guess of

_{0}*B*and for all

*m*we define the following iteration

then for *m* sufficiently large *B** ≈ B_{m}, *and so

*B*is a reasonable estimate of the coefficient vector.

_{m}Click here for a proof of Property 2, which uses calculus.

**Observation**: If we group the data as we did in Example 1 of Basic Concepts of Logistic Regression (i.e. summary data), then Property 1 takes the form

where *n* = the number of groups (instead of the sample size) and for each *i* *n _{i} *= the number of observations in group

*i.*

Property 2 also holds where *Y* = [y_{i}] is the *n* × 1 column vector of summarized observed outcomes of the dependent variable, *X* is the corresponding *n* × (*k*+1) design matrix, *P* = [*p _{i}*] is the

*n*× 1 column vector of predicted values of success and

*V*= [

*v*] is the

_{ij}*n × n*matrix where

*v*(1

_{ii}= n_{i}p_{i }*– p*) on the main diagonal and

_{i}*v*0 when

_{ij}=*i ≠ j.*

**Example 1** (using Newton’s Method): We now return to the problem of finding the coefficients *a* and *b* for Example 1 of Basic Concepts of Logistic Regression using the Newton’s Method.

We apply Newton’s method to find the coefficients as described in Figure 1. The method converges in only 4 iterations with the values *a* = 4.47665 and *b* = -0.0072.

**Figure 1 – Finding logistic regression coefficients using Newton’s method**

The regression equation is therefore logit(*p*) = 4.47665 – 0.0072*x*.

We can get the same result using the **Logistic Regression** data analysis tool as described in Finding Logistic Regression Coefficients using Solver, except that this time we check the Using **Newton method** option in the **Logistic Regression** dialog box (see Figure 4 of Finding Logistic Regression Coefficients using Solver or Figure 3 below).

**Using Newton’s Method with Raw Data**

**Example 2**: A study was made as to whether environmental temperature or immersion in water of the hatching egg had an effect on the gender of a particular type of small reptile. The table in Figure 2 shows the temperature (in degrees Celsius) and immersion in water (0 = no and 1 = yes) of the 49 eggs which resulted in a live birth as well as the sex of the reptile that hatched. Determine the odds that a female will be born if the temperature is 23 degrees with the egg immersed in water vs. not immersed in water.

We use the **Logistic Regression** data analysis tool, selecting the **Raw data** and **Newton Method** options as shown in Figure 3.

**Figure 3 – Logistic Regression dialog box for Example 2**

After pressing the **OK **button we obtain the output displayed in Figure 4.

**Figure 4 – Output from Logistic Regression data analysis tool**

Here we only show the first 19 elements in the sample, although the full sample is contained in range A4:C52. Note that in the raw data option the Input Range (range A4:C52) consists of one column for each independent variable (Temp and Water for this example) and a final column only containing the values 0 or 1, where 1 indicates “success” (Male in this case) and 0 indicates “failure” (Female in this case). Please don’t read any gender discrimination into these choices: we would get the same result if we chose Female to be success and Male to be failure.

The model indicates that to predict the probability that a reptile will be male you can use the following formula:

We can now obtain the desired results as shown in Figure 5 by copying any formula for p-Pred from Figure 4 and making a minor modification.

Here we copied the formula from cell K6 into cells G29 and G30. The formula that now appears in cell G29 will be =1/(1+EXP(-$R$7-MMULT(A29:B29,$R$8:$R$9))). You just need to change the part A29:B29 to E29:F29 (where the values of Temp and Water actually appear). The resulting formula

1/(1+EXP(-$R$7-MMULT(E29:F29,$R$8:$R$9)))

will give the result shown in Figure 5.

In Real Statistics Functions for Logistic Regression we show an easier way of finding the predicted values.

**Observation**: The approach described above for performing logistic regression with input in the form of raw data works well for up to 65,500 rows of data. When the input data contains more than 65,500 rows, you can still use the **Logistic Regression** data analysis tool, but you need to uncheck the **Show summary in output** option (see Figure 3).

See Real Statistics Functions for Logistic Regression for how to perform logistic regression including summaries when there are more than 65,500 rows of raw data.

Hi Charles,

Thanks for the help!

I managed to install your really powerful tool and running some data and it works like magic!

But I do encounter errors with larger raw data sets: 5000 – 10000 rows. The Logistic Regression function was returning an error: Type mismatch. All my raw data are in numbers and it should work well.

Just to check if the function is able to handle >5 variables?

Thanks a lot!

Richmond

Hi Richmond,

You are correct. The problem is in the DIAGONAL function. For a data set with 10,000 records, the resulting diagonal matrix can be huge (as much as 10,000 x 10,000). The program probably consumes all the memory and then gives an error. It turns out that the DIAGONAL function isn’t really needed in Logistic Regression and so I have replaced it by a simpler approach which resolves the problem. This is included in the new release (R 1.8) which came out today.

Thanks again for identifying the problem and helping me find a resolution.

Charles

Good day Charles

Please would you confirm if this can be done for ordinal logistic regression in excel. It’s difficult to find anything on the internet regarding ordinal logistic regression and how to use Newton Raphson method for it.

Kind regards

Declan

Declan,

I am currently finishing the update of the Real Statistics website to reflect all the changes made after release 2.0 and 2.1. Once I am finished with this I will start looking at ordered logistic regression and probit. Stay tuned.

Charles

Thank you Charles 🙂

You are a genius!

This is what I’ve been looking for almost a week!!! Thank you Charles Zaiontz!

Good to hear Tim. I hope that you find it useful. Charles.

Charles,

I work in a hospital where the applications of logistic regression are numerous. I have found your website easy to understand and extremely helpful. I cannot say thank you enough! I don’t have the words to express how impressed and excited I am to find your site.

Kind regards,

Isaiah

Isaiah,

Thank you very much for your kind words. I am very pleased that you have found the website useful.

Charles

Hi Charles,

So glad to find this helpful blog. However I have a question regarding the value of water immersion. From my understanding this value is independent variable. If I insert my independent variable to any number rather than 0,1 I got error. My question is, is it a must that this column be 0 or 1 only?

Thanks

Hi Raseeda,

An independent variable such as Water Immersion can take any value, not just 0 and 1. Note, however, that if you change an existing 0 or 1 to some other number you will increase the number of rows in the output. This won’t happen automatically and so the output will be incorrect. To resolve this problem you need to rerun the data analysis and the error should go away. If not, please send me the data you are using and I will try to see what the problem is.

Charles

When i run data on this tool it shows an error Compile error in hidden module.Could you please help me on this.

Kind Regards

Mohit

Mohit,

Which version of the Real Statistics Resource pack are you using. You can find this out by entering =VER() in any cell?

Which version of Excel are you using?

Charles

Hi Charles,

Would it be possible to have the excel version of figure one?

Thanks,

Muzz

Hi Muzz,

All the examples on the website can be downloaded, including the one in the referenced figure. See the webpage http://www.real-statistics.com/free-download/real-statistics-examples-workbook/ for more details.

Charles

Hi Charles,

I am a student from China, I am now learning how to do logistic regression in EXCEL.Then I find your website, I have read your all papers about the logistic regression ,but I still have some questions.

I have 19 kinds of independent variables and 1 dependent variables.I have used to ways to run the data, but I can’t find the answers….

Can you help me to find the reasons? Thanks very much.

Looking forward for your reply,

Yeva

Hi Yeva,

Which questions do you have?

Charles

Hi Charles，

I want to use the Newton’s method，but I can’t get the intercept and slope when I use the 19 independent variables. I can only get the coefficient with one independent variable at each time.

Why I can’t apply all the independent variables for the regression?

Yeva,

Please send me your worksheet and I will look at it.

Charles

Hi Yeva,

I have looked at the spreadsheet you sent me. There is no problem with the number of variables. The problem is the number of rows of data.

The

Logistic Regressiondata analysis tool uses theLogitSummaryfunction to convert raw data (as in your input) to summary format.There is a 65,500 limit for this function (and for similar functions in Excel). I have created a work-around to handle more than 65,500 rows of raw data using theLogitMatchesfunction. This is described on the webpage http://www.real-statistics.com/logistic-regression/real-statistics-functions-logistic-regression/Charles

Hi Charles,

I have used Logistic Regression Coefficients using Newton’s Method for my data. unfortunately, I couldn’t read and understand the results. Is there a link that explains the basics of logistic regression output.

Thanks,

Hi Shosho,

Perhaps the following webpage will help:

http://www.real-statistics.com/logistic-regression/finding-logistic-regression-coefficients-using-excels-solver/

Charles

Please sir, can you help me with the excel template for a Cox Proportional Hazard Model to complete my theses. Indeed am doing magic with your template at my work place as a statistics officer. My regards to you and your support team @ real-statistics.

THANKS!!

I plan on adding survival analysis capabilities later this year, but for now I will put the Cox Proportional Hazard Model on my list of future enhancements.

Charles

Hi Charles,

Your explanation regarding logistic regression was very helpful. However, I have a small doubt about categorical variables. When doing logistic regression with categorical (more than 2 values possible such as dept. 1, 2, 3) independent variable, how do I interpret the odds ratio (exp(b) in this tool) of such a categorical variable?

Thnaks

You should use a tag coding with more than 2 possible values for a categorical variable (this can be done manually or by first using categorical coding capability found in the Linear Regression data analysis tool). In this case you can have odd ratios (e.g. Dept 1 vs. Dept 3).

Charles

Hi Charles,

Your excel add in has helped me a lot in my project.

I was wondering, whether its possible or not, to have both categorical and interval/ratio (non categorical) data in the input, and can your logistic regression model be used with such a data.

My project is building a prediction model, based on inputs like Modification type (categorical), FICO score (continuous data), and so on.

Thanks,

Vatsal

You can use either type of data. If you use categorical data (with more than 2 categories) you need to decide whether to use tag coding (aka dummy coding). You can do this yourself as described elsewhere on the website or you can have the software do this for you. The dummy coding capability is available from the linear regression data analysis tool. See the webpage Categorical Coding.

Just use that tool to do the coding and then switch to the logistic regression tool to do the actual analysis.

Charles

Thanks for the swift response.

However, my model requires both, categorical as well as continuous data as input, and the output is binary.

Let me tell you my project: design a default rate prediction model, based on certain parameters. I have loan level data with me, in which there are both categorical and continous data for each loan number, and the output is, whether the loan defaulted or not (0 or 1, hence binary output).

I tried using the logistic regression capability of the add-in but the output wasnt what I desired, since it mapped each categorical data point with each individual continuous data point.

The example in the link (Categorical coding) is of linear regression. Can this be done in logistic regression as well?

Thanks again!

Hi Mr. Charles. I have a problem to understand how this newton’s method work for my data for MNL models. for example : I have 10 respondent and each respondent has a three alternative options (1 = Bus; 2=auto;3=motor). and of course all respondents has a three different time travels (travel times is independent variable). and from my observations I have what respondent choose from three alternative that I explain above.

I couldn’t follow your example above and adjusting my data.

With three possible outcomes, you should consider using multinomial logistic regression instead of (binary) logistic regression. Please look at the webpage Multinomial Logistic Regression.

Charles

Hi, Charles! Thank you for this very insightful site of yours, it helped me tremendously! I would like to ask, with the result, what value should I look for if I’ want to determine whether the tested independent variables are significant? And, are the tests for multicollinearity and interaction already included in the logistic regression test? Your reply will be deeply appreciated. Thanks!

Justin Radcliffe

Justin,

If you look at the p

Justin,

1. If you look at Figure 4, you will see that for each coefficient the p-value is given. This can be used to determine whether that coefficient is significant (i.e. the coefficient for the corresponding variable is significantly different from zero). If the coefficient is not significant then that variable does not contribute significantly to the model (in the presence of the other variables).

2. While the multicollinearity test is not included in the logistic regression output, it is identical to that used for linear regression, and so you can use the VIF function to test for it. If there is 100% multicollinearity then the logistic regression model will not converge and you will see error values in the output.

3. You can model interaction in the same way as you do for linear regression, namely if you are interested in the interaction between variables x1 and x2, then you need to include x1*x2 in the model (i.e. a column whose values are the (pairwise) product of the data in the columns for x1 and x2.

Charles

Thank you very much, Charles! Your reply is of big help!

Hi Charles! This page of yours is extremely helpful, I can’t thank you enough for it really helped me understand logistic regression better.

I’m trying to apply the binary logistic regression test on my data where I have dummy coded independent variables, however, there are errors on the result when I applied it on all the variables. But when I tried to do it with fewer variables, it worked. I wonder what’s the problem.

It is likely that there isn’t any problem, simply that one set of data is a fit for logistic regression and the other isn’t.

Charles

Hi Charles,

Thanks so much for all the instruction in this website! It’s been a life saver for me over the past few months.

One quick question: for categorical independent variables, is 1 or 0 the referent group for interpreting exp(b)? E.g. using your example, Figure 4, would you say that, after adjusting for temperature, the odds of being hatched male are 0.4 (95% CI, 0.1-1.5) times as likely for an egg born in water (water=1) compared to an egg born out of water (water=0)? Or is it that the odds of being hatched male are 0.4 (95%CI, 0.1-1.5) times as likely for an egg born out of water (water=0) compared to an egg born in water (water=1)?

Thanks again!

Rachael

Rachel,

The ratio Odds(0)/Odds(1) = exp(b) = .42265. Thus, the odds of a male birth for an egg in water = .42265 times the odds of a male birth for an egg not in water

For more information, see the comment below Figure 3 on the following webpage:

http://www.real-statistics.com/logistic-regression/finding-logistic-regression-coefficients-using-excels-solver/

Charles

How can I solve V which is covariance matrix?

Sisay,

Are you referring to the V in Property 2? If so V = [v_ij] be the n × n diagonal matrix where v_ii = p_i (1 – p_i) on the main diagonal and zeros elsewhere.

Charles

Thanks, Charles.

1. Specifically I want to know how you get covariance matrix which is

0.03483 -6.00E-05

-6.00E-05 1.10E-07 in your survival analysis example at iteration 0

2. Do you mind if you elaborate me the matrix of V=[v_ii]=p_i(1-p_i) in figures.

Thanks again

Sisay,

See Property 1 on the following webpage:

http://www.real-statistics.com/logistic-regression/significance-testing-logistic-regression-coefficients/

Charles

Dear Charles,

Eureka! Many thanks indeed! Education is sharing

I will keep in touch

Dear Charles,

I want to deal with the data that you given above: Rems, Survived, and dead. How ever, after I inserted the data in RealStats-2003 pac, a message that said “you should enter 0 and 1 in the last column” appeared. How can I run this pac?

Thanks.

Sisay,

The last column must contain only 0’s and 1’s if you use the Raw data option. This is not the case for the Summary data option. Probably you just need to choose the Summary option instead of the Raw option.

Charles

Hi Charles,

Looking at Figure 1, I was wondering if you could tell me what the design matrix would be in order to get the covariance matrix shown in iteration 0. Also directly affects what matrix V would be, so any insight onto that would also be appreciated.

Also, how is the iteration matrix formed?

Thanks,

David

David,

The design matrix that actually achieves the covariance matrix shown in range J9:K10 after zero iterations is the 10 x 2 matrix with ones in the first column and the values in the range E4:E13 in the second column.

The array formula used to calculate the covariance matrix for iteration 0 is

=MINVERSE(MMULT(TRANSPOSE(DESIGN(E4:E13)),MMULT(DIAGONAL(G4:G13*(1-G4:G13)*($B$4:$B$13+$C$4:$C$13)),DESIGN(E4:E13))))

Charles