# expert on excel using mega stat, you need to have megastat

2

>Output

Hypothesis

Test: Mea

n

vs.

Hypothesized

Value

1

,

1

5 0

.000 hypothesized value 1,1

8 3

.

23

5 mean Score 11

8.

9 7

9 std. dev. 20

.

4

05 std. error 34

n
33 df 1.

6

3 t .05

64 p-value

(one-tailed, upper) Hypothesis Test:

Mean

vs. Hypothesized Value

1,

15 0.000

hypothesized value

1,1

83

.2

35 mean Score 1

18

.

97

9

std. dev.

20.

40

5

std. error

34

n
33 df
1.

63

t
.0

56

4

p-value (one-tailed, upper) Hypothesis Test: Mean vs. Hypothesized Value 1,1

50

.000

hypothesized value
1,183.235 mean Score

118

.9

79

std. dev.
20.405 std. error
34 n

1.63

z .05

17

p-value (one-tailed, upper)
Hypothesis Test: Independent Groups (t-test,

pooled variance

) On-campus Commuter 87

.

13 76

.

93

mean
8.64 9.

54

std. dev.
15 15 n
28

df
10

.

200 dif

fe

rence (On-campus – Commuter) 82

.

81

0

pooled variance
9.

100 pooled std. dev. 3.

32

3 standard error of

difference 0

hypothesized difference 3.0

7

t
.00

47 p-value (two-tailed) One factor ANOVA Mean n

Std. Dev 73

.15

77

7777777

78 82.83 12 5.

96

4 No

rth 73.1

57

7777777778 72

.

80

10

10.0

43 South 73.1577777777778

73.13

11

8.

205 Central 73.1577777777778

63.8

1

12

5.820 East 73.

16 45 10.134 Total ANOVA table Source SS df MS F p-value Treatment 2,173.632

3

7

24

.5

44

0 12.

67 5.34E-06 Error 2,344.778 41 57.1

89

7 Total

4,

51

8.410

44
Post hoc analysis p-values

fo

r pairwise t-tests East South Central

North 63.81 72.80

73.13 82.83
East 63.81
South 72.80

.0082 Central 73.13

.00

52 .

92

16 North 82.83

2.54E-07 .0035 .00

37 Tukey simultaneous comparison t-values (d.f. = 41)

East South Central North
63.81 72.80 73.13 82.83
East 63.81

South 72.80

2.78 Central 73.13

2.

95 0.10 North 82.83

6.16 3.10 3.07 critical values for experimentwise error rate: 0.05 2.

68 0.01

3.32
Goodness of Fit Test observed expected O – E (O – E)² / E % of chisq 200

19

3.000 7.0

00 0.

25

4 6.

94 200

20

2.0

00 -2.000 0.020 0.54 200

1

84

.000 1

6.0

00 1.3

91 38

.01 200

22 1.0

00 –

21

.000 1.

99

5 54.51 800 800.000

0.000

3.

66

0 100.00 3.66 chi-square 3 df
.

30

05

p-value
Chi-square Contingency Table Test for Independence UG GR totals Total Y

es Observed 49 47 96 192 Expected 63.

61 32.

39 96.00 192.00 No

Observed 1

14 36 150 300 Expected

99.39 50.61 150.00 300.00 totals Observed

163 83 2

46 492 Expected

163.00 83.00 246

.00 492.00 Total Observed

3

26 166

492

98

4 Expected

326.00 166.00

492.00

98

4.0

0 16.

31

chi-square
4 df
.0026

p-value
Correlation Matrix Aptitude Sales Aptitude

1.000 0.821

88

73019

90

4

53

9 Sales

.822

1.000
19

sample size ± .

456 critical value .05 (two-tail) ± .5

75 critical value .01 (two-tail) Regression

Analysis r² 0.675 n 19 r 0.822 k

1
Std. Error 28.198 Dep. Var.

Sales
ANOVA table
Source

SS df

MS F p-value
Regression

28,138.0066 1

28,138.0066

35.39 1.

59

E-05 Residual 13,517.15

13 17 795.12

65 Total

