# Project #89838 - Excel workbook

1) in the Product pricing and shipping worksheet, assign the defined name ShippingCost to the data stores in the range D2:E7, which can be used for an approximate match lookup. (Hint: the lookup table includes only the values, not the descriptive labels.)

2) In the Invoice worksheet, use data validation to make it easier to enter ordered items in the range C16:C36 buu creating a list of the different items in the Product Pricing table in the Product Pricing and shipping workseet. (Hint: select the entire range before setting the validation rule.)

3) In the Per Unit column (the range G16:G36) use a VLOOKUP function to retrieve the per-unit price of each ordered product from the product pricing data in the range A3:B28 in the product and shipping workseet. (hing: use the definde name ProductPrice that was assigned to the product pricing data.)

4) Modify the formula in the Per Unit column by combinidng the IFERROR function with the VLOOKUP function to display either the per-unit price or a black cell if an error value occurs.

5) In the total column (te range H16:H36) enter a formula to calculate the toal charge for that row (Qty x per unit) use the IFERROR function to display either the total charge or a blank cell if an error value occurs. Format the column appropriately.

6) In the subtotal cell (cell H37) add a formula to sum the Total coolum. Use the IFERROR function to display either the subtotal or a blank cell if an error value occurs. Format this cell appropriately.

7) In the sales tax cell (cell H38) enter a fomula with nested IF function to caluclate 8.25 percent of the subtotal (cell H37) if the customer state (Cell D12) is OH, or 8.75 percent if the state is MI, otherwise, use 0 percent for the sales tax. Formate this cell approprately (hint: the deinded name Subtotl is assigned to cell H37. Note that the defined name :subtotl: is intentionally not spelled as "subtotal" which is the name of an Excel function. The defined name State is assigned to cell D12)

8) In the shipping clel (H39) enter a formula that looks up the shipping cost from the shipping cost table in the product pricing and shiipping workseet based o the subtotal in cell H37. If the subtotal is 0, the shipping cost should displace 0. Formate this cell appropriately (Hint: Use the defined name you created for the shipping cost table data)

9) In the total due cell (cell H40 calucalte the invoice total by entering a formula that adds the values in the Subtotal, sales tax, and shipping cells. Formate this cell appropriately.

10) Tes the workset using the following data:

Sold to   Ellen Farmer  address: 222 central avenue  arlington, MI 60005

Date: 6/152016

Items ordered Blanket 2  Duffle bag - large 1   scarf 2.

 Subject Business Due By (Pacific Time) 10/30/2015 12:00 am
TutorRating
pallavi

Chat Now!

out of 1971 reviews
amosmm

Chat Now!

out of 766 reviews
PhyzKyd

Chat Now!

out of 1164 reviews
rajdeep77

Chat Now!

out of 721 reviews
sctys

Chat Now!

out of 1600 reviews

Chat Now!

out of 770 reviews
topnotcher

Chat Now!

out of 766 reviews
XXXIAO

Chat Now!

out of 680 reviews