# logistic regression coefs to excel formulation

I have done a Logistic regression in r with 3 variables involving 1 interation:

lm(predicthead~vol+weekday*head, mydata)


weekday is a factor - 7 days of the week.

The coef values are :

    Estimate
(Intercept) -14.81119721
Vol 0.118819705
weekday2    -24.30795615
weekday3    -4.634180612
weekday4    -21.64229775
weekday5    0.607492058
weekday6    42.80131468
weekday7    19.16098601


From my Understanding below is what I do in excel:

intercept +
(coef of vol * vol)+
(coef of corresponding week days) + #there no is value for weekday 1
(coef of corresponding weekday:head * head) #no value for weekday1 so it will be zero.


The sum of the above should be equal to predict() value - but I am not able to match it. Could you please explain where I have done wrong in calculating predicthead value using excel?

• Are you including the link function? Jul 13 '17 at 23:31

By running a logistic regression model, the objective is to get the chance of a binary outcome based on the different predictor variables. Since the result is a chance, what is modeled is actually:

$$log \left(\frac{P_{+}}{1 - P_{+}}\right) = \beta_{0} + \beta_{1}x_{1} + ... + \beta_{n}x_{n}$$

So the coefficients you get from R are the $\beta$ values. The function you have so far in Excel computes correctly the right hand side (RHS) of the above equation, but to get the probability $P_{+}$, you need to solve the equation for it. Let's call the RHS, $\mathbf{Bx}$

$$log \left(\frac{P_{+}}{1 - P_{+}}\right) = \mathbf{Bx} \\ \frac{P_{+}}{1 - P_{+}} = e^{\mathbf{Bx}} \\ \frac{1}{e^{\mathbf{Bx}}} = \frac{1}{P_{+}} - 1 \\ \frac{e^{\mathbf{Bx}} + 1}{e^{\mathbf{Bx}}} = \frac{1}{P_{+}} \\ P_{+} = \frac{1}{1 + e^{-\mathbf{Bx}}}$$

To your results, apply this transformation and you should get the same results as the predict function in R.

• thank you. I tried as you suggested, but the value is always coming as 1. The excel function used is.1/(1+EXP(-RHSVALUE)) = the result was 1 only. Jul 8 '17 at 5:54
• Hmm.. that seems odd. Is Excel rounding the results? Can you change the value to a decimal? Jul 10 '17 at 17:51
• did that. But the value should be about 85 - but the result is 1. I am not trying for the probability value, I am looking for matching it with the predicted value through excel Jul 11 '17 at 4:12
• @surpavan are you adding all the applicable coefficients, including the intercept to RHSVALUE? Also, if you're trying to predict the reference category you need to add the cooefficients for all categories. Jul 13 '17 at 23:22
• @david25272, thank you. as said in the question, I have added the product of the (coefficients & its corresponding variable) and intercept. Jul 13 '17 at 23:27