1. If a customer orders up to 500 units of a product, he/she pays the full price of $10.00 per unit. If a customer orders more than 1000 units of a product, he/she receives a 50% off discount per unit. If a customer orders more than 500 units but less than or equal to 1000 units, he/she receives a 25% off discount per unit.

[IF nested within another IF] Column A shows the invoice number. Column B shows the number of units for that order. Column C shows the price per unit before any discount (i.e., $10.00). In Column E, you will craft a formula to calculate the total price for each order as a function of the number of units purchased.

[IF and AND combination] Column D shows customer status whether a customer is a VIP member. An order qualifies for free shipping only when its total price exceeds $3000 and it is placed by a VIP customer. In Column F, you will create a formula to calculate shipping cost if an order qualifies for free shipping, you display the number zero; otherwise, you calculate the shipping cost as 5% of the total price.

Write the formulas in cells E2 and F2 in a way that they can be copyandpasted across E3:E15 and F3:F15 respectively.

2 3 4 Invoice ID Units CHCL123 CHCL124 CHCL125 CHCL126 CHCL127 CHCL128 CHCL129 CHCL130 CHCL131 CHCL132 CHCL133 CHCL134 CHCL135 CHCL136 Price/ Unit Customer Status VIP Regular Regular VIP VIP Regular VIP VIP Regular VIP Regular Regular Regular New Total Price Shipping Cost 1011 $10.00 1853$10.00 1217$10.00 857$10.00 273 $10.00 $10.00 $10.00 $10.00 1205$10.00 $10.00 1230 939 133 221 1470 1094$10.00 1880 $10.00 1278$10.00