Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

1.In cell P5, nest the VLOOKUP function in an IFERROR function. The cell should display the error message Invalid Customer ID instead of the error

1.In cell P5, 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 P4 to 119.

2.In cell P7, enter a formula using the VLOOKUP function to look up the Customer ID value shown in cell P4 in the Customers table (located in the range A3:M27). The VLOOKUP function should then retrieve the Discount Amount from the Customers table (the 12th column in the table) for this record. The VLOOKUP function should find an exact match to the value in P4.

3.In cell P8, enter a formula using the HLOOKUP function to determine the Reward that the customer qualifies for based on their 2016 spending. The HLOOKUP function should look up the value in cell P6 in the range P13:S14 (which has the defined name Rewards). The HLOOKUP function should then retrieve the value in the 2nd row of the Rewards lookup table. Since these rewards are offered to customers that meet or exceed the yearly spending levels listed in the Rewards table, the HLOOKUP function should find an approximate match to the value in P6.

4.Go to the Analysis worksheet. In cell B5, enter a formula that uses the COUNT function and structured references that counts the total number of customers in the Customers table on the All Customers worksheet. (Hint: The COUNT function only counts rows that contain a value; you cannot use the LastName column as an argument in the function. Use the Customer ID column instead.)

5.In cell B11, enter a formula that uses the COUNTIF function and structured references to count the number of customers with a Value Rating of High in the Customers table on the All Customers worksheet. (Hint: The COUNTIF formula should use a structured reference to the Customer Value Rating column.)

6.In cell B6, enter a formula that uses the SUM function and structured references to calculate the total Current Year Purchases (YTD) for all customers.

7.In cell B12, enter a formula that uses the SUMIF function and structured references to calculate total Current Year Purchases (YTD) for customers with a Customer Value Rating of High in the Customers table on the All Customers worksheet.

8.In cell B13, enter a formula to calculate the percentage of all Current Year Purchases accounted for by High Value customers. (Hint: The calculation should divide the Total Sales (Current Year) for High Value Customers by the Total Sales (Current Year) for all customers.) Format the cell using the Percentage Number format with 0 decimal places.

9.In cell B7, enter a formula that uses the AVERAGE function and structured references to calculate the average tenure of all customers in the Customers table on the All Customers worksheet.

10.In cell B14, enter a formula that uses the AVERAGEIF function and structured references to calculate the average tenure of customers with a Customer Value Rating of High in the Customers table on the All Customers worksheet.

11.In cell B8, enter a formula to calculate the average spending per customer for all customers. (Hint: To calculate this average, divide the Total Sales (Current Year) in cell B6 by the Number of Customers in cell B5, rather than using the AVERAGE function.)

12. In cell B15, enter a formula to calculate the average spending per customer for high value customers. (Hint: To calculate this average, divide the Total Sales (Current year) for high value customers in cell B12 by the total number of high value customers in cell B11, rather than using the AVERAGEIF function.)

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

How To Perform A Building Water Audit

Authors: Troy Aichele

1st Edition

1651578273, 978-1651578278

More Books

Students also viewed these Accounting questions

Question

If its not broken, dont fix it. Evaluate this statement.

Answered: 1 week ago

Question

Create a Fishbone diagram with the problem being coal "mine safety

Answered: 1 week ago