3. The boss decides that using only 3 levels—small, medium, and large—does not give the shipping 1 point department enough information. She wants to use the 10 levels shown in Lookup Table 1 (A3:B12). For example, Order Quantity from 1 through 5 should be a "Mini" Type, Order Quantity from 6 through 10 should be a "Extra Small" Type, and so on. Write a formula in the “Expanded Order Type” column to assign the correct type to each order by “Order Quantity.” Copy the formula down to all the rows. Based on the layout of Lookup Table 1, which of the following function you should be using? (O VLOOKUP with a FALSE (exact match) option (® VLOOKUP with a TRUE (approximate match) option O HLOOKUP with a FALSE (exact match) option (O HLOOKUP with a TRUE (approximate match) option 4. Using your newly created “Expanded Order Type” column, calculate the total “Sales” for all orders of 1point “Medium” type (rounded to 2 decimal places). Do NOT put a "$" sign or commas in your answer. Hint: The SUMIF() function is useful here. Enter answer here 5. The company gives a 1% discount on any Extra Large or larger orders. In the “Discount” column, 1 point create a formula that returns 0.01 if the “Expanded Order Type” is Extra Large, XX Large, or XXX Large, and returns 0 otherwise. You can accomplish this by using a (select all that apply): [J VLOOKUP formula [J HLOOKUP formula [J Nested IF formula O a single IF formula (without additional formulae) 6. Create the formula from the previous question and copy the formula down to all the rows. How 1 point many orders will have a discount applied? Hint: The COUNTIF() function is useful here. Enter answer here 7. The “Sales with Discount” can be calculated by applying the discount to the original “Sales” entry: 1 point Sales with Discount = Sales - (Sales * Discount) or Sales with Discount = Sales * (1 - Discount) Create a formula in the “Sales with Discount” column and copy it down to all the rows. What is the impact of the discount on total sales? In other words, what is the difference between the sum of the “Sales” and the sum of the “Sales with Discount” (rounded to 2 decimal places)? Do not putany $ sign or commas in your answer.
