Excel task only experts urgent , have 2 hrs to complete this

CMR 282

Chapter 2 – Module 5

Visual Benchmark 1 - Use Lookup, Statistical, and Math Functions in a Billing Summary

1. Open BillHrs0ct4to8.xlsx.

2. Save the workbook with the name 2-BillableHrsOct4to8.

3. Review the worksheet shown in Figure 2.1. Use the following information to create the required formulas. Create range names to use in all the formulas so readers can easily interpret the formula:

a. In column F, create a formula to look up the attorney's hourly rate from the table at the bottom right of the worksheet. The formula should return results for exact matches only.

b. In column G, calculate the legal fees billed by multiplying the billable hours times the hourly rate.

c. In the range J6:J9, calculate the total legal fees billed by attorney (use SUMIF)

d. In the range J13:J16, calculate the average hours billed by attorney (use AVERAGEIF).

4. Save and then close 2-BillableHrsOct4to8.

Visual Benchmark 2 – Use lookup and logical functions to calculate cardiology costs

1. Open WPMCCardioCosts.xlsx.

2. Save the workbook with the name 2-WPMCCardioCosts.

3. Range names for this worksheet have already been created. Spend a few moments reviewing the range names and the cells each name references to become familiar with the worksheet.

4. Review the worksheet shown in Figure 2.2 and revise it to match the one shown by creating formulas using the following information:

a. In column G, create a formula to look up the surgery fee in the table at the bottom of the worksheet. The formula should return results for exact matches only.

b. In column H, create a conditional formula that inserts the cost of the aortic or mitral valve if the cardiac surgery required a replacement valve; otherwise, place a 0 in the cell. Hint: The surgery codes for surgeries that include replacement valves are ART and MRT (for aortic and mitral, respectively). Do not add a named range to represent the data in the Surgery Code column as Excel will use the entire range when calculating the result instead of an individual cell.

c. In column I, calculate the postoperative hospital cost by multiplying the number of days the patient was in hospital by the postoperative cost per day.

d. In column J, calculate the total cost as the sum of the surgery fee, valve cost, and postoperative hospital cost.

e. Calculate the total cost for each column in row 22.

5. Format the numbers as shown in Figure 2.2.

6. Save, print, and then close 2-WPMCCardioCosts.

Performance Assessment 1 – Conditional Format and Filter a Help Desk Worksheet

1. Open RSRHelpDesk.xlsx.

2. Save the workbook with the name U1-RSRHelpDesk.

3. Use the icon set from the Quick Analysis button to apply conditional formatting to the values in the Priority column.

4. Create a custom format for the values in the Time Spent column. The format should display a leading zero (that is, a 0 before the decimal point for a value less than 1), two digits after the decimal point, and the text hrs after each entry separated by one space from the number (for example, 2.20 hrs, 0.25 hrs, etc.).

5. Create two conditional formatting rules for the values in the Time Spent column as follows:

a. For all the entries in which the time spent is less than one hour, apply bold formatting and the Olive Green, Accent 3, Lighter 80% fill color (seventh column, second row).

b. For all the entries in which the time spent is more than two hours, apply the Yellow fill color (fourth column, last row).

6. Filter the worksheet by the Yellow fill color applied in the Time Spent column.

7. Clear the filter and filter arrow(s)

8. Save and then close U1-RSRHelpDesk.