help with MIS

E_CH08_EXPV2_A1_Instructions.docx

Office 2013 – myitlab:grader – Instructions Exploring Series Vol. 2, Chapter 8, A1

Statistical Functions

Project Description: You are the manager of a local gym and you will use excel to perform statistical analysis investigating the relationship between weight loss and workouts with a personal trainer.

Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Download and open the file named exploring_e08_grader_a1, and then save the file as exploring_e08_grader_a1_LastFirst, replacing “LastFirst” with your name. 0 2 Enter a conditional function in cell L4 to calculate total members 25 years old or younger. Format the results with Number format and no decimal points. Hint: On the FORMULAS tab, in the Function Library group, click the More Functions button, and then click Statistical. To format the cell, on the HOME tab, in the Number group, click the Number Format dialog box launcher. 5 3 Enter a similar function in cell L5 to calculate the total number of members over the age of 25. Hint: On the FORMULAS tab, in the Function Library group, click the More Functions button, and then click Statistical. 5 4 Enter a conditional function in cell M4 to calculate the total membership fees paid by members 25 years old or younger. Hint: On the FORMULAS tab, in the Function Library group, click Math & Trig. 5 5 Enter a similar function in cell M5 to calculate the total number of membership fees paid by members over the age of 25. Format the results in M4 and M5 with Accounting Number Format. Hint: On the FORMULAS tab, in the Function Library group, click Math & Trig. 10 6 Enter a function in cell L9 that calculates the total number of members 25 years old or younger that lost at least 5 pounds. Hint: On the FORMULAS tab, in the Function Library group, click the More Functions button, and then click Statistical. 5 7 Enter a similar function in cell L10 that calculates the total number of members over the age of 25 that lost at least 5 pounds. Hint: On the FORMULAS tab, in the Function Library group, click the More Functions button, and then click Statistical. 10 8 Enter a function in cell H4 that calculates the rank of the weight loss in cell G4 against the range of individual weight lost in the data set. Hint: On the FORMULAS tab, in the Function Library group, click the More Functions button, and then click Statistical. Enter the function as =RANK.AVG(G4,G$4:G$53). 10 9 Use the fill handle to copy the function down column H. Be sure to include the appropriate absolute or mixed cell references before copying the functions. Hint: Use the fill handle to copy the function. 10 10 Enter a function in cell K13 to calculate the correlation of column G and I. Hint: On the FORMULAS tab, in the Function Library group, click the More Functions button, and then click Statistical. 5 11 Format the results as Number format with three decimal positions. Hint: On the HOME tab, in the Number group, click the Number Format dialog box launcher. 5 12 Click the DATA tab and select Data Analysis. Select Descriptive Statistics and click OK. Complete the input criteria using the weight loss data in column G. Set the Output functions to display in cell K15 .(Hint: be sure to output Summary Statistics). Hint: On the DATA tab, in the Analysis group, click Data Analysis. 10 13 Click the DATA tab and select Data Analysis. Select Histogram and click OK. Use the ages in column C as the input range. Use the age range K4:K5 as the bin range. Output the data to a new worksheet. Be sure to include a chart with the output. Hint: On the DATA tab, in the Analysis group, click Data Analysis. 15 14 Name the new worksheet Histogram. Move the Member Information worksheet so that it is first in the workbook. Hint: Double-click the sheet tab to rename it. Click the sheet tab and drag it to the new position. 5 15 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Membership Information; Histogram. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0 Total Points 100

Updated: 07/17/2013 1 E_CH08_EXPV2_A1_Instructions.docx