Exploring Series Vol. 2, Chapter 8, H1 Statistical Functions

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_h1, and then save the file as exploring_e08_grader_h1_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.    5
3    Enter a similar function in cell L5 to calculate the total number of members over the age of 25.    5
4    Enter a conditional function in cell M4 to calculate the total membership fees paid by members 25 years old or younger.    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.    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.    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.    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.  Use RANK.AVG; use absolute references for the column argument.
    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.    10
10    Enter a function in cell K13 to calculate the correlation of column G and I.    5
11    Format the results as Number format with three decimal positions.    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).    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.    15
14    Name the new worksheet Histogram. Move the Member Information worksheet so that it is first in the workbook.    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