STATSTICS AND PROBABILITY

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.

Part II: Hypothesis Testing: Five Required Steps (Instructor’s Example)

Step 1

· Hypotheses

· This a two tailed test where the null hypothesis selects equal (=) sign whereas the alternate hypothesis selects not equal sign (≠) or different values than the value selected by the null hypothesis.

· Null Hypothesis (H0): The average body fat of the men attending Silver’s Gym is 20% of their body weight.

Alternate Hypothesis (H1): The average body fat of the men attending Silver’s Gym is different than 20% of their average body weight

Step2

· Let Alpha Level (Level of Error) = 0.05 (5%).

Step 3

· Calculate P-Value (You can post the calculated

· P-Value from your EXCEL spreadsheet)

· You will use EXCEL spreadsheet in Part 2 to calculate the P-Value. In this presentation, I will show how The P-value can be obtained manually.

· We must first calculate the Z statistic as follows:

· Z statistic calculation:

· Now, we must use the negative Z Table (Page 1) inside Week 3 MyStatsLab to find the related probability. We find that:

· For Z = -2.04,

· P = 0.04144 (P value). This is calculated probability of error on both tails.

· Z statistic calculation:

Now, we must use the negative Z Table (Page 1) inside Week 3 MyStatsLab to find the related probability. We find that:

For Z = -2.04,

P = 0.04144 (P value). This is calculated probability of error on both tails.

Step 4

· Compare Calculated P-Value to Alpha level of error (significance level of the test).

· Notice that for a two-tailed test, half of the alpha level of error falls on the end of the right tail and the other half is placed on the far left of the left tail. Hence, for one tail:

· 0.05 ÷ 2 =0.025

· Here is a standard normal Z distribution with Alpha error level of 5% (0.05) with half (0.025) on the right tail and the other half (0.025) on the left tail.

· Comparison

· 0.0414 < 0.05

(Calculated P-value for two tails is less than the Alpha Error level or level of significance of 5

Step 5:

· Comparison and Conclusion:

· We compare the calculated P-Value of Z-Statistic versus Alpha error level of 5% related to two tails of normal distribution:

· I) If calculated P-Value > 0.05 (more than), Then,

· we cannot reject the Null Hypothesis, and our decision will be based on the null hypothesis.

· II) If calculated P-Value < 0.05 (less than), Then,

· we must reject the Null Hypothesis, and our decision will be based on the alternate hypothesis.

· Based on the results of calculations for the instructor’s example:

· 0.0414 < 0.05

· (Calculated P-value for two tails is less than the Alpha Error level of 5%).

· This means that there is some significant factor but chance that impacts the change in body fat of Silver Gym members.

· Conclusion

· We must reject null hypothesis.

· Decision

· We can claim with 95% confidence that the average body fat of the men at the Gym will be different than 20% of their body weight.

04

.

2

252

61

.

6

20

15

.

19

tan

-

»

-

=

-

=

Z

Size

Sample

Deviation

dard

S

Mean

Population

Mean

Sample

Z