Question: Please Help with questions 2 through 5. Your task is to complete the Orders worksheet using lookup functions to display the product description and to
Please Help with questions 2 through 5. Your task is to complete the Orders worksheet using lookup functions to display the product description and to calculate the order total and shipping, handling, and discount charges. You should not use any IF functions for this exercise
- Examine the contents of each worksheet, and name the ranges listed in the table below. Use these range names as appropriate in Questions 3 through 9.
| Range Name | Worksheet | Cells |
| ItemPrice | Costs | A5:C20 |
| HandlingFee | Handling | B2:G3 |
| Disc | Discounts | A2:A5 |
| TotOrderVol | Discounts | B2:B5 |
Need help with 2 - 5. 1 is completed just put it in there for reference.
- In cell C7 of the Orders worksheet, write a formula that displays the product description for the first item in the order. Copy the formula into cells C8:C11.
- In cell D7 of the Orders worksheet, calculate the total value of the item (price multiplied by quantity). Copy the formula into cells D8:D11.
- In cell D14 of the Orders worksheet, calculate the total cost of the order.
- In cell D15 of the Orders worksheet, calculate the total shipping charge for this order based on three variables: the ship-to region, the total weight, and the method of shipping.
- HINT: This will require a HLOOKUP and cells C2, C3, and C4 to retrieve the answer. For the rownumber argument of the HLOOKUP, use C3 + 1
D E Total B Golf Products Order Form 2 Shipping weight 175 3 Region number 1 4 Shipping Method 3 5 6 Item# Quantity Description 7 1 10 8 7 2 9 4 8 10 15 2 11 5 20 12 13 14 Total Order 15 Shipping 16 Handling 17 Discount 18 19 Grand Total 20 21 22 23 24 25 26 27 28 Orders Costs Handling Discounts Shipping A B D E 1 2 3 4 Item# Description 5 1 Titanium Driver - Men 6 2 Fusion Driver - Women 7 3 Titanium Driver - Women 8 4 Fairway Woods - Men 9 5 Hybrid Woods - Men 10 6 Fusion Fairway - Women 11 7 Stainless Steel Fairway - Women 12 8 Irons w/Steel Shafts - Men 13 9 Hybrid Irons - Men 14 10 Irons w/Steel Shafts - Women 15 11 Hybrid Irons - Women 16 12 Steel Putter - Men 17 13 Steel Putter - Women 18 14 Forged Wedges - Men 19 15 Package - Men 20 16 Package - Women 21 22 23 24 25 26 27 Orders Costs Handling Price/item $ 325.00 $ 545.00 $ 300.00 $ 199.00 $ 59.95 $ 500.00 $ 200.00 $ 525.00 $ 399.00 $ 375.00 $ 399.00 $ 119.99 $ 119.99 $ 105.00 $ 675.00 $ 675.00 Discounts Shipping B D E F G H $ $ 2,500 $ 5,000 $ 7,500 $ 0.006 0.005 0.004 10,000 $ 12,500 0.003 0.002 0.007 $ 30.00 A 1 Handling charges: 2 Total order value 3 Handling charge (% of order value) 4. 5 6 Maximum Handling Fee 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Orders Costs Handling Discounts Shipping + D E B C 1 Discount Total Order Value Description 2 $ $ Less than $5,000, no discount 3 $ 150 $ 5,000 At least $5,000 but less than $10,000, $150 discount 4 $ 400$ 10,000 At least $10,000 but less than $50,000, $400 discount 5 $ 1,000 $ 50,000 $50,000 or more, $1000 discount 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Orders Costs Handling Discounts Shipping D . A B E Shipping Method Region 2 3 4 1 $ 0.09 $ 0.10 $ 1.22 NA 2 $ 0.16 $ 0.11 $ 1.62 NA 3 $ 0.20 $ 0.12 $ 1.70 $ 0.16 4 $ 0.24 $ 0.14 $ 1.82 $ 0.16 5 $ 0.37 $ 0.33 $ 2.84 $ 0.28 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Orders Costs Handling Discounts Shipping + D E Total B Golf Products Order Form 2 Shipping weight 175 3 Region number 1 4 Shipping Method 3 5 6 Item# Quantity Description 7 1 10 8 7 2 9 4 8 10 15 2 11 5 20 12 13 14 Total Order 15 Shipping 16 Handling 17 Discount 18 19 Grand Total 20 21 22 23 24 25 26 27 28 Orders Costs Handling Discounts Shipping A B D E 1 2 3 4 Item# Description 5 1 Titanium Driver - Men 6 2 Fusion Driver - Women 7 3 Titanium Driver - Women 8 4 Fairway Woods - Men 9 5 Hybrid Woods - Men 10 6 Fusion Fairway - Women 11 7 Stainless Steel Fairway - Women 12 8 Irons w/Steel Shafts - Men 13 9 Hybrid Irons - Men 14 10 Irons w/Steel Shafts - Women 15 11 Hybrid Irons - Women 16 12 Steel Putter - Men 17 13 Steel Putter - Women 18 14 Forged Wedges - Men 19 15 Package - Men 20 16 Package - Women 21 22 23 24 25 26 27 Orders Costs Handling Price/item $ 325.00 $ 545.00 $ 300.00 $ 199.00 $ 59.95 $ 500.00 $ 200.00 $ 525.00 $ 399.00 $ 375.00 $ 399.00 $ 119.99 $ 119.99 $ 105.00 $ 675.00 $ 675.00 Discounts Shipping B D E F G H $ $ 2,500 $ 5,000 $ 7,500 $ 0.006 0.005 0.004 10,000 $ 12,500 0.003 0.002 0.007 $ 30.00 A 1 Handling charges: 2 Total order value 3 Handling charge (% of order value) 4. 5 6 Maximum Handling Fee 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Orders Costs Handling Discounts Shipping + D E B C 1 Discount Total Order Value Description 2 $ $ Less than $5,000, no discount 3 $ 150 $ 5,000 At least $5,000 but less than $10,000, $150 discount 4 $ 400$ 10,000 At least $10,000 but less than $50,000, $400 discount 5 $ 1,000 $ 50,000 $50,000 or more, $1000 discount 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Orders Costs Handling Discounts Shipping D . A B E Shipping Method Region 2 3 4 1 $ 0.09 $ 0.10 $ 1.22 NA 2 $ 0.16 $ 0.11 $ 1.62 NA 3 $ 0.20 $ 0.12 $ 1.70 $ 0.16 4 $ 0.24 $ 0.14 $ 1.82 $ 0.16 5 $ 0.37 $ 0.33 $ 2.84 $ 0.28 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Orders Costs Handling Discounts Shipping +
Study Help