Challenge

Case Problem $2$

Data File needed for this Case Problem: NP$\_$EX$\_6-5.$xlsx

Mercy Field Clinic Craig Manteo is the Quality of Care manager at Mercy Field Clinic located in Knoxville, Tennessee. Craig wants to use Excel to monitor daily clinic appointments, looking at how many patients a doctor sees per day and how much time is spent with each patient. Craig is also interested in whether patients are experiencing long wait times within particular departments or with specific doctors. You've been given a works et containing the scheduled appointments from a typical day. Craig wants you to create a dashboard from that day. Complete the following.

Open the NP$\_$EX$\_6-5.$xIsx workbook located in the Excel$6>$ Case$2$ folder included with your Data Files. Save the workbook as NP$\_$EX$\_6\_$Mercy in the location specified by your instructor.

In the Documentation sheet, enter your name and the date.

The Patient Log worksheet lists the entire day's appointments, from $8$ am to $5$ pm at four departments. Convert this data range into an Excel table using Appointments as the table name. Turn off the banded rows style.

In the Dept Lookup, Physician Lookup, and Patient Lookup worksheets, convert each data range to a table, naming them Dept$\_$Lookup, Physician$\_$Lookup, and Patient$\_$Lookup, respectively. You'll use these tables to display names instead of IDs in the Appointments table.

In the Patient Log worksheet, use your mouse to enter formulas for the following calculated fields $($resize the columns as needed$)$:

a$.$ Between the Dept ID and Physician ID fields, insert the Department field, and then create an exact match lookup with the VLOOKUP function using the value of the Dept ID field in the Appointments table to retrieve the department name from column $2$ of the Dept$\_$Lookup table. $($Hint: Remember with an exact match lookup to set the range$\_$lookup argument to FALSE.$)$

b$.$ Between the Physician ID and Patient ID fields, insert the Physician field, and then create an exact match lookup with the VLOOKUP function using the Physician ID field to retrieve the physician name from column $2$ of the Physician$\_$Lookup table.

c$.$ Between the Patient ID and Patient Check In fields, insert the Patient field, and then create an exact match lookup using the Patient ID field to retrieve the patient name from column $2$ of the Patient$\_$Lookup table.

Insert the Patient Wait field in column K to calculate whether a patient had to wait past the scheduled appointment time. Use your mouse to enter the IF function to test whether the value of the Exam Start field $($cell $15)$ is greater than the Appt Time Field $($cell A$5)$; if it is$,$ return the value $1$ ; otherwise, return the value $0.$ Format the calculated values with the General cell format.