Illustrated Excel 2019 | Module 11: End of Module Project 1

Nimbus Health Center

ANALYZE DATA AND CORRECT FORMULAS

GETTING STARTED

Open the file IL_EX19_EOM11-1_FirstLastName_1.xlsx, available for download from the SAM website.

Save the file as IL_EX19_EOM11-1_FirstLastName_2.xlsx by changing the “1” to a “2”.

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 IL_EX19_EOM11-1_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

Tanisha Carmichael works in the Human Resources Department of Nimbus Health Center in Columbus, Ohio. She is using an Excel workbook to analyze employment data for the health center's Information Technology (IT) services. She asks for your help in completing the analysis and correcting errors.

Go to the Department Heads worksheet, where Tanisha asks you to complete the table of data about the department heads in IT Services. She copied and pasted the department names in the range B14:B20, but needs to list them in the range B4:B10 using title case, where each word is capitalized except for certain small words. In cell B4, enter a formula using the PROPER function to insert the text in cell B14 using title case. Fill the range B5:B10 with the formula in cell B4.

Enter the first and last names of the department heads by typing Travis in cell C4 and typing Campion in cell E4. Use Flash Fill to complete the first names in column C, and then use Flash Fill to complete the last names in column E.

In cell D14, enter a formula using the CONCAT function to display the first name in cell C4 with the last name in cell E4, separated by a space. Fill the range D15:D20 with the formula in cell D14. Copy the range D14:D20 to the range D4:D10, pasting values only. Hide columns C & E, which repeats information already included in the table. Hide rows 14:20 to avoid displaying duplicate data.

Go to the Clinical Staff worksheet. Tanisha asks you to assign names to ranges so that you can use the names in formulas. Assign the name Hours to the range C4:C12 to identify the Development Hours values. Assign the name Eval to the range D4:D12 to identify the Peer Eval values. Assign the name Salary to the range E4:E12 to identify the Salary & Bonus values.

In the range G3:H6, Tanisha wants to summarize statistics about the IT clinical staff with the highest peer evaluation ratings.
In cell H4, enter a formula using the AVERAGEIF function to average the salaries and bonuses (using the defined name Salary) of employees with a peer evaluation rating (using the defined name Eval) of 5. In cell H5, enter a formula using the SUMIF function to total the salaries and bonuses of employees with a peer evaluation rating of 5. In cell H6, enter a formula using the COUNTIF function to count the number of IT clinical staff with a peer evaluation rating of 5. Use defined names in all of the formulas.

In the range G8:H11, Tanisha wants to calculate statistics about staff using other criteria.
In cell H10, enter a formula using the COUNTIFS function to determine the number of IT clinical staff with a peer evaluation rating of 5 and professional development hours of 10 or more. In cell H11, enter a formula using the SUMIFS function to total the salaries and bonuses of IT clinical staff with a peer evaluation rating of 5 and professional development hours of 10 or more. Use defined names in all of the formulas.

Go to the Technical Staff worksheet. After entering the peer evaluation data in the range D4:D12, Tanisha applied data validation to the range to make sure she and others entered the correct ratings. She asks you to check for data-entry errors and then correct them. Circle invalid data in the worksheet. Change the circled value to 4.5 to use the correct evaluation rating.

Go to the Overview worksheet, where Tanisha wants to consolidate salary and bonus information about clinical and technical staff. In cell C4, enter a formula using the SUM function and 3-D references to add the salary and bonus totals (cell E13) on the Clinical Staff and Technical Staff worksheets. In cell C5, enter a formula without using a function that returns the value of cell H5 on the Clinical Staff worksheet. In cell C6, enter a formula without using a function that returns the value of cell H4 on the Technical Staff worksheet.

Go to the Income and Expenses worksheet. Tanisha asks you to correct the #DIV/0! error in the worksheet. In cell H6, trace the errors in the formula to identify the cause of the error message. Correct the formula so that it divides the income from services (cell G6) by the total income (cell G7).

Tanisha notes that if the total expenses are not calculated in cell G14, the formulas in the range H9:H13 would also display #DIV/0! errors. To provide direction in case of an error, add the IFERROR function to the formula in cell H9. If the result of dividing the value in cell G9 by the value in cell G14 produces an error, display Enter total expenses as an error message. Fill the range H10:H13 with the formula in cell H9.

Go to the Equipment Loan worksheet, which summarizes data about a loan for networking equipment. Tanisha asks you to calculate the monthly payment based on this data, which will update the total payments and total interest calculations. In cell C8, enter a formula using the PMT function to calculate the monthly payment on the equipment loan. Use relative references to cells C5, C6, and C4 as the arguments. Divide the interest rate in cell C5 by 12 to determine the monthly interest rate. Make the loan amount in the pv argument negative to display the monthly payment as a positive number.

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.

pur-new-sol

Related Questions