Home>Homework Answsers>Computer Science Homework Help Np_Ex19_6A   Np_Ex19_6A_Firstlastname_1    New Perspectives Excel 2019

Home>Homework Answsers>Computer Science homework help NP_EX19_6a   NP_EX19_6a_FirstLastName_1    New Perspectives Excel 2019 | Module 6: SAM      Project 1a                     Oval Lake Hospital MANAGING YOUR DATA WITH DATA TOOLS    GETTING STARTED · Open the file NP_EX19_6a_FirstLastName_1
xlsx, available for download from the SAM website
· Save the file as NP_EX19_6a_FirstLastName_2
xlsx by changing the1 to a2
o If you do not see the
xlsx file extension in the Save As dialog box, do not type it
The program will add the file extension for you automatically
· With the file NP_EX19_6a_FirstLastName_2
xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet
o If cell B6 does not display your name, delete the file and download a new copy from the SAM website
PROJECT STEPS 1
Donnie Cruz is a finance assistant for Oval Lake Hospital in south Florida
His current project is to format departmental data for the years 2021-2023 to make it easier to analyze
Switch to the Cancer Center worksheet
Unfreeze the top row of the worksheet
2
Sort the data in the CancerCenter table first in ascending order by the Admission field and then in ascending order by the Service field
3
Insert a Total Row in the CancerCenter table, and then use the Total row to calculate the total of the values in the 2021, 2022, and 2023 fields
  4
The administrator overseeing the cancer center is considering mandating that all inpatient service names be different from outpatient service names, and wants to see which duplicate names are currently in use
Apply a conditional formatting rule to the range C4:C11 that uses Light Red Fill with Dark Red Text for duplicate values
5
Switch to the Cardiac Care worksheet
Freeze the first column of the worksheet
6
Format the range A3:F10 as an Excel table with headers using the Gold, Table Style Medium 5 table style
Enter CardiacCare as the name of the table
7
Donnie notices that the CardiacCare table is missing a record
Add the record shown in Table 1 to the end of the CardiacCare table
Table 1: Data for the Range A11:F11     A B C D E F   11 Cardiac Care Inpatient Other 2855 3540 3752 8
The Maternity department has more admission criteria than other major divisions of the hospital
Donnie wants to summarize Maternity admissions data using subtotals to show the relationships between admission type and number of patients
Switch to the Maternity worksheet and complete the following steps: a
Sort the table by the Admission field in ascending order
b
Convert the table to a normal range
c
Insert subtotals into the range A3:F13, with the subtotals appearing at each change in the Admission column value
The subtotals should use the SUM function and include subtotals for the 2021, 2022, and 2023 fields
9
Switch to the Psychiatric worksheet and remove the duplicate record based on the values in the Admission and Service columns
10
The data bars in the last three columns make some of the numbers hard to read
Change the Data Bars conditional formatting for the range D4:F12 to use the Solid Fill Green Data Bar format
11
Donnie wants to calculate total admissions for the hospital for each year
Switch to the All Departments worksheet, then create the following formulas: a
In cell D40 use the SUM function with a structured reference to total the values in the 2021 column of the AllDepartments table
  b
In cell E40 use the SUM function with a structured reference to total the values in the 2022 column of the AllDepartments table
  c
In cell F40 use the SUM function with a structured reference to total the values in the 2023 column of the AllDepartments table
  12
Donnie wants to prepare the AllDepartments table for 2024 data when it becomes available
Add a table column to the end of the table
Your workbook should look like the Final Figures on the following pages
Save your changes, close the workbook, and then exit Excel
Follow the directions on the SAM website to submit your completed project
 Instructions_NP_EX19_6a
docxNP_EX19_6a_Smith_1
xlsxNP_EX19_6a_JumahSaleh_Report_1
xlsx