Excel Formulas

Invoice, Part 1: Free Shipping

Back to Tutorial
#### Lesson 20: Invoice, Part 1: Free Shipping

#### "Howdy! I heard that you're good with spreadsheets, so I thought I'd ask for a favor.

#### Our spreadsheet

#### How can we solve this problem?

#### Writing the function

#### "Perfect-o! Thanks for doing this!

/en/excelformulas/buying-a-printer-cost-comparison/content/

You probably know that we currently charge $5.99 for shipping on all of our orders. Well, we're going to start offering **free** **shipping** **if the subtotal of an order is at least $100**. Do you think you could update our invoice to calculate this automatically?"

This lesson is **part 1**** of 5** in a series. The other lessons in this unit will build on the same example and add various types of functionality. Before moving on, you'll want to make sure you are comfortable creating formulas. If you'd like more practice, you can work through some of the previous Excel Formulas lessons.

Once you've downloaded our spreadsheet, open the file in Excel or another spreadsheet application. It looks like we have an order invoice. There's information about the order, along with some formulas to calculate the tax, order subtotal, and total order cost.

If the subtotal for an order is at least $100, the shipping should be free. If not, it should cost $5.99. We could use the **IF** function to solve this problem. As you may remember from our lesson on Using the IF Function, we can use it to test a condition and then write something in a cell depending on the result. In this example, the IF function can tell us** whether the subtotal is at least $100 **and then set the shipping cost depending on the result.

- The first argument
**sets up the condition**the IF function will test: In this example, if the value in cell D6 is greater than or equal to 100. So our first argument will be**D6>=100**. - The second argument tells the function
**what to write**if the condition is**true**. In this example, if the value is greater than or equal to 100, we want it to write "0" in the cell. So our second argument will be**0**. - The third argument tells the function
**what to write**if the condition is**false**: In this example, if the value is less than 100, we want it to use the value from cell G3 (5.99). So our third argument will be**$G$3**(we'll use an absolute reference here just in case we move this formula in the future).

Here's our function: **=IF(D6>=100,0,$G$3)**. We'll enter this function into cell **D7**.

If you entered the function correctly, the shipping cost should change from **$5.99** to **$0.00**. That's because the **Order** **Subtotal** is more than $100.

If you want to test the function, change the value in cell **C2** from **2** to **1**. Because the Order Subtotal is now less than $100, the Shipping cost should change from $0.00 to $5.99.

OK—our function is working really well! We're ready to send this back!

This kind of automation is going to make everything a lot simpler. I'll be sure to send all of my complicated spreadsheet problems your way from now on."

If you'd like to continue on to the next part in this series, go to Invoice, Part 2: VLOOKUP.

/en/excelformulas/invoice-part-2-using-vlookup/content/