Shipping costs vary depending on method of shipping and the zone number of the destination. Creating a dynamic retrieval function leveraging the INDIRECT function can make it easily to calculate shipping costs for customers. On the ShippingCosts worksheet, create a Priority named range for cells A5:F10 and then create an Express named range for cells A15:F20. In cell I7, enter a VLOOKUP function to retrieve the correct shipping costs based on the weight and zone. Use the INDIRECT function for the table_array so that the customer can choose Express or priority shiping.

Several formulas on the Transactions worksheet resulted in #N/A where the lookup value is blank. The #N/A is not only distracting, it is confusing to users because they may believe that there are calculation errors when in actuality the errors are valid errors. On the Transactions worksheet, incorporate the IFERROR function in cells G10, H10, and J10 so that a blank value ("") is returned for the value_if_error. Use the fill handle to copy each formula down through row 30.

Aleeta would like to know the net revenue generated from each transaction as well as the total discounts for each transaction.On the Transactions worksheet, in cell L10, calculate the net revenue by multiplying the gross revenue by 1 - the sum of all discounts. If the calculation returns an error, replace the error with a blank value (""). Use the fill handle to copy the formula down through L30. In cell M10, calculate the total discount for the transaction by subtracting the net revenue from the gross revenue. If the calculation returns an error, replace the error with a blank value (""). Use the fill handle to copy the formula down through M30.

