Data Analysis with Microsoft Excel: Updated for Office 2007

408 Statistical Methods

Fitting the Effects Model

With these columns of indicator variables, you can now fi t the effects model

to the hotel pricing data.

To fi t the effects model using regression analysis:

1 Click the Data Analysis button from the Analysis group on the

Data tab, then click Regression in the Analysis Tools list box, and

click OK.

2 Type D1:D33 in the Input Y Range text box, press [Tab], and then

type F1:H33 in the Input X Range text box.

Recall that you have to remove one of the effect terms to keep from

overparametrizing the model. For this example, remove the New

York effect term. (You could have removed any one of the four city

effect terms.)

3 Click the Labels checkbox to select it because the range includes a

header row.

4 Click the New Worksheet Ply option button; then type Effects Model

in the corresponding text box.

5 Verify that all four Residuals checkboxes are deselected; then

click OK.

The regression output appears as in Figure 10-12. (The columns are

resized to show the labels.)

Figure 10-12
Created effects
model with the
Regression
command

average NYC

price

difference

from the NYC

average room

price