Question

Use Excel to Answer.

A random sample of six cars from a particular model year had the fuel consumption figures, measure in miles per gallon, shown below.

1. Using T.INV, find a 90% confidence interval for the population mean of fuel consumption for cars of this model year. Assume the distribution is normal.   (Round to 2 digits, since our raw data below are expressed with one digit.) (Show work in space provided.)

(ii) Calculate the same interval using CONFIDENCE.T. (Show work in space provided.)

(iii) Calculate the same interval using T.INV.2T.

(Note: when calculating the lower and upper bounds of the interval, do not use a rounded mean that you’ve calculated with AVERAGE. Use the unrounded mean.   Round only the final answers, which are the lower and upper bounds of the interval. Also, make sure to use STDEV.S to calculate the sample standard deviation.)

              Data:

              28.6

              18.4

              19.2

              25.8

              19.4

              20.5

Answer 1

ANS.

Obs.	X
1	28.6
2	18.4
3	19.2
4	25.8
5	19.4
6	20.5
SUM =	131.9
MEAN =	21.98333
STD. DEV.=	4.190664

alpha=0.10, size=6

We find mean and sample standard deviation using Excel formula

For mean:

=AVERAGE(B2:B6) and then press ENTER , Excel returns value =

21.98333

For sample standard deviation:

=STDEV.S(B2:B6)  and then press ENTER , Excel returns value =

4.190664

(i) Confidence Interval using CONFIDENCE.T:

=CONFIDENCE.T(alpha,standard_dev,size)

=CONFIDENCE.T(0.1,4.190664,6) and then press ENTER , Excel returns value =

3.4474082

Then we can find Confidence interval using formula =AVERAGE(B2:B6) +/-CONFIDENCE.T(0.1,4.190664,6)

Lower Bound =AVERAGE(B2:B6) - CONFIDENCE.T(0.1,4.190664,6) ,excel returns value =

18.53593

Upper Bound =AVERAGE(B2:B6) + CONFIDENCE.T(0.1,4.190664,6) ,excel returns value =

25.43074

90% Confidence Interval using EXCEL function CONFIDENCE.T

Lower Bound =	18.54
Upper Bound =	25.43

(ii) Confidence Interval using T.INV.2T EXCEL function:

Probability = 0.10 , Degree of freedom = size-1 = 6-1 = 5

First we find critical t-value using T.INV.2T:

=T.INV.2T(probability,deg_freedom)

=T.INV.2T(0.1,5)  and then press ENTER , Excel returns value =

2.0150484

then, we find margin of error using formula in excel

=(T.INV.2T(probability,deg_freedom) *STDEV.S(B2:B6))/SQRT(size)

=(T.INV.2T(0.1,5)*4.190664)/SQRT(6))  and then press ENTER , Excel returns value =

3.4474082

again,

Then we can find Confidence interval using formula =AVERAGE(B2:B6) +/-(T.INV.2T(0.1,5)*4.190664)/SQRT(6))

Lower Bound =AVERAGE(B2:B6) -(T.INV.2T(0.1,5)*4.190664​​​​​​​)/SQRT(6))  ,excel returns value =

18.53593

Upper Bound =AVERAGE(B2:B6) + (T.INV.2T(0.1,5)*4.190664​​​​​​​)/SQRT(6))  ,excel returns value =

25.43074

90% Confidence Interval using EXCEL function T.INV.2T

Lower Bound =	18.54
Upper Bound =	25.43

Here, is an EXCEL snapshot:-