Finding multinomial logistic regression coefficients using Solver

The approach described in Finding Multinomial Logistic Regression Coefficients doesn’t provide the best estimate of the regression coefficients. In fact a higher value of LL can be achieved using Solver.

Referring to Figure 2 of Finding Multinomial Logistic Regression Coefficients, set the initial values of the coefficients (range X6:Y8) to zeros and then select Data > Analysis|Solver and fill in the dialog box that appears with the values shown in Figure 1 (see Goal Seeking and Solver for more details) and then click on the Solve button.

Multinomial logistic regression Solver

Figure 1 – Solver dialog box for Multinomial Logistic Regression

The result is displayed in Figure 2 and 3.

Solver mulinomial logistic regression

Figure 2 – Multinomial logistic regression using Solver (part 1)

Multinomial logistic Solver 2Figure 3 – Multinomial logistic regression using Solver (part 2)

As you can see the value of LL calculated by Solver is -163.386 (see Figure 3), which is a little larger than the value of -170.269 calculated by the binary model (see Figure 4 of Finding Multinomial Logistic Regression Coefficients).

To test the significance of the coefficients (the equivalent of Figure 5 of Finding Multinomial Logistic Regression Coefficients for the Solver model) we need to calculate the covariance matrix (as described in Property 1 of Finding Multinomial Logistic Regression Coefficients). This is shown in Figure 4.

Covariance matricìx multinomial logistics

Figure 4 – Calculation of the Covariance Matrix

The covariance matrix displayed in Figure 4 is calculated using the formulas shown in Figure 5.

Multinomial logistic covariance formulasFigure 5 – Formulas used in Figure 4

Using the results in Figure 2 and 4, we get the result shown in Figure 6.

Multinomial logistic regression parameters

Figure 6 – Multinomial logistic regression using Solver (part 3)

The key formulas used to calculate the Cured + Dead table are shown in Figure 7 (the Sick + Dead table is similar).

Multinomial logistic parameters formulasFigure 7 – Key formulas in Figure 6

The forecasted probabilities, based on the multinomial logistic regression model using Solver, of the three outcomes for men and women at dosages of 24 mg and 24.5 mg is displayed in Figure 8.

Multinomial logistic regression forecast

Figure 8 – Forecasted probabilities using Solver

20 thoughts on “Finding multinomial logistic regression coefficients using Solver”

  1. Hello Sir,
    How to compute the confusion matrix for the example as shown above using Excel? Also I could not understand the GAMMALN(x) part as well. Why we are using this?

    Reply
  2. Hi Charles – Thank you. I’ve really enjoyed your site!

    I’m trying to determine the probability weighted average of an financial index under four independent variables. The data is broken down as the following:

    * 80 observations (quarterly returns) that are observed under 4 independent scenerios 19/25/20/16 is breakdown. Would you recommend using logistic or multinomial regression to determine the probabilities for each observation? For example, I put the 80 observations into logistic regressions model and under the 1st scenario I had 19 successes and 61 failures which gave me the probabilities for each of the 19 quarterly returns. I repeated this for the other 3 scenarios. I think the intercept and slope is too low. Not sure how I would estimate this. Is this the right way to think about this? Any help is greatly appreciated.

    Reply
    • Hi Aaron,
      If you have 4 possible non-overlapping outcomes, then probably multinomial logistic regression is the better choice. If there is an order to the 4 possible outcomes, the ordered multinomial logistic regression is best.
      Charles

      Reply
  3. Dear professor Charles,

    First of all, thank you for your kindness of making this add-ins for excel users. This is so much helpful especially for students who want to understand statistics more deeply.

    I am trying to understand the estimation of the winning probabilities of the horse in a horse racing but I am confused that which model I should use. Should it be logistic regression or multiple logistic regression? From the book that I have read, it suggests to use multiple logistic regression but I do not understand because the dependent variable is binary (win/lose) then why it is multiple logistic regression. I hope that if I can replicate the result from mlogit package and this add-ins will help me understand this more.

    If you could hint me how to do or explain why horse racing probabilities can be calculated from multinomial logistic regression, it would be very much appreciated.

    Reply
    • Hello,
      It really depends on what you are trying to predict. If it is the probability that horse A wins, then logistic regression could be a reasonable approach. Binary logistic regression would be reasonable with two horses (horse A wins equals horse B loses). When you have more than two horses (the usual situation), then multinomial logistic regression would be reasonable since it predicts the probability that horse A wins and the probability that horse B wins, …, and the probability that horse H wins (assuming 8 horses in the race).
      Charles

      Reply
  4. Hi
    I have a database of Race Horse results.
    I have been trying to self teach myself as to how to do Linear Regression,
    I am slowly getting there, but at times i get stuck.
    From reading articles on the Net, i have learn’t that in some cases i will have 2 dependent variables and up to 15 independent variables.
    Do i use Multinomial Logistic Regression to find the probability of horse winning at its next run / And as the example shows above, i would like to be able to convert the probability into odds,
    If you like i can send you a sample of my database / where you can do a small example
    eg 1 dependent variable and say, 3 independent variables.
    Ps i had a look on excel ”Data Analysis” tab and i cannot find Multinomial Regression”
    I have the latest excel software / Can you please give me a some guidance as to how
    to find this

    Thanks and Merry Xmas
    Charles

    Reply
    • Charles,
      1. If say there are 8 horses, you can use multinomial logistic regression to show the probability of each horse winning.
      2. odds = p/(1-p)
      3. The Multinomial Logistic Regression data analysis tool is not provided by Excel’s Data analysis tab. It is provided by the Real Statistics Resource Pack, which you can download for free from the Real Statistics website.
      Charles

      Reply
  5. Hi Charles,
    If were rather using multinomial regression to predict one of three outcomes, then what is the criteria used to determine the final outcome? Do we just pick the predicted outcome with highest probability.

    Thanks

    Reply
  6. is there any way you could post the spreadsheet you are using so that some of the values and where they came from are more clear? thanks,

    Reply
    • Anson,
      There probably is a way to do this based on the analysis already done, but I can’t think of it at this moment. Instead, you can reanalyze the original data taking one of the other variables (e.g. Gender) and the base variable.
      Charles

      Reply
  7. I’ve been working with a political campaign and decided to try a logistic regression to get a rough predictive formula for the likelihood of an individual voter showing up to the polls. My LL seems really low though, around -12,000. is this something that would indicate I did something wrong?

    Reply
    • Ed,
      The value of LL really depends on the nature of your data, and doesn’t necessarily mean that you have done something wrong.
      Charles

      Reply

Leave a Comment