NumXL Cookbook - GLM with Binary Data

Generalized Linear Model tutorial video

In this tutorial, we will use a sample data gathered during a clinical trial of a new chemical/pesticide on tobacco Budworms. The subjects (i.e. Budworms) are grouped into batches of 20, and exposed to different doses of the chemical. The results are summarized below

Input data table for generalized linear model in excel with binary data example

Data preparation

Our objective here is to model (and forecast) the effectiveness of the new chemical using different dosages, and explain, to some extent, any variation based on the gender of the budworm. Furthermore, we want to express the results in term of the worm mortality rates (i.e. probability).

generalized linear model in excel
 - Covert binary data into mortality rates

We plot the data into two separate curves: males and females. It is apparent that mortality rate is affected by those two factors: gender and dosage.

generalized linear model in excel
 in Excel - Plot for mortality rates for male and female Bud worm

We will make two assumptions: (1) the results for each trial (i.e. batch) are drawn from a Binomial distributed population; we would like to estimate p - the probability of success (i.e. worm’s death). The probability (p) is allowed to vary across different trials (batches). (2) The probability of success is affected by two factors: gender of the subject and administered dosage of the drug.

Based on these two assumptions, we would model this relationship:

$$P=f(X,Y)=E[p|X,Y]$$

Modeling

We are ready now to propose a statistical model: generalized linear model in Excel with residuals following the Binomial distribution.

Generalized linear model (Generalized Linear Model in Excel) Wizard

For now, we choose “Logit” as our link (transform) function, specify the trial or batch size(20), and instruct the Wizard to calibrate (i.e. compute optimal values for the coefficients). Leave the Goodness-of-fit and residual diagnosis options checked.

Generalized linear model (Generalized Linear Model in Excel ) - Model specification output table

Calibration

In this case, the Generalized Linear Model in Excel (GLM) Wizard has calibrated the model’s coefficients, so we can skip this step.

But, in the event we wish to experiment with different link functions: LOGIT, PROBIT or LOG-LOG, then we need to re-calibrate the model. To do so, we can either:

  1. Create a new model with the wizard, or,
  2. (2) Change the “Lvk” parameter in an existing model table, and run the calibration using NumXL toolbar

Generalized linear model (GLM in Excel) calibration wizard or user form

Step 1: Select the cell that acts as a header for the model table

Step 2: Click on the calibration icon/menu (Excel 2003)

Step 3: Click on “Solve” button in the Solver window

Forecast

Once the model is calibrated, and we are happy with the residuals, we can use it to construct our forecast mean (and confidence interval around it).

Using NumXL function (GLM_FORE), we can compute the mean. Using GLM_FORECI, we can compute the upper and lower limit of the confidence interval.

Generalized Linear model (Generalized Linear Model in Excel) forecast output table

Plotting the data again (actual) versus the model values.

generalized linear model Forecast plot with confidence region for male budworm in Excel

generalized linear model (Generalized Linear Model in Excel) Forecast with confidence region for female Bud worm

The dots represent the sample data, while the center line is the forecast mean. The shaded regions in the graphs are the 95% confidence intervals.

Notes

  1. The forecast error decrease as we increase the dosage (C.I. gets tighter). This is evident in male and female batches
  2. The logarithmic relation detected when we plot the raw data can be merely a data anomaly; the Generalized Linear Model in Excel shows more like a quadratic-type of relationship.
  3. The mean is not exactly the center of the confidence interval due to the discrete-nature of the underlying binomial distribution, and the small batch/trial size.
Have more questions? Submit a request

0 Comments