41,6

55

.1579 18 Regression output confidence interval variables coefficients std. error t (df=17)

p-value

95%

lower 95% upper Intercept 335.9

221 27

.3

1

48 12.

29

8 6.91E-10 278.2928 393.5513 Aptitude

1.4732 0.2477 5.949 1.59E-05 0.9

507 1.995

7 Predicted values for: Sales 95% Confidence Interval 95% Prediction Interval Aptitude

Predicted

lower

upper

lower upper

Leverage 107 493.557 479.908 507.206 432.519 554.595 0.053

Hypothesis Test: Mean vs. Hypothesized Value

80.000

hypothesized value
83.528 mean

Income 13.233

std. dev.
2.206

std. error
36

n
35

df
1.

60

t
.0593

p-value (one-tailed, upper)
Hypothesis Test: Independent Groups (t-test, pooled variance) Briar Hills Englewood 338.

74

2 315.264

mean
26.755 31.781

std. dev.
12 14 n
24

df
23.4774 difference (Briar Hills – Englewood) 875.1917

pooled variance
29.

58

36

pooled std. dev.
11.6381 standard error of difference

0 hypothesized difference

2.02

t
.0275

p-value (one-tailed, upper)
Hypothesis test for proportion vs hypothesized value Observed Hypothesized
0.73 0.

62 p (as decimal) 38/52 32/52 p (as fraction) 37.96 32.24 X 52 52

n 0.0673 std. error 1.63 z
.0511

p-value (one-tailed, upper)
Hypothesis test for two independent proportions p1 p2 pc 0.

474 0.358 0.4133

p (as decimal)
64/

135 53/148 117

/

283

p (as fraction)
63.99 52.984 116

.974

X
135 148 283 n
0.116

difference
0

hypothesized difference 0.05

86

std. error
1.98

z
.0478

p-value (two-tailed)

Comparison of Groups

North South Central East 87.0

82.4 60.1 54.2 82.9

76.0

65.7 70

.2

91.1 67.2 62.3 67.4 85

.3

74.6 80.4 71

.9 79.0

80.5 64.5 59.6 74.4 59.5 75.3 67.8 76.9 84.4

77.0

54.6 85.6 59.8

79.0

66.4

84.0

61.1 79.2 61.4 91.8 82.5 78.8

61.4

82.6 82.1

62.3

73.4 68.5

82.83333333333333 72.8 73.12727272727273 63.80833333333334 73.1577777777778 73.1577777777778 73.1577777777778 73.1577777777778

73.0 7

5.0

83.0 83.0 85.0 85.0 89.0 93.0 93.0

103

.0

105

.0 107.0 117.0

120

.0

129

.0

141

.0

147

.0

153

.0

155

.0

462

.0

447

.0

408

.0

435

.0

483

.0

486

.0

465

.0

480

.0 507.0 474.0

512

.0

456.0 468

.0

541

.0

524

.0

522

.0

594

.0

581

.0

537

.0

Aptitude
Sales

## Sheet1

to show there are or there are no difference between what you are testing and the current situation.

ly above the university mean of

0. Level of signifance (Alpha) is 95%

Score

9

1

4

0

7

7

Hypothesis Test: Mean vs. Hypothesized Value

1,150.000 hypothesized value

1,183.235 mean Score

118.979 std. dev.

20.405 std. error

6

34 n

6

1.63 z

1

.0517 p-value (one-tailed, upper)

5

1247

8

9

On-campus Commuter

86 71
79 80
88 83
97 87
88 76
85 62
97 68
79 82
88 84
87 75
91 84
86 61
104 72
67 96
85 73
Hypothesis Test: Independent Groups (t-test, pooled variance)
On-campus Commuter

mean

8.64 9.54 std. dev.
15 15 n
28 do

difference (On-campus – Commuter)

pooled variance

9.100 pooled std. dev.

standard error of difference

0 hypothesized difference
3.07 t
.0047 p-value (two-tailed)
1.0

Income

79

95
77
105
65
100
67

95

83
76

83

93
79
75

79

62
103

77

87
74

