Question

A problem with a phone line that prevents a customer

from receiving or making calls is upsetting to both the customer

and the telecommunications company. The file Phone

contains samples of 20 problems reported to two different

offices of a telecommunications company and the time to

clear these problems (in minutes) from the customers' lines:

Central Office I Time to Clear Problems (minutes)

1.48 1.75 0.78 2.85 0.52 1.60 4.15 3.97 1.48 3.10

1.02 0.53 0.93 1.60 0.80 1.05 6.32 3.93 5.45 0.97

Central Office II Time to Clear Problems (minutes)

7.55 3.75 0.10 1.10 0.60 0.52 3.30 2.10 0.58 4.02

3.75 0.65 1.92 0.60 1.53 4.23 0.08 1.48 1.65 0.72

a. Assuming that the population variances from both offices

are equal, is there evidence of a difference in the mean

waiting time between the two offices? (Use a = 0.05.)

b. Find the p-value in (a) and interpret its meaning.

c. What other assumption is necessary in (a)?

d. Assuming that the population variances from both offices

are equal, construct and interpret a 95% confidence interval

estimate of the difference between the population

means in the two offices.

PLEASE SHOW THE ANSWERS IN EXCEL ONLY

Time Location

1.48 1

1.75 1

0.78 1

2.85 1

0.52 1

1.60 1

4.15 1

3.97 1

1.48 1

3.10 1

1.02 1

0.53 1

0.93 1

1.60 1

0.80 1

1.05 1

6.32 1

3.93 1

5.45 1

0.97 1

7.55 2

3.75 2

0.10 2

1.10 2

0.60 2

0.52 2

3.30 2

2.10 2

0.58 2

4.02 2

3.75 2

0.65 2

1.92 2

0.60 2

1.53 2

4.23 2

0.08 2

1.48 2

1.65 2

0.72 2

I am having trouble doing these calculations in excel, can you please show me how to do the calculations in excel?

Answer 1

(a) and (b) :

Null Hypothesis H0 : There is no significant difference between the Population means i.e

Alternate Hypothesis H1 : There is significant difference between the Population means i.e

In excel,

1.We can calculate mean using "=AVERAGE(B2:B21)"

2.We can calculate standard deviation using "=STDEV.S(B2:B21)"

3. We can find the pooled standard deviation using " =(D22+E22)/(20+20-2)" and then square root it using "=sqrt()"

4.Then to perform t test use " =(B23-C23)/(B27*SQRT((1/20)+(1/20)))"

5. To find the p-value use " =T.DIST.2T(t,df)" where t is the calculated t-value and df is the degrees of freedom

6. The t-critical value is calculated using "=T.INV.2T(0.05,38)" where alpha=0.05 and df=38

	Office I	Office II	(x1-x1bar)^2	(x2-x2bar)^2
	1.48	7.55	0.539	30.675
	1.75	3.75	0.215	3.022
	0.78	0.1	2.056	3.654
	2.85	1.1	0.404	0.831
	0.52	0.6	2.870	1.992
	1.6	0.52	0.377	2.225
	4.15	3.3	3.748	1.660
	3.97	2.1	3.084	0.008
	1.48	0.58	0.539	2.049
	3.1	4.02	0.785	4.034
	1.02	3.75	1.426	3.022
	0.53	0.65	2.836	1.854
	0.93	1.92	1.649	0.008
	1.6	0.6	0.377	1.992
	0.8	1.53	1.999	0.232
	1.05	4.23	1.355	4.922
	6.32	0.08	16.859	3.731
	3.93	1.48	2.945	0.282
	5.45	1.65	10.472	0.131
	0.97	0.72	1.548	1.668
Sum	44.280	40.230	56.081	67.992
Mean	2.214	2.0115
std	1.718039	1.891706
variance	2.951657	3.57855
s^2	3.265104
s	1.80696
t	0.354386
p-value	0.725009
t-critical	2.024394

Since, The t-value is 0.35439. The p-value is .725009. The result is not significant at p < .05

(c)

The assumptions made for conducting this test are :

1. Parent populations are normally distributed.
2. The two populations are independent.
3. Variance of the two populations are same.

(d)

The Confidence - Interval for the difference of means t -test is given by:

Where is the level of significance. So, we have

Therefore, the confidence interval is ( - 0.9543 , 1.3593 )