hw

1

Statistical Analysis in

Excel

Run linear regression

1. Click ‘File’ tab → ‘More’ (bottom-left) → ‘Options’

2. Click ‘Add-ins’ (bottom-left) → Select ‘Excel Add-ins’ → Click

‘Go…’

2

3. Check the box ‘Analysis ToolPak’ → Click ‘OK’

4. Click ‘Data’ on the top menu → Click ‘Data Analysis’ on your

top right.

3

5. Scroll down → Select ‘Regression’ → Click ‘OK’

6. Input “$A1:$C70” for X range and “$D1:$D70” for Y range –

Check ‘Labels’ and ‘Line Fit Plots’ → Click ‘OK’

4

7. The linear regression results will load on a separate tab.

Check the coefficient (effect) of X variables (TV, radio, and social

media ad budgets). Are they positive or negative? Does each TV,

radio, and social media ad influence sales positively?

Check the R-squared value. Is it higher than 0.2? Can you

conclude that the model is statistically significant?

5

Perform analysis of variance (ANOVA)

1. Compare the satisfaction ratings for two different website

layouts.

2. Group 1 rated the layout A, and Group 2 rated the layout B

6

3. Click ‘Data’ tab on the top menu → Click ‘Data Analysis’ on

your top right → Select ‘ANOVA Single Factor’ → Click ‘OK’

4. Scroll and select all cells ($A$1:$B$12) → Check “Labels in

First Rows” → Click ‘OK’

7

5. Excel gives the ANOVA results on a new tab.

What is the F score?

Is P-value lower than 0.1?

Are the ANOVA results statistically significant?

Can you say that satisfaction ratings significantly vary by

website layout?