New Perspectives Excel 2013 Tutorial 8: SAM Project 1a 1

Subject:MS ExcelPrice:19.87 Bought3

Share With

New Perspectives Excel 2013 Tutorial 8: SAM Project 1a

1. 

Go to the CustomerList worksheet and complete the following actions:

a. Apply a conditional formatting rule to the range A4:A32 that formats

any duplicate values with Yellow Fill with Dark Yellow Text.

b. Update the Customer ID value for Firehouse Partners LLC to 1015 and

the Customer ID value of the Applewild Learning Center to 1023.

The conditional formatting rule should no longer highlight any values in

the range A4:A32.

2. 

Edit the conditional formatting rule applied to the range G4:G32 so that

the highlighted cells are formatted with the font color 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.)

4. 

In cell I4, enter a formula that uses the IF function and structured

references to calculate the 2-Year Growth Percentage.

a. The 2-Year Growth Percentage can be calculated if the value in 2015

Sales column is greater than 0.

b. To calculate the 2-Year Growth Percentage, divide the value in the 2-

Year Sales Growth column by the value in the 2016 Sales column.

c. If the value in the 2015 Sales column is not greater than 0, return a

value of N/A.

5. 

In cell J4, enter a formula that uses an IF function and structured

references to determine customer discount eligibility. (Tip: You will need

to use the OR function in this formula.)

a. A customer is eligible for a discount if the customer’s 2016 sales

greater than or equal to 100000 OR if the customers First Order was

placed in 2016.

b. If the customer qualifies for a discount, return a value of Y. (Tip: For

the value_if_true value, use “Y”.)

c. If the customer does not qualify for a discount, return a value of N.

(Tip: for the value_if_false, use “N”.)

6. 

In cell K4, enter a formula that uses a nested IF function and structured

references to calculate the discount percentage:

a. If the value in Discount Y/N column is equal to N, the Discount column

value should be 0.

b. If the value in the Discount Y/N column is equal to Y, the formula

should check if the value in the Tenure (Yrs) column is less than 5.

c. If the value in Tenure (Yrs) column is less than 5, the Discount column

value should be 0.07

d. Otherwise, the value of Discount column should be 0.1.

(Note: The Discount column is formatted with the Percentage Number

format, so the values returned by the nested IF function will appear as

0%, 7%, or 10%.)

7. 

In cell L4, Enter a formula that uses an IF function and structured

references to assign a priority value to each customer. (Tip: You will need

to use an AND function in this formula.)

a. The If function should check if a customer has a Tenure field value

greater than or equal to 5 years AND 2016 sales field value greater than

or equal to 150000.

b. If the customer meets both those criteria, the function should return

the value High.

c. If the customer does not meet both those criteria, the function should

return the value Low.

8. 

In cell Q4, nest the VLOOKUP function in an IFERROR function. (Hint: The

arguments for the VLOOKUP function should not be modified.) 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 Q3 in the CustomersNashua table

(located in the range A3:L32). The VLOOKUP function should then

retrieve the Discount value from the CustomersNashua 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 Q5 in the range

O10:R11 (which has the defined name CustomerIncentives). The

HLOOKUP function should then retrieve the value in the 2nd row of the

CustomerIncentives lookup 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 the CustomerAnalysis worksheet. In cell C4, enter a formula that

uses the COUNT function and structured references that counts the total

number of customers in the CustomersNashua table 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.)

12. 

In cell D4, enter a formula that uses the COUNTIF function and structured

references to count the number of customers with a Priority rating of

High in the CustomersNashua table on the CustomerList worksheet. (Tip:

The COUNTIF formula should use a structured reference to the Priority

column.)

13. 

In cell C5, enter a formula that uses the SUM function and structured

references to calculate 2016 sales for all customers.

14. 

In cell D5, enter a formula that uses the SUMIF function and structured

references to calculate 2016 sales for customers with a Priority rating of

High in the CustomersNashua table on the CustomerList worksheet.

15. In cell D6, enter a formula to calculate the percentage of High Priority

Customers Sales out of All Customers 2016 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.

16. In cell C7, enter a formula that uses the AVERAGE function and

structured references to calculate the average tenure of all customers in

the CustomersNashua table on the CustomerList worksheet.

17. In cell D7, enter a formula that uses the AVERAGEIF function and

structured references to calculate the average tenure of customers with

a Priority rating of High in the CustomersNashua table on the

CustomerList worksheet.

18. In cell C8, enter a formula to calculate the average sales per customer

for all customers. (Tip: To calculate this average, divide the total 2016

sales (cell C5) by the total number of customers (cell C4) rather than

using the AVERAGE function.)

19. In cell D8, enter a formula to calculate the average sales per High

Priority Customer. (Tip: To calculate this average, divide the total 2016

sales for high priority customers (cell D5) by the total number of high

priority customers (cell D4) rather than using the AVERAGEIF function.)