CourtyardMedical_Project3-Excel-ACP-3 | Courtyard Medical Plaza (CMP) скачать в хорошем качестве

CourtyardMedical_Project3-Excel-ACP-3 | Courtyard Medical Plaza (CMP)

For this project, your tasks include billing statistics and survey results for Courtyard Medical Plaza (CMP). Files Needed: CourtyardMedical_Project3-Excel-ACP-3.xlsx and CMPLogo.png Completed Project File Names: -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. 1. 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. 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 a SUMIFS formula. a. Select cell J5 and start a SUMIFS function [Math & Trig category] 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. e. For Criteria2, type 1/1/23 to select dates in 2022. If you type the formula, include quotation marks and the ending parenthesis. 5. Copy and edit a SUMIFS formula. a. Copy the formula in cell J5 to cell J6. b. Edit the Criteria1 argument in cell J6 to select the Cardiology department. c. Edit the Criteria_range2 argument to refer to the values in the Date Paid column. d. Edit the Criteria2 argument to find dates after 2022. e. Format cells J5:J6 as Currency with zero decimal places. 6. 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. d. Place a solid vertical border between columns and a dashed style horizontal border between rows. e. Place a solid single bottom border for cells A3:G3. 7. 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. c. Undo the copy task. d. Delete the formula in cell G4 and start an IF formula [Logical category]. e. 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. f. 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. g. 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. h. Copy the formula to cells G5:G33 and preserve the borders. 8. 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. 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). e. f. Figure 1 g. Nested MATCH and INDEX functions h. Select cell I11 and type Esom. 9. Nest UNIQUE and SORT to display a list of physician names. a. Select cell L4 and start a SORT function. b. 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. d. Select cell A2 (Figure 2).