76

107
73
89
97
80
64
96
78

76

70

78
65

92

106

2.0

Briar Hills Englewood

277

3.0

4.0

Example

North South Central East
87 82.4 60.1 54.2
82.9 76 65.7

91.1 67.2 62.3 67.4

74.6 80.4

79 80.5 64.5 59.6
74.4 59.5 75.3 67.8
76.9 84.4 77 54.6
85.6 59.8 79 66.4
84 61.1 79.2 61.4
91.8 82.5 78.8 61.4
82.6 82.1 62.3
73.4 68.5

One factor ANOVA

Mean n Std. Dev
73.1577777777778 82.83 12

North
73.1577777777778 72.80 10 10.043 South
73.1577777777778 73.13 11 8.205 Central
73.1577777777778 63.81 12 5.820 East
73.16 45 10.134 Total

ANOVA table
Source SS df MS F p-value

Treatment 2,173.632 3

5.34E-06

Error 2,344.778 41

Total 4,518.410 44

Post hoc analysis
p-values for pairwise t-tests
East South Central North
63.81 72.80 73.13 82.83
East 63.81

South 72.80 .0082

Central 73.13

North 82.83 2.54E-07 .0035 .0037

Tukey simultaneous comparison t-values (d.f. = 41)
East South Central North
63.81 72.80 73.13 82.83
East 63.81
South 72.80 2.78
Central 73.13

0.10
North 82.83 6.16 3.10 3.07

critical values for experimentwise error rate:
0.05

0.01 3.32

1

5

2

4

3 307.4 3
4

1

5

4

6 353.3 2
7

4

8

4

9

2.5

5 1 2 3 4 5

10

5

Englewood Briar Hills

11

4

12

5

13

1

14

3

15 337.3 3
16

4

17

3

18

3

19 339.3 2
20

5

21

2

22

2

23

4

24

4

25

5

26 275.8 2
27

2

28 339.2 2
29

4

30

3

31

4

32

2

33 306.8 3
34

2

35

5

36

1

37 271.6 2
38

4

39

2

40

2

41

3

42 330.9 2
43

4

44

1

45

2

46 353.7 2
47

5

48

1

49

1

50

4

51

4

52

1

53

2

54 298.1 2
55

2

56

1

57

1

58

1

59

5

60

3

61

2

62 300.2 2
63 347.4 3
64

2

65

4

66

2

67

4

68

5

2

70

5

71

4

72

5

73

2

74

5

75

2

76

2

77

4

78 314.4 3
79

5

80

3

81

4

82

2

83

4

84

2

85

.8

1

86 338.6 3
87

2

88 368.6 3
89

3

90 297.7 2
91 350.7 2
92

5

93

5

94

4

95

4

96

4

97

5

98

2

99

4

100

1

101

4

102 349.6 3
103

3

104

4

105

2

106

.8

4

107

5

4

109

1

1

.5

1

112

2

113

5

114

3

115

5

116

5

117

5

118 283.2 2
119 328.1 3
120

4

121

2

122 456.0 5
123

1

124

5

example

fo fe

North 200

202 South 200

East 200

221

200

Goodness of Fit Test
observed expected O – E (O – E)² / E % of chisq
200

7.000

200

-2.000 0.020 0.54

200

16.000

200

1.995 54.51

800 800.000 0.000

100.00

3.66 chi-square
3 df

p-value

UG GR totals

Yes 49 47 96

No 114 36 150
totals 163 83 246
Chi-square Contingency Table Test for Independence

Total

Yes Observed

47 96 192

Expected

96.00 192.00

No Observed

36

300

Expected 99.39 50.61 150.00 300.00
totals Observed

83

492

Expected 163.00 83.00

492.00

Total Observed

166 492

Expected 326.00 166.00 492.00

chi-square

4 df
.0026 p-value

Yes 24 50 105

No 49 58 100 207
73 108 205 386
Example

. Make predictions for X = 107

X Y

