Homework probability and statistics

ACTIVITY INSTRUCTIONS: You will submit only one file, i.e., your final Excel file. Therefore, be sure to complete each step of the activity in the relevant Excel file, showing all your work and answering any questions in the text boxes provided.

HW: Probability

Use the Excel file titled HW_Probability.xlsx for this activity. See the Documentation worksheet (tab) for a brief description of the current data file and its contents.

Submit only your final Excel file as your solution.

1. In the Excel worksheet titled “Data” insert a new column directly to the right of the "OR Base" column. Call this column the "Surgery" column. Using the IF function, make it so that anytime a 0 occurs in the "OR Base" column it returns a "No" and if it shows any other number other than 0, it returns a "Yes". This will allow you to easily separate patients that have had surgery and those who have not.

2. Generate a pivot table using the entire dataset. Place the PivotTable on the existing worksheet titled “Pivot” in cell A
   1. In the pivot field list, move the "Surgery" variable you just made in question 1 to the rows box and the "Race" variable to the columns box. Then take the "ID" variable and move it to the values box.
   2. Once you have finished generating your pivot, delete rows 8 through 20 so there is less space between your pivot and the tables below it.

3. In the table called "A" manually calculate the marginal probabilities in the ORANGE cells and the joint probabilities in the GREEN cells. Be sure to use relative cell references in your formulas.

4. Briefly explain what the marginal and joint probabilities you just calculated are telling you.
5. Copy the entire pivot table (A2:G6) at the top of the "Pivot" spreadsheet and paste it next to table "A" with the top left corner being pasted in cell "I9". Recreate table "A" from question 3 on this new pivot table using the pivot table capabilities.

6. In table "B" calculate the joint probabilities that show if you had surgery/no surgery OR your specific race. Your calculations should go in the GREEN cells. The ORANGE cells should be the sum of what you just calculated in the GREEN cells. You will know you calculated everything correctly if cell “G19” sums to 6 [it will sum to 6 because you take the total number of rows (2), then add the total number of columns (5), and then subtract (1)]. Be sure to use relative cell references in your formulas. Briefly explain what the probabilities mean that you calculated in the GREEN cells.

7. In table "C" calculate the conditional probabilities for COLUMN totals. Be sure to use relative cell references in your formulas. Briefly explain what the probabilities mean that you calculated in the GREEN cells.

8. Copy the original pivot table (A2:G6) at the top of the "Pivot" spreadsheet and paste it next to table "C" with the top left corner being pasted in cell "I22". Recreate table "C" from question 7 on this new pivot table using the pivot table capabilities.

9. In table "D" calculate the conditional probabilities for ROW totals. Be sure to use relative cell references in your formulas. Briefly explain what the probabilities mean that you calculated in the GREEN cells.

10. Copy the original pivot table (A2:G6) at the top of the "Pivot" spreadsheet and paste it next to table "D" with the top left corner being pasted in cell "I29". Recreate table "D" from question 9 on this new pivot table using the pivot table capabilities.      

11. Consider the normal distribution. What percent of the data values fall between:
    1. +- 1 Standard Deviation?
    2. +- 2 Standard Deviations?
    3. +- 3 Standard Deviations?