Invoice, Part 1: Free Shipping

In this Excel formulas invoice interactive, help figure out free shipping if the subtotal is less than $100 for a particular order.

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

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

Bonus Section

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