Calculate shipping costs based on order amount and shipping destination
Determining shipping costs based on order amount and destination can be managed efficiently using Excel's IF and Nested IF functions. This article will show you how to use these functions to calculate shipping costs and automate the process.
Example
Here's an example table showing order amounts, shipping destinations, and their respective shipping costs:
| Order Amount | Destination | Shipping Cost |
|---|---|---|
| $50 | Domestic | $5 |
| $150 | Domestic | Free |
| $75 | International | $20 |
| $250 | International | $15 |
| $100 | Domestic | $5 |
Exercise
Input different order amounts and destinations in the table and use the following functions to calculate the shipping costs based on these criteria:
- Domestic shipping costs $5 for orders below $100 and is free for orders $100 or above.
- International shipping costs $20 for orders below $200 and $15 for orders $200 or above.
Solution
When you input the order amounts and destinations into the Excel sheet, the formula in the "Shipping Cost" column will automatically calculate the shipping cost based on the specified criteria:
The nested IF function in the "Shipping Cost" column evaluates both the order amount and destination. The formula used is:
=IF(AND(A2>=100, B2="Domestic"), "Free", IF(B2="Domestic", 5, IF(AND(A2>=200, B2="International"), 15, 20)))This formula checks the following conditions:
- If the order amount in cell A2 is greater than or equal to $100 and the destination is "Domestic", the shipping cost is "Free".
- If the destination is "Domestic" and the order amount is below $100, the shipping cost is $5.
- If the order amount is greater than or equal to $200 and the destination is "International", the shipping cost is $15.
- For all other "International" orders below $200, the shipping cost is $20.