Math

Guidelines to complete required EXCEL calculations for Phase 4 Individual Project of MATH301

Hello MATH301 Online Students,

I would like to provide some guidelines to help you with EXCEL calculations using EXCEL formulas for Phase 4 Individual Project, in case you need additional help after viewing the live chat presentation. Please follow these guidelines exactly as instructed and contact me if you have further questions.

-----------------------------------------------------

First download the spreadsheet with data related to body fat (in percent) and body weight (in pounds) of Silver Gym members. Then, download both live chat power point presentation with instructions how to complete the tasks of the assignment as well as the instructor’s spreadsheet with similar data and calculated values showing the EXCEL formulas used.

The location of data table inside instructor’s spreadsheet is exactly the same as that of the Task List with the main data table to be used for calculations. Use the spreadsheet downloaded from Task List with body fat weight data table for Silver Gym. It is the best to create a second spreadsheet within the same EXCEL posting for Phase 4(IP). Part 1 to be used for statistical measurement and the second Part 2 to be used for calculation of P-value for hypothesis testing.

Part 1: Statistical Measurements

Here is how to obtain the mean or algebraic average of body fat and body weight of 252 Silver Gym members. Notice column “A” only shows the members’ ID numbers and it cannot be used for calculations. Column “B” of data table represents body fat of members in percent while column “C” of data table represents body weight of members in pounds.

Also, we notice that both columns B and C data values begin on Row 2 and ends on Row 253. Notice that Row 1 is used to show the headings or titles of each column. So, for all calculations, we need to enter the following EXCEL format inside the EXCEL formula window to represent the entire data for body fat and body weight.

B2:B253 (for all body fat data values from cell B2 through B253)

C2:C253 (for all body weight data values from cell C2 through cell C253)

Mean values

1.Place the cursor in empty cell to the right side of the data table (in similar cell location as shown inside instructor’s spreadsheet).

2.Open “Formula” tab located at the top of the spreadsheet.

3.Open the tab appearing as “more function” with orange color background by placing the cursor on this tab by holding down the left click of mouse to open “more function” tab.

4.Open “ Statistical” from the list that appears at this time.

5.From the vertical menu that opens at this time (in alphabetical order) select AVERAGE tab in order to find the mean or average of the body fat. Open the AVERAGE tab.

6.From the widow that pops up at this time, you will see two small rectangular windows at the top. Select the small window at the top. Place the cursor inside this window and exactly type in: B2:B253

7.Ignore the second small window, and open “OK” tab.

8.Now, move the cursor to inside spreadsheet inside a remote and blank cell.

9.At this time, you should be able the calculated value for mean of body fat inside the cell you initially selected.

In order to obtain the calculated value for mean of the body weight, follow exactly the steps stated above by typing the following inside the opened window in step 6:

C2:C253

Median

In order to find the median of the body fat and body weight, follow the 9 steps stated above except in step 5, select the following function tab from the appearing vertical menu:

MEDIAN

Once again use B2:B253 for median of body fat and C2:C253 for median of body weight of the Gym members.

Standard Deviation

In order to find the standard deviation of the body fat and body weight, follow the 9 steps stated above except in step 5, select the following function tab (not other similar tabs) from the appearing vertical menu:

STDEV.S

Once again use B2:B253 for standard deviation of body fat and repeat this process with C2:C253 for standard deviation of body weight of the Gym members.

Range

Notice that there is no quick formula available inside EXCEL statistical function menu for range. Recalling, from live chat power point presentation, range is the difference between the greatest and smallest value of the data set. Hence, we must subtract the smallest value from the largest value in each column B and C of the data table to find the range for body fat and body weight, respectively. The exact formula to be created is shown below:

For range of body fat:

MAX(B2:B253)-MIN(B2:B253)

For range of body weight

MAX(C2:C253)-MIN(C2:C253)

Notice in case you type in these formulas inside the function window at the top, you might receive an error message inside the selected cell location. In order to avoid this issue, I suggest you follow these simple guidelines:

1.For range of body fat, locate the cursor on an empty cell location.

2.Manually type in the exact following statement (must include equal sign first):

=MAX(B2:B253)-MIN(B2:B253)

3.Move the cursor to a remote and empty cell location inside your spreadsheet and place it inside that empty cell for a moment by pressing on left click of your mouse in order to avoid error message by EXCEL.

4.At this time, you should be able to see the calculated range of body fat for your data table, in percent.

For range of body weight

Follow the steps stated above exactly except in step 2, you need to manually type in

=MAX(C2:C253)-MIN(C2:C253)

This will provide you with the range of body weight of Gym members, in pounds, for your data table inside your spreadsheet.

--------------------------------------------------------------

Part 2: Hypothesis Testing

 You will use the spreadsheet, below Part 1, to perform the P-value calculations related to Part 2: Hypothesis testing about the claim or hypothesis made by the Gym’s manager.

P-Value Calculations

Population Mean of Body Fat

1. Place the cursor inside cell location H20 and manually type in “20” for mean of body fat population. Make sure you move the cursor after typing to a remote area and place it in a blank cell location and left click there in order to avoid an error message.

Standard deviation of Body Fat

2. Place the cursor inside cell H22. Then, find the standard deviation of body fat the way explained in part I inside spreadsheet 1.

P-Value Calculation

3. Place the cursor inside cell location H24. Then open “Formulas” tab at the top of spreadsheet. Then, open “more functions” tab. Then, open “Statistical” tab from the appearing list. Then, from the vertical menu that pops up on the right side, select Z-TEST function at the bottom of the vertical menu. A window pops up with three small windows.

4. Type in “B2:B253” inside the top window to represent the array of data for body fat.

5. Place the cursor inside the middle window and type in “20”. This represents the population mean of body fat of 20.

6. Place the cursor inside the lower or third window and type in “H22”. This represents the standard deviation.

7. Now, open the “OK” tab at the bottom of the large window.

8. You should be able to see the cumulative probability of error inside cell location H24.

9. Place the cursor in cell location “H27” and manually type in “ =1-H24 ”. This will find the complement of the probability found for F7 location.

10. Place the cursor in cell “H29” and manually type in “ =2*H27 ”. This will provide the P-Value for two tails of normal distribution.

11. You need to use this P-value (in H29 cell location) inside the word document to perform the hypothesis test in part 2 the way explained inside power point presentation.

This will complete all calculations for two parts of Phase 4(IP) assignment. Please make sure you download and read the Power Point presentation for live chat presentation posted under Instructor Files to learn how to respond to Task List questions for two parts based on the calculated results from EXCEL spreadsheet. In Part 2 inside your Word document, you need to show the required five steps to perform the hypothesis two tailed test as discussed and explained inside the posted power point presentation. Please place all your discussion and responses inside your Word Document and not inside spreadsheet as the EXCEL spreadsheet is only to show the calculations as explained. Also, include reference citation for using information from other sources in your discussion and use the OV too to obtain your similarity score followed by reducing your similarity score as required by CTU.

After following these instructions and viewing the live chat presentation, please contact me if you have specific questions about this assignment.

Please do not use the instructor’s spreadsheet to submit your EXCEL attachment. You must use the EXCEL spreadsheet with Silver Gym’s data table downloaded from Task List.