In Practice Excel 365: Application Capstone Project 3: Courtyard Medical (Full answer 2025) скачать в хорошем качестве

In Practice Excel 365: Application Capstone Project 3: Courtyard Medical (Full answer 2025)

Join this channel to get access to perks:    / @calculusphysicschemaccountingt   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 [Statistical category]. 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 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. 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. c. Select the physician names in column D and replace pelz with Adams. d. Select cell A2 (Figure 2). e. f. FIGURE 2 COMPLETED BILLING WORKSHEET 10. Group and format worksheets. a. Browse the data on the Calls1 and Calls2 worksheets. These sheets track data for two weeks. b. Group the Calls1 and Calls2 sheets. c. Apply the Center Across Selection command to cells A2:H3. d. Fill the day names to reach Sun. e. From the Page Setup dialog box, change the page orientation to Landscape. f. #MicrosoftExcel #ApplicationCapstone #PracticeExcel365 #Capstone