Appendix 8.1 One-Sample Hypothesis Testing Using Excel 1 Appendix 8.1 ■ One-Sample Hypothesis Testing Using Excel The instruction block in this section begins by describing the entry of data into an Excel spreadsheet. Alternatively, the data may be downloaded from this book’s website. Please refer to Appendix 1.1 for further information about entering data, saving data, and printing results. Hypothesis test for a population mean: The Data Analysis ToolPak in Excel does not explicitly provide for one-sample tests of hypotheses. A one-sample test can be conducted using the Descriptive Statistics component of the Analysis ToolPak and a few additional computations using Excel. Descriptive statistics: • Enter the interest rate data into cells A2:A16 with the label Rate in cell A1. • Select Data : Data Analysis : Descriptive Statistics. • • Click OK in the Data Analysis dialog box. In the Descriptive Statistics dialog box, enter A1.A16 into the Input Range box. • Place a checkmark in the “Labels in first row” check box. • Under output options, select “New Worksheet Ply” to have the output placed in a new worksheet and enter the name Output for the new worksheet. • Place a checkmark in the Summary Statistics checkbox. • Click OK in the Descriptive Statistics dialog box. The resulting block of descriptive statistics is displayed in the Output worksheet and the entries needed to carry out the test computations have been entered into the range D3:E6. Computation of the test statistic and p-value: • In cell E7, use the formula ⫽ (E3 ⫺ E4)Ⲑ(E5ⲐSQRT(E6)) • to compute the test statistic t (⫽ ⫺4.970). Click on cell E8 and then select the Insert Function button fx on the Excel toolbar. • In the Insert Function dialog box, select Statistical from the “Or select a category:” menu, select TDIST from the “Select a function:” menu, and click OK in the Insert Function dialog box. • In the TDIST Function Arguments dialog box, enter abs(E7) in the X window. • • Enter 14 in the Deg_freedom window. Enter 1 in the Tails window to select a onetailed test. • Click OK in the TDIST Function Arguments dialog box. • The p-value related to the test will be placed in cell E8.
© Copyright 2020