Log-linear regression (Poisson) in Excel tutorial (2024)

FAQs

How do you do a log-linear regression in Excel? ›

Setting up a Log-linear regression

After opening XLSTAT, select the **XLSTAT / Modeling data / Log-linear regression command, or click on the corresponding button of the Modeling data toolbar. Once you've clicked on the button, the dialog box appears. The data are presented in 200 rows and 3 columns table.

We provide two examples with their solutions from the literature to show Poisson regression in Excel. Additionally, we present a pattern recognition method for the Hessian matrix to find the Variance–Covariance matrix in Poisson regression which is used to obtain the coefficient's standard errors.

Poisson regression assumes the response variable Y has a Poisson distribution, and assumes the logarithm of its expected value can be modeled by a linear combination of unknown parameters. A Poisson regression model is sometimes known as a log-linear model, especially when used to model contingency tables.

Excel Function: Excel provides the following function for the Poisson distribution: POISSON. DIST(x, μ, cum) = the probability density function value for the Poisson distribution with mean μ if cum = FALSE, and the corresponding cumulative probability distribution value if cum = TRUE.

The logarithmic transformation is what as known as a monotone transformation: it preserves the ordering between x and f (x). Recall that in the linear regression model, logYi = α + βXi + εi, the coefficient β gives us directly the change in Y for a one-unit change in X.

Contrary to linear regression, in Poisson regression: Residuals may follow an asymmetric distribution around the mean (λ). Hence, outcomes on either side of the mean (λ-x, λ+x) are NOT equally likely.

It requires that the count variable's variance is not greater than its mean. Violating this assumption, known as “overdispersion,” results in deflated standard errors and inflated z values, yielding Type I errors and thus making Poisson regression contraindicated.

For a sample of size n, the likelihood for a Poisson regression is given by: L ( β ; y , X ) = ∏ i = 1 n e − exp ⁡ { X i β } exp ⁡ This yields the log-likelihood: ℓ ( β ) = ∑ i = 1 n y i X i β − ∑ i = 1 n exp ⁡ { X i β } − ∑ i = 1 n log ⁡

Log-linear analysis is a form of categorical data analysis mostly used with three-way contingency tables. A common Poisson GLM would only explain a subset of the independence relations that a log-linear analysis can explain, though in some cases (e.g. two-way tables) both methods are exactly the same.

The logarithmic Poisson model is a member of the geometric family. We do not consider different types because the mean value functions of the models are independent of type, and the mean value function is the primary determinant of the model characteristics.

Why use log linear regression? ›

One reason is to make data more "normal", or symmetric. If we're performing a statistical analysis that assumes normality, a log transformation might help us meet this assumption. Another reason is to help meet the assumption of constant variance in the context of linear modeling.

Poisson distribution is calculated by using the Poisson distribution formula. The formula for the probability of a function following Poisson distribution is: f(x) = P(X=x) = (e^-λ λ^x )/x!

Fitting a Poisson Distribution to Given Data

For a given frequency distribution of a quantity, if the range of that quantity starts from 0 and proceeds to a positive integer, then a Poisson Probability Distribution can be fitted to that data using the parameter = the observed mean frequency of that quantity.

Straight lines on graphs with logarithmic axes

In these cases, linear regression will fit a straight line to the data but the graph will appear curved since an axis (or both axes) are not linear. In contrast, nonlinear regression to an appropriate nonlinear model will create a curve that appears straight on these axes.

Excel displays the Format Axis window. Select the "Scale" tab on the Format Axis window. Click the "Logarithmic Scale" check box near the bottom of the window. This changes the chart's axis to a log scale.

Excel Functions: Excel provides the following two functions: LOGNORM. DIST(x, μ, σ, cum) = the log-normal cumulative distribution function with mean μ and standard deviation σ at x if cum = TRUE and the probability density function of the log-normal distribution if cum = FALSE.