Ex $6-68$ Excel $|$ Module $6$ Managing Data with Data Tools

$7.$ Insert the Wait Time field in column L$.$ Calculate how many minutes each patient had to wait by using your mouse to enter a formula that multiplies the value of the difference between the Exam Start $($cell $15)$ and Appt Time $($cell A$5)$ fields by $24**60($to express the difference in minutes$).$ Format the calculated values with Cl $.$ Calculate cell format. mouse to enter a formula that mulated values with the Getween Exam End and Exam Start fields by ${24}^{**}60.$ Format the calculat and Physician fiel format.

$9.$ Insert slicers for the Department and Pepartment slicer and the slicers to the Dashboard worksheet. Resize the Deped range A$8$: ${?}^{10}$ merged range A$4$:D$7.$ Resize the Physician slicer to cover the merg of both slicers to $5$ columns.

Explore $10.$ On the Slicer tab, in the Slicer grewo, ins andex, change the caption of the Department slicer to Treating Department and wien shician slicer to Examining Physician.

Explore $11.$ Select the Department slicer, and the settings button in the Slicer group to open the Slicer Settings dialog box. Clicle wies with no data check box to hide department names when they are not releva Physician slicer.

$12.$ Calculate the following summary statistics in the Dashboard worksheet:

a$.$ In the merged cell A$19,$ show the number of appointments behind schedule by applying the SUBTOTAL function with the SUM function to the Patient Wait field of the Appointments table.

b$.$ In the merged cell B$19,$ show the number of appointments that were on time by applying the SUBTOTAL function with the COUNT function to the Appt Time field of the Appointments table and subtracting the value of cell A$19.$

c$.$ In the merged cell C$19,$ use the SUBTOTAL function to calculate the average value of the Wait Time field in the Appointments table.

d$.$ In the merged cell D$19,$ use the SUBTOTAL function to calculate the average value of the Visit Length field in the Appointments table.

$13.$ Insert a pie chart of the data in the range $A18$:$B23,$ move the chart to cover the range $A25$:$B34.$ Remove the chart legend but add Data Callouts data labels showing the percent of each category. $($Hint: If you don't see two categories in the pie chart, on the Chart Design tab, in the $-$ Data group click the Switch Row$/$Column button.$)$

$14.$ Create a histogram of the data in the Wait Time column in the Patient Log worksheet. Move the histogram to the Dashboard worksheet and then resize the chart to fit in the range $C25$:$C34.-$ Change the chart title to Waiting Time. Add data labels. Set the bin width to $5,$ the Overflow bin value to $10,$ and the Underflow bin value to $0.$

$15.$ Create a histogram of the data in the Visit Length column in the Patient Log worksheet. Move the histogram to the Dashboard worksheet and resize it to fit in the range D$25$:D$34.$ Change the chart title to Length of Visit and add data labels. Set the bin width to $10,$ the Overflow bin value to $60,$ and the Underflow bin value to $20.$

$16.$ Test the slicer buttons by showing the results for Dr$.$ Jacob Leiva of the Internal Medicine department.

$17.$ Save the workbook, and then close it$.$