NP_EX365_2021_CT6c_FelippeFigueiredo_Report_2

Felippe Figueiredo GE ver. 17.2.0-rc0000 1. 2/2 Unfreeze a row. 2. 2/2 Sort a table based on multiple ranges. 3. 2/2 Insert a Total row in a table. Summarize table data using a Total row. 4. 2/2 Create a Find Duplicates conditional formatting rule. Format a conditional formatting rule. 5. 2/2 Freeze a column. 6. 2/2 Format a range as a table. Apply a table style. Set the name of a table. 7. 3/3 Add a record to a table. 8. 3/3 Sort a table. Convert a table to a range. Insert a subtotal row in a range. Set options for subtotaling data. 9. 3/3 Delete a record from a table. 10. 0/3 Format a conditional formatting rule. 11. 3/3 Create a formula using a function. Create a formula using a function. Create a formula using a function. 12. 3/3 Add a column to a table. SUBMISSION #2 | SCORE IS: 27 OUT OF 30 Donnie Cruz is a finance assistant for Oval Lake Hospital in south Florida. His current project is to format departmental data for the years 2024-2026 to make it easier to analyze. Switch to the Cancer Center worksheet. Unfreeze the top row of the worksheet. Sort the table data first in ascending order by the Admission column and then in ascending order by the Service column. Insert a total row in the table, and then calculate the totals for the 2024, 2025, and 2026 columns. 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 service names that uses light red fill with dark red text for duplicate values. Don't include the total row since there's no value there. Switch to the Cardiac Care worksheet. Freeze the first column of the worksheet. The data on this worksheet is not formatted like the other worksheets. Format the headings and the data as an Excel table with headers using the same style as the table on the Cancer Center worksheet. Enter CardiacCare as the name of the table. Donnie notices that the CardiacCare table is missing a record. Add the record shown in Table 1 to the bottom of the CardiacCare table. The Maternity department has more admission criteria than the 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 at each change in the Admission column value. The subtotals should display total values and include subtotals for the 2024, 2025, and 2026 columns. Switch to the Psychiatric worksheet and remove the duplicate record based on the values in the Admission and Service columns. The data bars in the last three columns make some of the numbers hard to read. Replace the existing conditional formatting rule using solid green data bars. In the Psychiatric worksheet, the conditional formatting rule in the range D4:F12 should apply Solid Fill Green Data Bar fill color formatting. Donnie wants to calculate total admissions for the hospital for each year. Switch to the All Departments worksheet, and then create the following formulas: a. Below the table, in the row labeled "Total Admissions", use a function and a structured reference to total the values in the 2024 column of the AllDepartments table. b. Use the same function to calculate the 2025 and 2026 totals in the same row. Donnie wants to prepare the AllDepartments table for 2027 data when it becomes available. Add a table column to the end of the table. Be sure that the column shows the appropriate column header. New Perspectives Excel 365/2021 | Module 6: SAM Critical Thinking Project 1c

Author: Note: New Pe

M

Oval Lake Hospital Patients Admitted 2024-2026 Department Admission Service 2024 2025 2026 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

Column5 2026 18,632 4,121 12,189 2,247 5,786 488 48 3,752

Oval Lake Hospital Patients Admitted 2024-2026 Department Admission Service 2024 2025 2026 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

Oval Lake Hospital Patients Admitted 2024-2026 Department Admission Service 2024 2025 2026 2027 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