1) 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.)

2) 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.

3) 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?.)

4) 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%.)

5) 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.

6 ) 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.

7) 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 11^th column in the table) for this record. The VLOOKUP function should find an exact match to the value in Q3.

8) 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 2^nd 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.

9 ) 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.)

10) 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.)

11) In cell C5, enter a formula that uses the SUM function and structured references to calculate 2016 sales for all customers.

12) 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.

13) 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.

14) 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.

15) 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.

16) 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.)

17) 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.)

New Perspectives Excel 2013 Tutorial 8: SAM Project 1a Spring Software ADVANCED FUNCTIONS AND CONDITIONAL FORMATTING Author: Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. Current Year 2017 Spring Software-Active Customer Database (Nashua) Tenure (Yrs) 2014 Sales 2015 Sales 2-year Growth Percentage Discount Y/N Customer ID Customer First Order 1001 Quantum Engineering 2010 $ 700,000 $ 720,000 $ 720,000 Err:508 Customer Name Err:508 1002 Lakeside Caf 2014 $ 20,002 $ 20,002 $ 21,002 Err:508 2016 Sales Err:508 1003 B&W Lumber 2015 $ - $ 27,500 $ 29,500 Err:508 Discount 1004 Surfside Pool & Patio 2014 $ 140,000 $ 142,000 $ 145,000 Err:508 Customer Incentives 1005 Whitefield School System 2013 $ 95,000 $ 96,000 $ 97,000 Err:508 1006 Grapevine Consulting Grp 2012 $ 323,000 $ 350,000 $ 500,000 Err:508 1007 Fairfield Asset Management 2011 $ 317,000 $ 355,000 $ 410,000 Err:508 Yearly Sales $0 $75,000 $150,000 $400,000 1008 Whitehead Financial 2010 $ 247,000 $ 256,000 $ 301,000 Err:508 Free Service None Onsite Training Weekend Tech Support Dedicated Tech Support 1009 Funstop Toys 2016 $ - $ 43,000 Err:508 1010 Anna's Boutiques 2013 $ 15,300 $ 15,400 $ 15,500 Err:508 1011 Sunnyside Stores 2009 $ 183,000 $ 187,000 $ 172,000 Err:508 1012 Bedfordshire School System 2011 $ 65,000 $ 124,000 $ 125,000 Err:508 1013 Westview Tutors 2014 $ 105,000 $ 125,000 $ 140,000 Err:508 1014 BWM Partners 2016 $ - $ 233,000 Err:508 1012 Firehouse Partners LLC 2012 $ 75,000 $ 161,000 $ 200,600 Err:508 1016 Forest Construction LLC 2015 $ - $ 257,000 $ 301,000 Err:508 1017 Farmington Strategy Group 2013 $ 250,000 $ 275,000 $ 301,000 Err:508 1018 Diamond School District 2010 $ 183,000 $ 191,000 $ 193,000 Err:508 1019 Granite State Communications 2009 $ 245,000 $ 251,000 $ 302,000 Err:508 1020 Green Mountain Publishing 2014 $ 377,500 $ 379,500 $ 381,000 Err:508 1021 Atlantic Learning Group 2013 $ 235,000 $ 246,000 $ 250,000 Err:508 1022 Fontas Convenience 2010 $ 79,000 $ 88,450 $ 77,000 Err:508 1022 Applewild Learning Center 2009 $ 133,000 $ 138,000 $ 142,000 Err:508 1024 Acme Printing 2013 $ 99,000 $ 101,000 $ 100,000 Err:508 1025 Pinnacle Publications 2015 $ - $ 157,000 $ 184,010 Err:508 1026 Fox Graphics 2015 $ - $ 233,000 $ 256,000 Err:508 1027 Brookfield Regional Schools 2016 $ - $ $ 98,000 Err:508 1028 Ludlow Pharmacy Group 2014 $ 58,550 $ 75,650 $ 98,000 Err:508 1029 Wayside Public 2008 $ 75,000 $ 112,000 $ 117,000 Err:508 $ $ 2016 Sales Customer Details - - - 2-Year Sales Growth Discount Priority Customer ID 1119 Customer Incentives Customer Analysis - Nashua All Customers Number of Customers 2016 Sales Percent of 2016 Sales Average Tenure Average Sales/Customer High Priority Customers New Perspectives Excel 2013 Tutorial 8: SAM Project 1a Spring Software ADVANCED FUNCTIONS AND CONDITIONAL FORMATTING Author: morgyken@gmail.com Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. morgyken@gmail.com Current Year 2017 Spring Software-Active Customer Database (Nashua) Customer Details 2-Year Sales Growth 2-year Growth Percentage Discount Y/N Discount 720,000 Err:508 Err:508 Err:508 Err:508 Err:508 Customer Name 20,002 $ 21,002 Err:508 Err:508 Err:508 Err:508 Err:508 2016 Sales $ 27,500 $ 29,500 Err:508 Err:508 Err:508 Err:508 Err:508 Discount $ 140,000 $ 142,000 $ 145,000 Err:508 Err:508 Err:508 Err:508 Err:508 Customer Incentives Err:509 $ 95,000 $ 96,000 $ 97,000 Err:508 Err:508 Err:508 Err:508 Err:508 2012 Err:509 $ 323,000 $ 350,000 $ 500,000 Err:508 Err:508 Err:508 Err:508 Err:508 Fairfield Asset Management 2011 Err:509 $ 317,000 $ 355,000 $ 410,000 Err:508 Err:508 Err:508 Err:508 Err:508 Yearly Sales $0 $75,000 $150,000 $400,000 1008 Whitehead Financial 2010 Err:509 $ 247,000 $ 256,000 $ 301,000 Err:508 Err:508 Err:508 Err:508 Err:508 Free Service None Onsite Training Weekend Tech Support Dedicated Tech Support 1009 Funstop Toys 2016 Err:509 $ - $ 43,000 Err:508 Err:508 Err:508 Err:508 Err:508 1010 Anna's Boutiques 2013 Err:509 $ 15,300 $ 15,400 $ 15,500 Err:508 Err:508 Err:508 Err:508 Err:508 1011 Sunnyside Stores 2009 Err:509 $ 183,000 $ 187,000 $ 172,000 Err:508 Err:508 Err:508 Err:508 Err:508 1012 Bedfordshire School System 2011 Err:509 $ 65,000 $ 124,000 $ 125,000 Err:508 Err:508 Err:508 Err:508 Err:508 1013 Westview Tutors 2014 Err:509 $ 105,000 $ 125,000 $ 140,000 Err:508 Err:508 Err:508 Err:508 Err:508 1014 BWM Partners 2016 Err:509 $ - $ 233,000 Err:508 Err:508 Err:508 Err:508 Err:508 1015 Firehouse Partners LLC 2012 Err:509 $ 75,000 $ 161,000 $ 200,600 Err:508 Err:508 Err:508 Err:508 Err:508 1016 Forest Construction LLC 2015 Err:509 $ - $ 257,000 $ 301,000 Err:508 Err:508 Err:508 Err:508 Err:508 1017 Farmington Strategy Group 2013 Err:509 $ 250,000 $ 275,000 $ 301,000 Err:508 Err:508 Err:508 Err:508 Err:508 1018 Diamond School District 2010 Err:509 $ 183,000 $ 191,000 $ 193,000 Err:508 Err:508 Err:508 Err:508 Err:508 1019 Granite State Communications 2009 Err:509 $ 245,000 $ 251,000 $ 302,000 Err:508 Err:508 Err:508 Err:508 Err:508 1020 Green Mountain Publishing 2014 Err:509 $ 377,500 $ 379,500 $ 381,000 Err:508 Err:508 Err:508 Err:508 Err:508 1021 Atlantic Learning Group 2013 Err:509 $ 235,000 $ 246,000 $ 250,000 Err:508 Err:508 Err:508 Err:508 Err:508 1022 Fontas Convenience 2010 Err:509 $ 79,000 $ 88,450 $ 77,000 Err:508 Err:508 Err:508 Err:508 Err:508 1023 Applewild Learning Center 2009 Err:509 $ 133,000 $ 138,000 $ 142,000 Err:508 Err:508 Err:508 Err:508 Err:508 1024 Acme Printing 2013 Err:509 $ 99,000 $ 101,000 $ 100,000 Err:508 Err:508 Err:508 Err:508 Err:508 1025 Pinnacle Publications 2015 Err:509 $ - $ 157,000 $ 184,010 Err:508 Err:508 Err:508 Err:508 Err:508 1026 Fox Graphics 2015 Err:509 $ - $ 233,000 $ 256,000 Err:508 Err:508 Err:508 Err:508 Err:508 1027 Brookfield Regional Schools 2016 Err:509 $ - $ $ 98,000 Err:508 Err:508 Err:508 Err:508 Err:508 1028 Ludlow Pharmacy Group 2014 Err:509 $ 58,550 $ 75,650 $ 98,000 Err:508 Err:508 Err:508 Err:508 Err:508 1029 Wayside Public 2008 Err:509 $ 75,000 $ 112,000 $ 117,000 Err:508 Err:508 Err:508 Err:508 Err:508 Customer ID Customer First Order Tenure (Yrs) 2014 Sales 2015 Sales 1001 Quantum Engineering 2010 Err:509 $ 700,000 $ 720,000 $ 1002 Lakeside Caf 2014 Err:509 $ 20,002 $ 1003 B&W Lumber 2015 Err:509 $ - 1004 Surfside Pool & Patio 2014 Err:509 1005 Whitefield School System 2013 1006 Grapevine Consulting Grp 1007 $ $ 2016 Sales - - - Priority Customer ID 1019 Err:508 None Customer Incentives TO BE CONTINUED Customer Analysis - Nashua All Customers Number of Customers 2016 Sales Percent of 2016 Sales Average Tenure Average Sales/Customer High Priority Customers Err:508 Err:508 $ Err:508 Err:508 Err:508 205,262 $ Err:508 294,143 New Perspectives Excel 2013 Tutorial 8: SAM Project 1a Spring Software ADVANCED FUNCTIONS AND CONDITIONAL FORMATTING Author: morgyken@gmail.com Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. morgyken@gmail.com Current Year 2017 Spring Software-Active Customer Database (Nashua) Customer Details 2-Year Sales Growth 2-year Growth Percentage Discount Y/N Discount 720,000 Err:508 Err:508 Err:508 Err:508 Err:508 Customer Name 20,002 $ 21,002 Err:508 Err:508 Err:508 Err:508 Err:508 2016 Sales $ 27,500 $ 29,500 Err:508 Err:508 Err:508 Err:508 Err:508 Discount $ 140,000 $ 142,000 $ 145,000 Err:508 Err:508 Err:508 Err:508 Err:508 Customer Incentives Err:509 $ 95,000 $ 96,000 $ 97,000 Err:508 Err:508 Err:508 Err:508 Err:508 2012 Err:509 $ 323,000 $ 350,000 $ 500,000 Err:508 Err:508 Err:508 Err:508 Err:508 Fairfield Asset Management 2011 Err:509 $ 317,000 $ 355,000 $ 410,000 Err:508 Err:508 Err:508 Err:508 Err:508 Yearly Sales $0 $75,000 $150,000 $400,000 1008 Whitehead Financial 2010 Err:509 $ 247,000 $ 256,000 $ 301,000 Err:508 Err:508 Err:508 Err:508 Err:508 Free Service None Onsite Training Weekend Tech Support Dedicated Tech Support 1009 Funstop Toys 2016 Err:509 $ - $ 43,000 Err:508 Err:508 Err:508 Err:508 Err:508 1010 Anna's Boutiques 2013 Err:509 $ 15,300 $ 15,400 $ 15,500 Err:508 Err:508 Err:508 Err:508 Err:508 1011 Sunnyside Stores 2009 Err:509 $ 183,000 $ 187,000 $ 172,000 Err:508 Err:508 Err:508 Err:508 Err:508 1012 Bedfordshire School System 2011 Err:509 $ 65,000 $ 124,000 $ 125,000 Err:508 Err:508 Err:508 Err:508 Err:508 1013 Westview Tutors 2014 Err:509 $ 105,000 $ 125,000 $ 140,000 Err:508 Err:508 Err:508 Err:508 Err:508 1014 BWM Partners 2016 Err:509 $ - $ 233,000 Err:508 Err:508 Err:508 Err:508 Err:508 1015 Firehouse Partners LLC 2012 Err:509 $ 75,000 $ 161,000 $ 200,600 Err:508 Err:508 Err:508 Err:508 Err:508 1016 Forest Construction LLC 2015 Err:509 $ - $ 257,000 $ 301,000 Err:508 Err:508 Err:508 Err:508 Err:508 1017 Farmington Strategy Group 2013 Err:509 $ 250,000 $ 275,000 $ 301,000 Err:508 Err:508 Err:508 Err:508 Err:508 1018 Diamond School District 2010 Err:509 $ 183,000 $ 191,000 $ 193,000 Err:508 Err:508 Err:508 Err:508 Err:508 1019 Granite State Communications 2009 Err:509 $ 245,000 $ 251,000 $ 302,000 Err:508 Err:508 Err:508 Err:508 Err:508 1020 Green Mountain Publishing 2014 Err:509 $ 377,500 $ 379,500 $ 381,000 Err:508 Err:508 Err:508 Err:508 Err:508 1021 Atlantic Learning Group 2013 Err:509 $ 235,000 $ 246,000 $ 250,000 Err:508 Err:508 Err:508 Err:508 Err:508 1022 Fontas Convenience 2010 Err:509 $ 79,000 $ 88,450 $ 77,000 Err:508 Err:508 Err:508 Err:508 Err:508 1023 Applewild Learning Center 2009 Err:509 $ 133,000 $ 138,000 $ 142,000 Err:508 Err:508 Err:508 Err:508 Err:508 1024 Acme Printing 2013 Err:509 $ 99,000 $ 101,000 $ 100,000 Err:508 Err:508 Err:508 Err:508 Err:508 1025 Pinnacle Publications 2015 Err:509 $ - $ 157,000 $ 184,010 Err:508 Err:508 Err:508 Err:508 Err:508 1026 Fox Graphics 2015 Err:509 $ - $ 233,000 $ 256,000 Err:508 Err:508 Err:508 Err:508 Err:508 1027 Brookfield Regional Schools 2016 Err:509 $ - $ $ 98,000 Err:508 Err:508 Err:508 Err:508 Err:508 1028 Ludlow Pharmacy Group 2014 Err:509 $ 58,550 $ 75,650 $ 98,000 Err:508 Err:508 Err:508 Err:508 Err:508 1029 Wayside Public 2008 Err:509 $ 75,000 $ 112,000 $ 117,000 Err:508 Err:508 Err:508 Err:508 Err:508 Customer ID Customer First Order Tenure (Yrs) 2014 Sales 2015 Sales 1001 Quantum Engineering 2010 Err:509 $ 700,000 $ 720,000 $ 1002 Lakeside Caf 2014 Err:509 $ 20,002 $ 1003 B&W Lumber 2015 Err:509 $ - 1004 Surfside Pool & Patio 2014 Err:509 1005 Whitefield School System 2013 1006 Grapevine Consulting Grp 1007 $ $ 2016 Sales - - - Priority Customer ID 1019 Err:508 None Customer Incentives TO BE CONTINUED Customer Analysis - Nashua All Customers Number of Customers 2016 Sales Percent of 2016 Sales Average Tenure Average Sales/Customer High Priority Customers Err:508 Err:508 $ Err:508 Err:508 Err:508 205,262 $ Err:508 294,143