Create 3 lookup tables based on the following information: Unit Price: Apple, $45; Banana, $34; Grape,...

  

Transcribed Image Text:

Create 3 lookup tables based on the following information: Unit Price: Apple, $45; Banana, $34; Grape, $89; Kumquat, $150; Orange, $20 Sales Amount Discount: For the sales amount up to less than 1,000, the discount is 0%; for the sales amount from 1,000 up to less than 5,000, the discount is 2%; For the sales amount from 5,000 up to less than 10,000, the discount is 5%; for the sales amount 10,000 and up, the discount is 8%. Date Discount: Mon, 10%; Tue, 5%; Wed, 0%; Thu, 2%; Fri, 20% 2. Calculate Unit Price using VLOOKUP function 3. Calculate Extension/Sales Amount, which is equal to Unit Price X Quantity 4. Calculate Sales Amount Discount Amount using VLOOKUP function ((format cell to Currency) 5. Calculate Date Discount Amount using VLOOKUP function (format cell to Currency) 6. Calculate Discounted Price, which is equal to the Extension - (Sales Amount Discount)-(Date Discount Amount) 7. Calculate Total in cells F11 to 111 8. DO NOT change the order of the data from A1 to 112 ------- ------ Employee Product Quantity Sale Date Bob 23 Monday 45 Thursday Banana Jill Orange Bob Apple George Apple 65 Wednesday 58 Monday 79 Tuesday George Banana Bob Grape 132 Wednesday Jill Grape KumQuat 56 Monday 12 Friday Mike Unit Price Vlookup Name: TrchalekDaniel || | ||| ||| |||| Unit Price Totals: Sales/Extension Discount Vlookup Extension/Sales Sales Discount Amount (in Date Discount Amount (in Amount Dollar) Dollar) Date Discount Vlookup Discounted Price DO NOT change the order of the data in this area (i.e., A1:111). Doing so will cause a mis-match in grading. Format cells in columns G, H, and I in this table to currency. Create 3 lookup tables based on the following information: Unit Price: Apple, $45; Banana, $34; Grape, $89; Kumquat, $150; Orange, $20 Sales Amount Discount: For the sales amount up to less than 1,000, the discount is 0%; for the sales amount from 1,000 up to less than 5,000, the discount is 2%; For the sales amount from 5,000 up to less than 10,000, the discount is 5%; for the sales amount 10,000 and up, the discount is 8%. Date Discount: Mon, 10%; Tue, 5%; Wed, 0%; Thu, 2%; Fri, 20% 2. Calculate Unit Price using VLOOKUP function 3. Calculate Extension/Sales Amount, which is equal to Unit Price X Quantity 4. Calculate Sales Amount Discount Amount using VLOOKUP function ((format cell to Currency) 5. Calculate Date Discount Amount using VLOOKUP function (format cell to Currency) 6. Calculate Discounted Price, which is equal to the Extension - (Sales Amount Discount)-(Date Discount Amount) 7. Calculate Total in cells F11 to 111 8. DO NOT change the order of the data from A1 to 112 ------- ------ Employee Product Quantity Sale Date Bob 23 Monday 45 Thursday Banana Jill Orange Bob Apple George Apple 65 Wednesday 58 Monday 79 Tuesday George Banana Bob Grape 132 Wednesday Jill Grape KumQuat 56 Monday 12 Friday Mike Unit Price Vlookup Name: TrchalekDaniel || | ||| ||| |||| Unit Price Totals: Sales/Extension Discount Vlookup Extension/Sales Sales Discount Amount (in Date Discount Amount (in Amount Dollar) Dollar) Date Discount Vlookup Discounted Price DO NOT change the order of the data in this area (i.e., A1:111). Doing so will cause a mis-match in grading. Format cells in columns G, H, and I in this table to currency.

Answer rating: 100% (QA)

93 answers STEPS 1 CREATE TABLE OF DATA FOR UNIT PRICE SALE... View the full answer