Homework answers / question archive / In Practice Excel 365: Application Capstone Project 3 These instructions are compatible with both Microsoft Windows and Mac operating systems

In Practice Excel 365: Application Capstone Project 3 These instructions are compatible with both Microsoft Windows and Mac operating systems

Unrecognized

Share With

In Practice Excel 365: Application Capstone Project 3 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. [Student Learning Outcomes: 5.1, 5.4, 5.6, 6.2, 6.3, 6.4, 6.7, 7.1, 7.2, 7.3, 7.4, 7.5, 7.6] Files Needed: CourtyardMedical_Project3-Excel-ACP-3.xlsx and CMPLogo.png Completed Project File Names: [your name]-CourtyardMedical_Project3-Excel-ACP-3.xlsx Skills Covered in this Project Use the COUNTIF function. Build SUMIFS formulas. Build borders and add fill to format a worksheet. Build a date calculation. Create an IF formula. Nest XMATCH and INDEX functions. Build an array formula. Group and format worksheets. Copy a worksheet and rename a tab. Create a static data consolidation. Insert a picture in a worksheet. 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. Open the CourtyardMedical_Project3-Excel-ACP-3 start file. If the workbook opens in Protected View, click the Enable Editing button so you can modify it. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor. Build a COUNTIF formula. Select cell J4 on the Billing sheet and start a COUNTIF function [Statistical category]. 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. Create a SUMIFS formula. Select cell J5 and start a SUMIFS function [Math & Trig category] with cells $E$4:$E$33 as the Sum_range argument. Select the department name column for Criteria_range1. Remember that argument ranges must have the same dimension. For Criteria1, type bar* for the Bariatric department. If you prefer to type the formula, you must include quotation marks around the criteria. Select the billing date column for Criteria_range2. For Criteria2, type <=1/1/23 to select dates in 2022. If you type the formula, include quotation marks and the ending parenthesis. Copy and edit a SUMIFS formula. Copy the formula in cell J5 to cell J6. Edit the Criteria1 argument in cell J6 to select the Cardiology department. Edit the Criteria_range2 argument to refer to the values in the Date Paid column. Edit the Criteria2 argument to find dates after 2022. Format cells J5:J6 as Currency with zero decimal places. Apply formats to cells in the Billing sheet. Merge & Center cells I3:J3. Set the font size for the merged cell to 16 pt. Select cells A3:G33 and apply a single solid Outline border. Place a solid vertical border between columns and a dashed style horizontal border between rows. Place a solid single bottom border for cells A3:G3. Determine and build an IF formula using dates. Select cell G4 and build a formula to subtract the invoice date from the date paid. The first result is 95 days. Copy the formula to cells G5:G7 and note the issue when there is no paid date. Undo the copy task. Delete the formula in cell G4 and start an IF formula [Logical category]. For the Logical_test argument, build the subtraction formula (from step 7.a) 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. 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. For the Value_if_false argument, type a space. This displays as an empty cell. When the result is a negative number, the cell displays as blank. Copy the formula to cells G5:G33 and preserve the borders. Nest XMATCH and INDEX to display the department name for a physician. The INDEX formula looks through cells A3:G33 to find the name that matches data in cell I11 and displays the corresponding department name. Select cell I13, start an INDEX formula [Lookup & Reference category], and choose the first argument list. Select cells A3:G33 for the Array argument to identify the entire range of data. For the Row_num box, nest an XMATCH function to lookup cell I11 in the D3:D33 array. Nest a second XMATCH function for the Column_num argument to lookup cell I12 in the A3:G3 array (Figure 1). Nested MATCH and INDEX functions FIGURE 1 NESTED MATCH AND INDEX FUNCTIONS Select cell I11 and type Esom. Nest UNIQUE and SORT to display a list of physician names. Select cell L4 and start a SORT function. Nest a UNIQUE function for the Array argument and select the physician names without the header. The results spill to display the list. Select the physician names in column D and replace pelz with Adams. Select cell A2 (Figure 2). Completed Billing worksheet FIGURE 2 COMPLETED BILLING WORKSHEET Group and format worksheets. Browse the data on the Calls1 and Calls2 worksheets. These sheets track data for two weeks. Group the Calls1 and Calls2 sheets. Apply the Center Across Selection command to cells A2:H3. Fill the day names to reach Sun. From the Page Setup dialog box, change the page orientation to Landscape. From the Page Setup dialog box, add a footer that displays the sheet name in the center section. Ungroup the sheets. Copy and rename a worksheet. Copy the Calls1 sheet to the end. Name the copied sheet as Consolidated. Format the tab color for the Consolidated sheet as Red, Accent 5, Darker 50%. Create a static data consolidation. Delete the contents of cells B6:H13 on the Consolidated sheet. Consolidate the data in cells B6:H13 using the Sum function. Insert a picture in a worksheet. Select cell C15 in the Consolidated sheet and insert the CMPLogo downloaded from the Resources link. Change the image Color to Washout in the Recolor group. Position the top left corner of the image in cell A1 and set the shape height to 1”. Position the image so that its top and bottom borders are visible. Select cell A15. Save the workbook (Figure 3). completed worksheets FIGURE 3 COMPLETED CALLS AND CONSOLIDATED WORKSHEETS Build a custom template workbook. Copy the Billing sheet to the end of the workbook and rename the copied sheet Billing Template. Hide the original Billing sheet and the Calls1, Calls2, and Consolidated sheets. Delete columns I:J. The names array moves to column J. Delete the contents of cells A4:B33 and cells E4:F33. Cell formats and the formula remain. Cut cells D3:D33, right-click cell C3, and insert the cut cells to rearrange the columns. The physician’s name now precedes the department name. Convert a dynamic array to a range and prepare lookup data. Select and copy cells J4:J17 to the Clipboard. Select cell J4 and paste Values to convert the array to labels. Remove the moving border and select cell J4. Individual labels replace the array (look in Formula bar). Sort cells C4:D33 by physician name in ascending order. Type the department name for each physician in column K by referring to columns C:D (Figure 4). completed worksheets FIGURE 4 PHYSICIAN AND DEPARTMENT NAMES Create a data validation list. Delete the contents of cells C4:D33. Select cells C4:C33 and create a data validation list using the physician names in column J. Test your data validation list and choose any name for cell C4. Build an XLOOKUP formula to display the department name. Select cell D4 and start an XLOOKUP formula. Lookup the label in C4 and use absolute references to the lookup and return arrays. Note that both arrays have the same dimension. Copy the formula to reach cell D33 and preserve borders. When there is no entry in column C, the standard #N/A error message displays. Nest an IFERROR formula for the XLOOKUP function. Select cell D4 and click after the = sign in the Formula bar. Type ife and insert IFERROR. Click after the closing parenthesis and type a comma ( , ) to move to the value_if_error argument for IFERROR. The value argument is the XLOOKUP function. Type " and press Spacebar, type " for the closing quotation mark, and type ) for the closing parenthesis (Figure 5). This argument will display a blank cell instead of the standard error message. Nested IFERROR function FIGURE 5 NESTED IFERROR FUNCTION Copy the edited formula in cell D4 to row 33 and preserve borders. Insert check box form controls. Insert a row at row 2 and set its height at 48. Type Administered By? in cell B2 including the question mark and set the font size to 16. Insert a check box form control in cell D2. Edit the label to display CMP, include 3-D shading for the control, and turn off its Locked property. Copy the control, paste the copy in cell F2, and edit the label to Carrier. Move the copied control to cell E2. Select both controls and align them. Fine-tune the position of the controls as needed. Uncheck a control that has been accidentally checked. Check accessibility and add alt text. Check accessibility for the worksheet. Add alt text for the logo image that says This logo includes addresses, phone, and web site information. including the period. Hide columns J:K. Delete names in column C, if any, that you entered to complete the sheet. Unlock cells and protect the sheet. Unlock cells A5:C34 and cells E5:F34. Cells with formulas remain locked. Select cell A5. Protect the worksheet without a password. Allow all cells to be selected and allow columns and rows to be formatted. Save the template worksheet (Figure 6). Nested IFERROR function FIGURE 6 COMPLETED TEMPLATE WORKSHEET Complete the billing template by completing the data shown in Figure 7. Select the Carrier checkbox form control to select it. Enter the following data starting in row 5. You need not type currency symbols because the column is formatted. Invoice ID Date Physician Department Amount Date Paid 5 CMP0035 4/1/2024 Greenfield Emergency $325.00 5/8/2024 6 CMP0036 5/3/2024 Ruhl Maternity $275.00 6/7/2024 Save the workbook, and close it. Completed template worksheet with data entered FIGURE 7 COMPLETED TEMPLATE WORKSHEET WITH DATA ENTERED Upload and save your project file. Submit project for grading.