![regression excel 2013 regression excel 2013](http://lh4.ggpht.com/-tTzb0STBIGE/U4UlzHYW8NI/AAAAAAAAxlU/i-j7bfPi7R8/clip_image005_thumb%255B1%255D.jpg)
Thus, for Example 1, we have the table shown in Figure 6. The standard errors are shown in range J23:J26 using the Real Statistics array formula =DIAG(F23:I26). The covariance matrix for Example 1 is shown in range F23:I26 of Figure 5 using the array formula:
![regression excel 2013 regression excel 2013](https://www.exceltip.com/wp-content/uploads/2014/11/pasted-image-0-50.png)
It then follows that the main diagonal of S contains the standard error of each regression coefficient. Where X is the n × k design matrix (whose rows are the X i) and Z is the n × k matrix where z ij = μ i If B = is the k × 1 column vector containing the regression coefficients and μ is the n × 1 column vector of μ i (scalar) values where μ i = exp( x i 1 b 1 + ... + x ikb k) for each i, then the covariance matrix for B is Also, the LL value changes to -48.8017 as shown in Figure 2. The coefficients now change to the values shown in range F20:I20 of Figure 2. This is done by selecting Data > Analysis|Solver, filling in the dialog box that appears as shown in Figure 4 and pressing the OK button. As mentioned in (see Basic Concepts), LL is calculated by the formula =R19-Q19-S19, which corresponds to the mathematical formula shown earlier.Īs we have done elsewhere (see Logistic Regression using Solver for example), we now use Solver to maximize LL. Cells Q19, R19 and S19 contain the sums of the values in the corresponding column. Note that initially LL contains the value -5925831 (based on the initial guess that all the regression coefficients are 1).įor now, we will focus on columns P through S, since these are used to calculate LL.įor example, cell P4 contains the worksheet formula =EXP(SUMPRODUCT(F4:I4,F$20:I$20)), cell Q4 contains the formula =P4*K4, cell R4 contains the formula =J4*LN(Q4) and S4 contains the formula =LN(FACT(J4)). Our goal is to maximize the value of LL, more precisely called LL fit, (cell N14) which contains the formula =R19-Q19-S19, based on the part of the worksheet shown in Figure 3. The values shown in Figure 2 are those that will be obtained using Solver, as explained shortly. Initially, we insert the value 1 for each of the coefficients (range F20:I20). Since our model will contain a constant term, we show these values as well (in column F).įigure 2 – Poisson regression using Solver Since the number of packs of cigarettes smoked are ordered, we retain the number of packs smoked as a numeric value.
REGRESSION EXCEL 2013 CODE
To create our model, we code the three psychological profiles using two dummy variables. We develop a Poisson regression model that can be used to predict cancer in other populations. For each of the 15 categories, the population of people in that fit that category are also listed (whether or not they developed a tumor).
REGRESSION EXCEL 2013 HOW TO
We now show how to use Excel’s Solver to estimate the coefficient values that maximize the log-likelihood statistic LL (see Basic Concepts).Įxample 1: Figure 1 summaries the number of people who developed a tumor during the study period based on two criteria: psychological profile (A, B or C) and number of packs of cigarettes smoked each day (none, half a pack, one pack, one and a half packs or two packs).