NP_EX19_6a_FirstLastName_1

profileexpmen
NP_EX19_6a_JumahSaleh_Report_1.xlsx
Home>Computer Science homework help>NP_EX19_6a_FirstLastName_1

Graded Summary Report

New Perspectives Excel 2019 | Module 6: SAM Project 1a
Jumah Saleh
SUBMISSION #1 | SCORE IS: 100 OUT OF 100 GE ver. 15.0.0-rc0000
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. 8/8
Unfreeze a row.
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. 8/8
Sort a table based on multiple ranges.
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. 8/8
Insert a Total row in a table.
Summarize table data using a Total row.
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. 8/8
Create a Find Duplicates conditional formatting rule.
Format a conditional formatting rule.
5. Switch to the Cardiac Care worksheet. Freeze the first column of the worksheet. 8/8
Freeze a column.
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. 8/8
Format a range as a table.
Apply a table style.
Set the name of a 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. 8/8
Add a record to a table.
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. 8/8
Sort a table based on multiple ranges.
Convert a table to a range.
Insert a subtotal row in a range.
Set options for subtotaling data.
9. Switch to the Psychiatric worksheet and remove the duplicate record based on the values in the Admission and Service columns. 9/9
Delete a record from a table.
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. 9/9
Format a conditional formatting rule.
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.  9/9
Create a formula using a function.
Create a formula using a function.
Create a formula using a function.
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. 9/9
Add a column to a table.

Documentation

New Perspectives Excel 2019 | Module 6: SAM Project 1a
Oval Lake Hospital
MANAGING YOUR DATA WITH DATA TOOLS
Author: Jumah Saleh
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website.

Cancer Center

Oval Lake Hospital
Patients Admitted 2021-2023
Department Admission Service 2021 2022 2023
Cancer Center Inpatient Diagnosis 2,252 1,914 1,550
Cancer Center Inpatient Hospice 81 92 107
Cancer Center Inpatient Other 592 704 866
Cancer Center Inpatient Surgery 7,894 6,631 6,565
Cancer Center Outpatient Consultation 1,288 1,649 1,814
Cancer Center Outpatient Imaging 3,923 3,335 2,935
Cancer Center Outpatient Other 841 891 1,149
Cancer Center Outpatient Surgery 653 627 558
Total 17,524 15,843 15,544

Cardiac Care

Oval Lake Hospital
Patients Admitted 2021-2023
Department Admission Service 2021 2022 2023
Cardiac Care Inpatient Surgery 18,904 16,636 18,632
Cardiac Care Outpatient Consultation 3,190 3,350 4,121
Cardiac Care Inpatient Diagnosis 7,803 9,910 12,189
Cardiac Care Outpatient Other 1,788 1,842 2,247
Cardiac Care Outpatient Imaging 4,502 5,673 5,786
Cardiac Care Outpatient Surgery 525 525 488
Cardiac Care Inpatient Hospice 44 51 48
Cardiac Care Inpatient Other 2,855 3,540 3,752

Maternity

Oval Lake Hospital
Patients Admitted 2021-2023
Department Admission Service 2021 2022 2023
Maternity Delivery Natural 3,396 4,279 4,921
Maternity Delivery Surgical 5,084 5,033 5,234
Delivery Total 8,480 9,312 10,155
Maternity Inpatient Non-maternity 1,322 1,408 1,296
Maternity Inpatient Other 226 287 339
Maternity Inpatient Procedure 1,252 1,189 1,213
Maternity Inpatient Pre-labor 556 428 496
Inpatient Total 3,356 3,312 3,344
Maternity Outpatient Imaging 7,795 8,964 7,440
Maternity Outpatient Consultation 5,208 5,781 6,128
Maternity Outpatient Other 2,253 2,298 2,873
Maternity Outpatient Surgery 285 259 298
Outpatient Total 15,541 17,302 16,739
Grand Total 27,377 29,926 30,238

Psychiatric

Oval Lake Hospital
Patients Admitted 2021-2023
Department Admission Service 2021 2022 2023
Psychiatric Outpatient Other 6,256 5,881 6,587
Psychiatric Inpatient Diagnosis 1,803 1,857 1,523
Psychiatric Inpatient Other 628 754 618
Psychiatric Inpatient Substance 884 972 902
Psychiatric Outpatient Labs 3,893 3,620 4,018
Psychiatric Outpatient Therapy 10,051 9,548 10,789
Psychiatric Inpatient Surgery 51 44 44
Psychiatric Outpatient Imaging 523 450 518
Psychiatric Inpatient 3-day hold 796 812 918

All Departments

Oval Lake Hospital
Patients Admitted 2021-2023
Department Admission Service 2021 2022 2023 2024
Cancer Center Inpatient Diagnosis 2,252 1,914 1,550
Cancer Center Inpatient Hospice 81 92 107
Cancer Center Inpatient Other 592 704 866
Cancer Center Inpatient Surgery 7,894 6,631 6,565
Cancer Center Outpatient Consultation 1,288 1,649 1,814
Cancer Center Outpatient Imaging 3,923 3,335 2,935
Cancer Center Outpatient Other 841 891 1,149
Cancer Center Outpatient Surgery 653 627 558
Cardiac Care Inpatient Diagnosis 7,803 9,910 12,189
Cardiac Care Inpatient Hospice 44 51 48
Cardiac Care Inpatient Other 2,855 3,540 3,752
Cardiac Care Inpatient Surgery 18,904 16,636 18,632
Cardiac Care Outpatient Consultation 3,190 3,350 4,121
Cardiac Care Outpatient Imaging 4,502 5,673 5,786
Cardiac Care Outpatient Other 1,788 1,842 2,247
Cardiac Care Outpatient Surgery 525 525 488
Maternity Delivery Natural 3,396 4,279 4,921
Maternity Delivery Surgical 5,084 5,033 5,234
Maternity Inpatient Non-maternity 1,322 1,408 1,296
Maternity Inpatient Other 226 287 339
Maternity Inpatient Pre-labor 556 428 496
Maternity Inpatient Procedure 1,252 1,189 1,213
Maternity Outpatient Consultation 5,208 5,781 6,128
Maternity Outpatient Imaging 7,795 8,964 7,440
Maternity Outpatient Other 2,253 2,298 2,873
Maternity Outpatient Surgery 285 259 298
Psychiatric Inpatient 3-day hold 796 812 918
Psychiatric Inpatient Diagnosis 1,803 1,857 1,523
Psychiatric Inpatient Other 628 754 618
Psychiatric Inpatient Substance 884 972 902
Psychiatric Inpatient Surgery 51 44 44
Psychiatric Outpatient Imaging 523 450 518
Psychiatric Outpatient Labs 3,893 3,620 4,018
Psychiatric Outpatient Other 6,256 5,881 6,587
Psychiatric Outpatient Therapy 10,051 9,548 10,789
Total Admissions 109,397 111,234 118,962

image2.png

image3.png

image4.png

image1.png