write two analysis for two different excel works

CMR 282
Chapter 1 – Module 3

Assessment 1

1. Open RSRServRpt.xlsx.
2. Save the workbook with the name 1-RSRServRpt
3. Apply the following formatting changes to the Sep worksheet:
a. Format the values in the range C6:C23 as fractions using the As quarters (2/4) option in the Type list box.
b. Format the rate codes in the range D6:D23 with the 3 Traffic Lights(Rimmed) icon set (second column, first row of the Shapes section.)
c. Use the New Rules option to format the parts values in the range F6:F22 with the light red fill color (sixth column, second row) for those cells with values that are equal to 0.
d. Format the total invoice values in the range G6:G22 using the red data bars. (Use the Red Data Bar option in the Gradient Fill section at the drop down gallery.)
4. Save and then close 1-RSRServRpt.
Assessment 2

1. Open 1-RSRServRpt.
2. Save the workbook with the name 1-RSRServRpt-2.
3. Create and apply the following custom number formats to the Sep worksheet:
a. Create a custom number format that displayshrsone space after each value in the range C6:C23. Hint: After selecting Custom in the Category list box, click after the existing format codes in the Type text box and then add the required entry (Do not delete what is already in the Type text box.)
b. Create a custom number format that displaysRSR-in front of each work order number in the range B6:B22.
4. Save and then close 1-RSRServRpt-2.

Assessment 3

1. Open 1-RSRServRpt-2.
2. Save the workbook with the name 1-RSRServRpt-3.
3. Make the following changes to the Sep worksheet:
a. Select the range A5:G22 and turn on the Filter feature.
b. Using the filter arrow at the top of the Hours Billed column, display those invoices for which the hours billed are between 1.75 and 3.75. Hint: When typing in the values, type 1.75 and 3.75. Do not select 1 3/4 hrs and 3 3/4 hrs from the drop-down lists in the Custom AutoFilter dialog box.
4. Make the following changes to the Oct worksheet:
a. Select the range A5:G22 and then turn on the Filter feature.
b. Filter the Parts column by color to show only those invoices for which no parts were billed.
5. Make the following changes to the Nov worksheet:
a. Clear the filter from the Date column.
b. Filter the worksheet by the icon associated with rate code 3.
6. Make the following changes to the Dec worksheet:
a. Remove the filter arrows from the worksheet.
b. Make any cell within the range A5:G22 active.
c. Open the Sort dialog box.
d. Define three sort levels as follows:
Sort by Sort on Order
Rate code cell icon Red traffic light (on top)
Rate code cell icon Yellow traffic light (on top)
Rate code cell icon Green traffic light (on top)

7. Save and then close 1-RSRServRpt-3.
 
Assignment 4
1. Open VRPay-Oct30.xlsx.
2. Save the workbook with the name 1-VRPay-Oct30
3. Create and apply two conditional formatting rules for the values in the Pay Rate column as follows:
a. Apply a light blue fill color (fifth column, third row) to values between11.50and 13.00.
b. Apply a light green fill color (seventh column, third row) to values greater than 13.00.
4. Create a conditional formatting rule for the Gross Pay column that will format the values in the dark red font color (first option in the Standard Colors section) for those employees that have worked overtime hours. Hint: When entering the formula use relative referencing (K6:K23) in the logical test.
5. Use the Quick Analysis button to find the top 10 percent among values in the Gross Pay column.
6. Edit the formatting rule for Cell Value< 11.5 in the Pay Rate column by applying the light purple fill color (eighth column, third row). 7. Use the Quick Analysis button to delete the formatting rule for Overtime Hours column. 8. Save and then close 1-VRPay-Oct30 Visual Benchmark 1. Open Billings0ct4to8.xlsx. 2. Save the workbook with the name 1-Billings0ct4to8 3. Format the worksheet to match the one shown in Figure WB-1.1 using the following information: • The data in the Billing Code column has been custom formatted to add the text Amicus#- in the standard blue font color in front of the code number. • Icon sets have been used in the Attorney Code column and the same icon set should be applied in the Attorney Code Table section of the worksheet. • The data bars added to the values in the Legal Fees column have been formatted with the Turquoise, Accent 3 gradient fill (seventh column, first row in the Theme Colors section). Hint: Select More Rules in the Data Bars side menu. • Values below 1500.00 in the Total Due column have been conditionally formatted in bold dark red text and the worksheet has been sorted by the font color used for the conditional format. 4. Save and then close 1-Billings0ct4to8