From William E. Becker, Statistics for Business and Economics Using Microsoft Excel 97, S.R.B. Publishing, 1997, p 338. Reproduced with permission of S.R.B. Publishing.
QUERY 9.1: A Wall Street Journal (April 1, 1991) article reported that because of price wars the average retail price for a gallon of self-serve regular unleaded gasoline was only $1.04, with some places charging less than 90 cents per gallon even though market analysts say prices should be higher. As additional information on gas prices, the article reported prices in ten cities:
Atlanta $ 0.94
Los Angeles 0.94
Dallas 0.98
Miami 1.03
San Francisco 1.05
Philadelphia 1.08
Las Vegas 1.12
Long Island 1.15
Boston 1.15
Chicago 1.19 But this sample has an average of $1.063 suggesting that it is not representative of the population, or the national average might be higher than $1.04, or sampling error caused the difference. Do these data refute or support the idea that the national average is greater than $1.04?
ANSWER: The null and alternative hypotheses are
H0: µ is less than or equal to 1.04 and HA: µ is greater than 1.04
THE T TEST IS DONE USING EXCEL FOR CALCULATIONS USING THE FOLLOWING ENTRIES:
0.94 (THESE OBSERVATIONS ON THE 0.94 PRICE OF GASOLINE WERE READ 0.98 INTO EXCEL FROM THE ASCII FILE 1.03 ON THE DISK BUNDLED WITH THE 1.05 TEXTBOOK, WHERE THE FIRST 1.08 OBSERVATION WAS ARBITRARILY 1.12 PLACED IN CELL E51 WITH THE 1.15 LAST IN E60 AND THE COUNT 1.15 COMMAND IN E62.) 1.19 =COUNT(E51:E60) 10 =AVERAGE(E51:E60) 1.063 =STDEV(E51:E60) 0.090191 =E64/SQRT(E62) 0.028521 =(E63-1.04)/E65 0.806425 =TDIST(E66,AE2-1,1) 0.220393
We are using t here because sigma is unknown and the sample is small. Furthermore, assuming that the population is normally distributed does not seem unreasonable. At any of the typical sigma-levels the null hypothesis cannot be rejected because the calculated t = 0.8064 is so small. TO OBTAIN THE P-VALUE THE "TDIST" COMMAND MAY BE USED, WHERE THE FIRST ENTRY IS x, THE SECOND IS df AND THE THIRD IS ONE (1) OR TWO (2) TAILS. AS SHOWN ABOVE, ENTERING "=TDIST(0.8064, 9,1)" YIELDS THE P-VALUE OF 0.22039. Thus, these ten sample observations on the price of gasoline are not inconsistent with the claim that the national average is $1.04 and they do not support the claim that the national average is greater than $1.04.
Go to:
Examples of EXCEL Use
Statistics for Business and Economics WWW Page