Question

Please use excel to calculate the probability of the given question! Also please use excel to show the steps of the pivot table!

Note that there are 211 total counts for each category of response/division/employment status.

Somewhere on this sheet create a well made PivotTable to calculate the percentage for each response. Then add two Slicers to the PivotTable for the Fields Division and Employment Status. Then in cell B5 show the percentage of NO responses given that respondent was a Full Time Faculty in the Business Department. FiEds Division and Ermmo raculty in the Business Deprment P(No Business AND Full Time) Original Question: Should Advising be Mandatory? Made up data below:

Answer 1

Ans. To create the pivot table, please follow the following steps:

1. In MS Excel, go to Insert --> PivotTable --> PivotTable
2. Select the entire data range, the sheet and cell reference where you want to insert the pivot table and click on ‘Ok’.
3. In the PivotTable Field list, drag the item ‘Response’ to the ‘Row Labels’ and ‘Values’ sections.
4. Click on the arrow on the ‘Response’ item in the ‘Values’ section and click on ‘Value Field Settings’.
5. In the ‘Summarize Values By’ tab, select ‘Count’ and in the ‘Show Values As’ tab, select ‘% of Grand Total’.
6. Once you click on ‘Ok’, the pivot table will be created.
7. Now, go to the ‘PivotTable Tools’ section at the top of the ribbon and from the ‘Options’ tab, click on Insert Slicer --> Insert Slicer.
8. Select the field ‘Division’ and click on ‘Ok.’ The slicer will be created.
9. Repeat steps 7 and 8 for the field ‘Employment Status’.

The final pivot table with slicers will look like this:

Now, Probability of a NO response given that the respondent was a full-time faculty in the Business Department
     = P (NO $\mid$ Business AND Full Time)
     = P (NO $\cap$ Business AND Full Time) / P (Business AND Full Time)
     = (N (NO $\cap$ Business AND Full Time) / Total N) / (N (Business AND Full Time) / Total N)
     = (10/211) / (45/211)
     = 0.047/0.213
     = 0.22