Shipping Costs Weight (lbs) 0.5 2 Priority 5.5 Zone 2 Priority Mail Rates Zone 2 Zone 3 $6.35 $6.79 $6.35 $6.79 $6.89 $7.03 $7.29 $8.53 $8.24 $9.63 $8.95 $10.18 Zone 1 $6.35 $6.35 $6.89 $7.29 $8.24 $8.95 4 Zone 4 $6.89 $6.89 $7.18 $9.42 $10.99 $11.12 Shipping Method Weight (lbs) Zone Shipping Costs Zone 5 $6.98 $6.98 $7.91 $13.77 $16.84 $19.51 6 8 10 Weight (lbs) 0.5 2 4 Zone 1 $7.62 $7.62 $8.27 $8.75 $9.89 $10.74 Express Mail Rates Zone 2 Zone 3 $7.62 $8.15 $7.62 $8.15 $8.27 $8.44 $8.75 $10.24 $9.89 $11.56 $10.74 $12.22 Zone 4 $8.27 $8.27 $8.62 $11.30 $13.19 $13.34 Zone 5 $8.38 $8.38 $9.49 $16.52 $20.21 $23.41 6 8 10 Transaction Analysis Coupons CouponCode Discount T453X 15% C384R 10% T382P 5% Travel Agent Online Travel Promotion Local Magazine Promotion Coupon Notes Promotion Reward Program Discounts Rewards1 Rewards2 Quantity 65.90 0.1 0.1 0.1 0.1 0.1 TransID P000100 PO00101 P000102 P000103 P000104 PO00105 PO00106 PO00107 PO00108 PO00109 P000110 P000111 P000112 PO00113 P000114 PO00115 ItemID C884P23 T822248 F232P37 C583K29 F232P37 F375P67 X740029 T981011 C884P23 X487248 R483P24 R483P24 X349R39 F833K19 R239157 C583K29 Payment Type Cash Cash Apple Pay APP! Apple Pay Cash Cash Apple Pay Cash Cash Apple Pay Apple Pay Card Apple Pay Apple Pay Card Card #N/A CouponCode ClubMember? Promoltem? 3T453X No No 2 No Yes 1 Yes No 3 No Yes 3 T453X No No 3 T453X Yes INo 1 Yes Yes 1 1 1 No #N/A 2 2 No No 3C384R No #N/A 1 C384R No #N/A 1 T453X Yes #N/A 3T382P No #N/A 3T382P No #N/A 2T453X No No 1 No #N/A #N/A #N/A #N/A #N/A #N/A #N/A GrossRevenue Member Disc $ 171.00 $ $ 129.00 7% $ 177.00 $ 387.00 $ 46.35 7% $ 453.00 7% $ 12.00 $ 114.00 $ 1,257.00 $ 2.00 $ 2.00 7% $ 149.85 $ 17.85 $ 194.00 $ 59.00 #N/A #N/A #N/A #N/A #N/A NetRevenue TotalDiscounts 5%$ 136.80 $ 34.20 $ 65.90 $ $ 119.97 $ 9.03 $ 177.00 $ 5% $ 309.60 $ 77.40 $ 36.15$ 10.20 $ 421.29 $ 31.71 $ 12.00 $ 5% $ 108.30 $ 5.70 $ 1,131.30$ 125.70 $ 1.80 $ 0.20 $ 1.56 $ 0.44 $ 142.36 $ 7.49 $ 16.96 $ 0.89 $ 164.90$ 29.10 $ 59.00 $ Coupon Cash Disc 15% #N/A #N/A #N/A 15% 15% #N/A #N/A #N/A 10% 10% 15% 5% 596 15% #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N #N/A #N/A #N/A #N/A #N/A #N/A #N/A Shipping Costs Weight (lbs) 0.5 2 Priority 5.5 Zone 2 Priority Mail Rates Zone 2 Zone 3 $6.35 $6.79 $6.35 $6.79 $6.89 $7.03 $7.29 $8.53 $8.24 $9.63 $8.95 $10.18 Zone 1 $6.35 $6.35 $6.89 $7.29 $8.24 $8.95 4 Zone 4 $6.89 $6.89 $7.18 $9.42 $10.99 $11.12 Shipping Method Weight (lbs) Zone Shipping Costs Zone 5 $6.98 $6.98 $7.91 $13.77 $16.84 $19.51 6 8 10 Weight (lbs) 0.5 2 4 Zone 1 $7.62 $7.62 $8.27 $8.75 $9.89 $10.74 Express Mail Rates Zone 2 Zone 3 $7.62 $8.15 $7.62 $8.15 $8.27 $8.44 $8.75 $10.24 $9.89 $11.56 $10.74 $12.22 Zone 4 $8.27 $8.27 $8.62 $11.30 $13.19 $13.34 Zone 5 $8.38 $8.38 $9.49 $16.52 $20.21 $23.41 6 8 10 Transaction Analysis Coupons CouponCode Discount T453X 15% C384R 10% T382P 5% Travel Agent Online Travel Promotion Local Magazine Promotion Coupon Notes Promotion Reward Program Discounts Rewards1 Rewards2 Quantity 65.90 0.1 0.1 0.1 0.1 0.1 TransID P000100 PO00101 P000102 P000103 P000104 PO00105 PO00106 PO00107 PO00108 PO00109 P000110 P000111 P000112 PO00113 P000114 PO00115 ItemID C884P23 T822248 F232P37 C583K29 F232P37 F375P67 X740029 T981011 C884P23 X487248 R483P24 R483P24 X349R39 F833K19 R239157 C583K29 Payment Type Cash Cash Apple Pay APP! Apple Pay Cash Cash Apple Pay Cash Cash Apple Pay Apple Pay Card Apple Pay Apple Pay Card Card #N/A CouponCode ClubMember? Promoltem? 3T453X No No 2 No Yes 1 Yes No 3 No Yes 3 T453X No No 3 T453X Yes INo 1 Yes Yes 1 1 1 No #N/A 2 2 No No 3C384R No #N/A 1 C384R No #N/A 1 T453X Yes #N/A 3T382P No #N/A 3T382P No #N/A 2T453X No No 1 No #N/A #N/A #N/A #N/A #N/A #N/A #N/A GrossRevenue Member Disc $ 171.00 $ $ 129.00 7% $ 177.00 $ 387.00 $ 46.35 7% $ 453.00 7% $ 12.00 $ 114.00 $ 1,257.00 $ 2.00 $ 2.00 7% $ 149.85 $ 17.85 $ 194.00 $ 59.00 #N/A #N/A #N/A #N/A #N/A NetRevenue TotalDiscounts 5%$ 136.80 $ 34.20 $ 65.90 $ $ 119.97 $ 9.03 $ 177.00 $ 5% $ 309.60 $ 77.40 $ 36.15$ 10.20 $ 421.29 $ 31.71 $ 12.00 $ 5% $ 108.30 $ 5.70 $ 1,131.30$ 125.70 $ 1.80 $ 0.20 $ 1.56 $ 0.44 $ 142.36 $ 7.49 $ 16.96 $ 0.89 $ 164.90$ 29.10 $ 59.00 $ Coupon Cash Disc 15% #N/A #N/A #N/A 15% 15% #N/A #N/A #N/A 10% 10% 15% 5% 596 15% #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N #N/A #N/A #N/A #N/A #N/A #N/A #N/A