In Practice Excel 365: Application Capstone Project 2 (Chs. 5-7) CourtyardMedical Excel ACP 2 xlsx
In Practice Excel 365: Application Capstone Project 2 (Chs. 5-7)
These instructions are compatible with both Microsoft Windows and Mac operating systems.
For this project, your tasks include billing statistics and survey results for Courtyard Medical Plaza (CMP). You group, format, and consolidate worksheets, and build an array formula. You also create a template with data validation lists and form controls.
Files Needed: CourtyardMedical_Excel-ACP-2.xlsx and CMPLogo.png
CourtyardMedical_Excel-ACP-2.xlsx
Skills Covered in this Project
• Use the COUNTIF function.
• Build SUMIFS formulas.
• Create an IF formula.
• Nest XMATCH and INDEX functions.
• Build a date calculation.
• Build an array formula.
• Design borders and add fill to format a worksheet.
• Group and format worksheets.
• Copy a worksheet and rename a tab.
• Create a static data consolidation by category.
• Insert a special symbol.
• Build and use a LAMBDA function.
• Create a custom template.
• Convert an array to a range.
• Set data validation with a list.
• Nest an IFERROR formula.
• Insert a check box form control.
• Check accessibility.
• Add worksheet protection.
• Create a workbook from a template.
Steps to complete this project:
Mark the steps as checked when you complete them.
1. Open the CourtyardMedical_Excel-ACP-2.xlsx start file. If the workbook opens in Protected View, click the Enable Editing button so you can modify it.
2. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.
3. Build a COUNTIF formula.
a. Select cell J4 on the Billing sheet and start a COUNTIF function
b. Select cells F4:F33 for the Range argument and type with no space between the symbols as the criteria. The criteria finds cells that are not empty. If you type the formula, enclose the symbols within quotation marks.
4. Create SUMIFS formulas.
a. Select cell J5 and start a SUMIFS function with cells $E$4:$E$33 as the Sum_range argument.
b. Select the department name column for Criteria_range1. Remember that argument ranges must have the same dimension.
c. For Criteria1, type: bar* for the Bariatric department. If you prefer to type the formula, you must include quotation marks around the criteria.
d. Select the billing date column for Criteria_range2 and use criteria to select dates in 2025.
e. Use SUMIFS in cell J6 to find the valued specified in cell I6. Recall the criteria use in step 3b to find nonblank cells.
f. Format cells J5:J6 as Currency with zero decimal places.
5. Apply formats to cells in the Billing sheet.
a. Merge & Center cells I3:J3.
b. Set the font size for the merged cell to 16 pt.
c. Select cells A3:G33 and apply a single solid Outline border, a solid vertical border between columns, and a dashed style horizontal border between rows.
d. Place a solid single bottom border for cells A3:G3.
6. Determine and build an IF formula using dates.
a. Select cell G4 and build a formula to subtract the invoice date from the date paid. The first result is 95 days.
b. Copy the formula to cells G5:G7 and note the issue when there is no paid date. Undo the copy task.
.c. Delete the formula in cell G4 and start an IF formula [Logical category].
d. For the Logical_test argument, build the subtraction formula (from step 6a) followed by:=0. The logical_test is that the result of the subtraction (the number of days) is greater than or equal to zero, that it is not a negative result.
e. For the Value_if_true argument, show the subtraction formula. When the result of the subtraction is greater than zero, the subtraction is carried out and the result displays.
f. For the Value_if_false argument, type a space to display an empty cell. When the result is a negative number, the cell displays as blank.
g. Copy the formula to cells G5:G33 and preserve the borders.
7. Nest XMATCH and INDEX to display the department name for a physician.
a. Select cell I13, start an INDEX formula [Lookup & Reference category], and choose the first argument list.
b. Select cells A3:G33 for the Array argument to identify the entire range of data.
c. For the Row_num box, nest an XMATCH function to lookup cell I11 in the D3:D33 array.
d. Nest a second XMATCH function for the Column_num argument to lookup cell I12 in the A3:G3 array (Figure 1). The INDEX formula looks through cells A3:G33 to find the name that matches data in cell I11 and displays the corresponding department name.
Figure 1 Nested MATCH and INDEX functions
e. Select cell I11 and type: Esom.
8. Nest UNIQUE and SORT to display a list of physician names.
#practiceExcel365, #microsoft #microsoftexcel #capstone #application #capstoneproject