X Y
Aptitude Sales Aptitude Sales
73 462 73 462
75 447 75 447
83 408 83 408
83 435 83 435
85 483 85 483
85 486 85 486
89 465 89 465
93 480 93 480
93 507 93 507
103 474 103 474
105 512 105 512
107 456 107 456
117 468 117 468
120 541 120 541
129 524 129 524
141 522 141 522
147 594 147 594
153 581 153 581
155 537 155 537
Correlation Matrix
Aptitude Sales
Aptitude 1.000

Sales .822 1.000
19 sample size

critical value .05 (two-tail)

critical value .01 (two-tail)

is significant
Regression Analysis
r² 0.675 n 19
r 0.822 k 1
Std. Error 28.198 Dep. Var. Sales
ANOVA table
Source SS df MS F p-value
Regression

1 28,138.01 35.39 1.59E-05

Residual 13,517.15 17

Total

18

Regression output confidence interval
variables coefficients std. error t (df=17) p-value

95% upper

Intercept

6.91E-10 278.2928 393.5513

Aptitude 1.4732 0.2477 5.949 1.59E-05

Predicted values for: Sales
95% Confidence Interval 95% Prediction Interval
Aptitude Predicted lower upper lower upper Leverage

493.557 479.908 507.206 432.519 554.595 0.053

X Y

