1. Start Excel and open Test 2 Data File Version A.xlsx. 2. Save the spreadsheet as Your Last Name - Test 2 Version A. (ie Boisvenue - Test 2 Version A) 3. Select the Documentation sheet and enter your name and date in cells B4 and B6. 4. In the Pricing and Shipping worksheet, assign the define name ShippingCost to the data in the range D3:E7. 5. In the Customer Invoice worksheet, in the Item column (range B16:B26), use data validation to create a list of the items in the Product Pricing table from the Pricing and Shipping worksheet. 6. In the Flavor column (range E16:E26), use data validation to create an input message only indicating that the popcorn flavor should be entered. The flavors are located below the invoice. You do not need to create a list of flavours. 7. In the Price cell (cell G16), use a VLOOKUP function to retrieve the price of the ordered item listed in the Product Pricing table in the Pricing and Shipping worksheet. When no item is selected, this cell will display an error message. 8. Modify the formula in the cell G16 by combining the IFERROR function with the VLOOKUP function to display either the price or a blank cell if an error value occurs. Copy the formula down the range G16:G26 fill without formatting. 9. In the Total column (range H16:H26), enter a formula to calculate the total charge for that row (Qty x Price). Use the IFERROR function to display either the total charge or a blank cell if an error value occurs. 10.In the Subtotal cell (Cell H27), add a formula to sum the Total column. Use the IFERROR function to display either the total charge or a blank cell if an error value occurs. 11.In the Sales Tax Cell (H28), enter a formula with an IF function so if the customers state (Cell 12) is NV, then calculate 6.85 percent of the subtotal (cell H27); otherwise, use 0 for the sales tax. 12.In the shipping cell (cell H29), enter a VLOOKUP formula to look up the shipping cost from the Shipping Cost table in the Pricing and Shipping worksheet based on the subtotal in cell H27. 13.In the Total Due cell (cell H30), calculate the invoice total by entering a formula that adds the values in the Subtotal, Sales Tax, and Shipping cells. 14.Test the worksheet by using the following order date: a. Sold to: Harry Potter b. Street: 4 Privet Drive c. City, State, Zip: Surrey, NV 89415 d. Date Sold: Function for Todays date ISP3026 Test 2 Version A Page 3 of 6 e. Item #1: Gourmet (2) 1g f. Flavor: Nacho Cheese g. Quantity: 2 h. Item 2: Plain Tin 1g i. Flavor: None j. Quantity: 1 k. Item 3: Standard (2 ) 3 g l. Flavor: Cheesy Cheddar m. Quantity: 1