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

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.

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.

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!

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/