Excel functions – Excel does not always have a straight-forward way of getting the test statistic and p- value for a one-sample test of hypotheses. The commands below should work. In each case replace data range with a valid Excel cell reference, e.g. A12:A37.

 Z-test (𝜎 known)
o To test: 𝐻0 : 𝜇 = 𝜇0 versus 𝐻𝐴 : 𝜇 > 𝜇0

 =z.test(data range,𝜇0,𝜎) – This finds the p-value of the test =norm.s.inv(1–pvalue) – This finds the test statistic

o To test: 𝐻0 : 𝜇 = 𝜇0 versus 𝐻𝐴 : 𝜇 < 𝜇0
 =1–z.test(data range,𝜇0,𝜎) – This finds the p-value of the test =norm.s.inv(pvalue) – This finds the test statistic

o To test: 𝐻0 : 𝜇 = 𝜇0 versus 𝐻𝐴 : 𝜇 ≠ 𝜇0 =2*z.test(data range,𝜇0,𝜎)

 If this value is less than 1, then it is the p-value. In this case the test statistic is given by =norm.s.inv(1–(pvalue/2))

 If the above value is greater than 1, then you must use the following command instead =2*(1–z.test(data range,𝜇0,𝜎))

 In this case the test statistic is given by =norm.s.inv(pvalue/2)

 T-test (𝜎 unknown) – Excel does not have a function to perform a one-sample t-test, but there is a work-around that will produce the needed output.

o Enter the actual data into column A. Enter copies of 𝜇0 into column B so that it’s the same length as column A.

o From the menu select: Data → Data Analysis → t-Test: Paired Two Sample for Meanso Complete the necessary information in the dialog box.

 Variable 1 Range = cell-reference to column A cells Variable 2 Range = cell-reference to column B cells Hypothesized Mean Difference = 0

o Position the output to the right of the data (e.g. cell D7). To make the output more readable, widen the columns by highlighting the columns then right-click → column width → 15.

o Before printing make sure the output will fit on one page. From the menu select: View →Page Break Preview and drag the dashed page break so as to fit all the output onto one page.

1

Problem 1 – Biodiesel fuels are made from vegetable oils and animal fats and may be used instead of conventional diesel fuel. One advantage to using biodiesel is a possible decrease in regulated emissions, specifically total hydrocarbons (HC). Using the heavy-duty transient Federal Test Procedure (FTP), the mean HC emission is 0.23 g/hp-hr (grams per horsepower-hour) with standard deviation 𝜎 = 0.07. A random sample of heavy-duty engines was obtained, and each was tested with biodiesel fuel. The data is in the file Excel Homework 5 – Problem 1. Suppose the underlying population is normal. Is there any evidence to suggest the use of biodiesel has decreased the mean level of HC emissions? Use 𝛼 = 0.05.

Create the Output [2 points]

•  Download the data file from the Blackboard page for the course.
•  Make your header – enter your name and information into cells A1 to A5 (just as you did for theprevious Excel homework assignments).
•  Use the appropriate Excel functions to get the test statistic and p-value for the test. Be sure tolabel the values appropriately.Questions – write your answers to the following directly on your output. Be neat and label your answers.

1. (a) Give the null and alternative hypotheses. [1 point]
2. (b) Give the decision for the test. Write a conclusion that relates your decision to the context of theproblem. [2 points]

Problem 2 – Most water parks use a recycling system so that the only water loss is due to evaporation and splashing. Despite a sophisticated recycling system, the Schlitterbahn Waterpark in New Braunfels, Texas, has informed the city water department of their need for 250,000 gallons of water per day. Thecity water department selected a random sample of days, and the park’s water usage (in thousands ofgallons) on each day was recorded. The data is in the file Excel Homework 5 – Problem 2. Is there any evidence to suggest the mean water usage is different from 250 (thousand gallons)? Assume the underlying population is normal and use 𝛼 = 0.05.

Create the Output [2 points]

•  Download the data file from the Blackboard page for the course.
•  Make your header – enter your name and information into cells A1 to A5 (just as you did for theprevious Excel homework assignments).
•  Use the appropriate Excel functions to get the test statistic and p-value for the test. Be sure tolabel the values appropriately.Questions – write your answers to the following directly on your output. Be neat and label your answers.

1. (a) Give the null and alternative hypotheses suggested by the problem above. [1 point]
2. (b) Give the decision for the test. Write a conclusion that relates your decision to the context of theproblem. [2 points]

2