Calls Sales
6 19
12 38
14 34
10 24
20 47
22 38
25 60
27 53
29 70
51 46
33 59
36 63
37 70
42 67
44 53
48 57
52 33
 Week 4 – More Hypothesizing and some real practical material Hypothesis testing is what you do The steps we use in this course are: Know the Ho (Null Hypothesis or current situation) Know the Alpha value Enter into MegaStat the H1 (Research Hypothesis) Enter the data. Make a decision on the Ho, based on the result of the “p-value” computation of H1 First we do the Ho testing using the Z or “t” for one or two groups Example An instructor wants to know if the mean entrance exam score of his class of 34 students . is significant 115 1295 112 1326 102 1006 1206 1279 123 122 1301 124 987 104 1177 Note: since the N > 30 one uses the Z test 1040 1266 1345 1230 1239 1434 1385 114 101 1182 1012 121 113 1120 Decision – since p-value > Alpha fail to reject Ho 1 277 Mean score is not statically significant above 1150 992 119 1181 109 Another example An instructor wants to know did his On-campus students scored differently than his On-line students. Alpha = .075 87.13 76.93 10.200 82.810 3.323 Decision: since the p-value < Alpha one rejects the Ho and can say there is a difference. Note since one did not test for greater or less, one can not say which scored higher based upon this test Your turn A shopping center developer wants to create a development in a particular area only and only if the mean income of the homes in the immediate vicinity is greater 80 thousand dollars. Alpha = .025. Does the developer build in this area? 106 A developer wants to build a shopping center near Briar Hills, IF the homes there are more expensive that the homes in Englewood. Test using Alpha = .05. 328.1 330.9 368.6 350.7 306.8 300.2 348 297.7 399 353.3 338.6 283.2 337.3 271.6 349.6 353.7 314.4 307.4 275.8 347.4 343 319.7 339.3 298.1 339.2 FYI – you conduct similar test on Proportions A professional basketball player has a 62% free throw percentage. Since making a change in his technique he has hit 73% out of 52 free throws. Is this evidence that his change has helped? Alpha = .05 47.4% of 135 men say they would purchase a particular product, 35.8% of 148 women say they would purchase the product. Are these percentages different? Do the appropriate test. Alpha =.05 Now we statistical test when there are more than two groups (ANOVA test) there are several different ANOVA one can use, The version one uses depends upon the groups being tested. In this class we do a simple straight forward test of the means Given the sales in the three regions shown below. Test if there are difference in the average sales by region 70.2 85.3 71.9 5.964 724.5440 12.67 p-value 9 < .05 so reject Ho 57.1897 There is a difference in means FYI – these p-values show which groups are different .0052 .9216 2.95 2.68 Click in cell A1 to return to the Index. No. Price SubDiv 480.1 397.8 413.0 389.3 331.0 381.2 42 427.3 Burbsville Lone Tree Stanton 380.6 439.6 249.8 248.0 376.5 320.4 341.8 455.9 273.7 283.8 381.4 382.8 419.6 336.8 391.4 387.0 412.9 290.7 343.0 452.2 224.7 407.4 278.0 350.6 328.4 401.8 235.0 357.9 475.7 257.7 283.0 399.2 245.0 192.9 258.4 298.3 227.0 224.1 262.0 433.8 333.3 346.2 299.7 407.0 272.3 380.9 414.9 69 354.6 415.1 381.6 452.3 296.7 451.4 280.1 248.2 411.4 500.0 316.8 406.8 267.7 247.5 345.5 207 276.5 309.7 511.0 460.2 411.7 383.3 392.3 450.9 341.6 379.1 197.8 390.3 296.2 390.2 348.8 386 475.5 108 385.3 263.6 110 200.5 111 202 341.4 452.4 332.0 430.8 421.6 429.5 405.6 277.0 239.9 457.3 Now a statistical used with Nominal Data (means are not involved) Chi-Square Test Given the sample of units sold in four regions, are the number of units sold in the four regions uniformly distributed? test with Alpha = .05 193 note the fo gives the actual data while the fe gives the expected values if there were an = distribution 184 West 193.000 0.254 6.94 202.000 184.000 1.391 38.01 221.000 -21.000 3.660 .3005 decision: since p-value > .05 fail to reject. the units are evenly distributed. another example This table shows the computer ownership of a sample of GRaduate and UnderGraduate students. Are the factors independent? Calculate the expected frequencies and perform a chi-square test: Alpha = .05 observed frequencies Own PC UG GR totals 49 63.61 32.39 114 150 163 246 246.00 326 984 984.00 16.31 decision; Since p-value < Alpha of .05 reject Ho, there is a difference This table is a result of a sample of 386 managers from small, medium, and large companies who were asked by a local university if they planned to pursue an MBA degree in the next five years. Test to see if there are differences – Alpha =.025 Size of company Small Medium Large Plan MBA? 179 Now we move on to Regression, scatter plot and correlation – they are all tied together We previously made some scatter plots. Now we move forward and show how it is related to Correlation and regression These data show the relationship between a sales aptitude test (X) and Sales in thousands (Y). A. Use MegaStat to do a Regression Analysis Note – the in depended variable is on horizontal axis 0.8218873019904539 ± .456 ± .575 Note the r > than the critical values so it Also note .822^2 = 0.675684 Which is R R is the correlation squared and is the relationship (%) between variable Now regression – note where we typed the 107 28,138.01 795.1265 41,655.16 95% lower 335.9221 27.3148 12.298 0.9507 1.9957 107 Use the following data to compute a (1) Scatter plot, (2) a Correlation – state if r is statistical significant, (3) state the relationship between the variable in percent, and (4) forecast/predict the sales of sales person that makes 22 Calls

&P of &N

Comparison of Groups
North South Central East 87.0 82.4 60.1 54.2 82.9 76.0 65.7 70.2 91.1 67.2 62.3 67.4 85.3 74.6 80.4 71.9 79.0 80.5 64.5 59.6 74.4 59.5 75.3 67.8 76.9 84.4 77.0 54.6 85.6 59.8 79.0 66.4 84.0 61.1 79.2 61.4 91.8 82.5 78.8 61.4 82.6 82.1 62.3 73.4 68.5 82.83333333333333 72.8 73.12727272727273 63.80833333333334 73.1577777777778 73.1577777777778 73.1577777777778 73.1577777777778

73.0 75.0 83.0 83.0 85.0 85.0 89.0 93.0 93.0 103.0 105.0 107.0 117.0 120.0 129.0 141.0 147.0 153.0 155.0 462.0 447.0 408.0 435.0 483.0 486.0 465.0 480.0 507.0 474.0 512.0 456.0 468.0 541.0 524.0 522.0 594.0 581.0 537.0 Aptitude
Sales

Given the sample of prices, are the prices different in the different subdivisions of the RealEstateData? Sort on subdivsion and form the groups below. Do the analysis with MegaStat.