Excel Formulas: Invoice, Part 1: Free Shipping

Lesson 20: Invoice, Part 1: Free Shipping

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

illustration of character

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

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.

Our spreadsheet

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.

screenshot of excel 2013

How can we solve this problem?

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.

Writing the function

  • 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.

screenshot of excel 2013

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.

screenshot of excel 2013

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

illustration of character

"Perfect-o! Thanks for doing this!

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/