Hw-999 excel chapter 08 electricity lesson plans for 5th grade


Henrique Silva works in the Sales department of Spring Software, a software development company headquartered in Cambridge, Massachusetts. Henrique has created a worksheet to capture customer sales data for the Nashua office for the past three years. He has asked you to enhance the workbook using conditional formatting and advanced formulas to better analyze the customer data, highlight trends, and identify top customers.

• With the file NP_Excel2013_T8_P1a_FirstLastName_2.xlsxstill open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

2. Edit the conditional formatting rule applied to the range G4:G32 so that the highlighted cells are formatted with the font color is White, Background 1 and the fill color Blue, Accent 5, Lighter 40% (9th column, 4th row in the Theme color palette.)

3. In cell D4, enter a formula that calculates customer tenure, in years, using absolute and structured references. The formula should calculate tenure based on subtracting the value in the First Order column from the current year value in cell B1. If necessary, copy the formula you created in step D4 to the range D5:D32. (Tip: Remember to use an absolute reference to the current year value in cell B1.)

8. In cell Q4, nest the VLOOKUP function in an IFERROR function. The cell should display the error message Invalid Customer ID instead of the error value, if the VLOOKUP function finds an error. Confirm the error message appears and then update the Customer ID value in cell Q3 to 1019.

9. In cell Q6, enter a formula using the VLOOKUP function to lookup the Customer ID value shown in cell Q3in the CustomerNashua table (located in the range A3:L32). The VLOOKUP function should then retrieve the Discount value from the CustomerNashua table (the 11th column in the table) for this record. The VLOOKUP function should find an exact match to the value in Q3.

10. In cell Q7, enter a formula using the HLOOKUP function to determine the free services customer incentive offered to Spring Sales bigger clients.The HLOOKUP function should look up the value in cell Q5in the range N10:R11 (which has the defined name CustomerIncentives). The HLOOKUP function should then retrieve the value in the 2nd row of the CustomerIncentiveslookup table. Since these free services are offered to customers that meet or exceed the yearly sales levels listed in the CustomerIncentives table, the HLOOKUP function should find an approximate match to the value in Q5.

11. Go to Customer Analysis worksheet. In cell C4, enter a formula that uses the COUNT function and structured references that counts the total number of customers in the CustomersNashuatable on the CustomerList worksheet (Tip: The COUNT function only counts rows that contain a value, you cannot use the Customer column as an argument in the function. Use the Customer ID column instead.)

15. In cell D6, enter a formula to calculate the percentage of High Priority CustomersSales out of All Customers2016 sales. (Tip: The calculation should divide the 2016 High Priority Customer sales by the total 2016 sales for all customers.) Format the cell using the Percentage number format with no decimal places.