In Part 2 (get Part 1 Here) of our IF Statement series, we are discussing simple IF statements. These are the basic formulas for returning a values based on your (the user’s) criteria. These functions can be used for a variety of purposes and the examples below explore some of the potential uses of IF statements.

A Definition of IF Statements

IF Statements are logical functions in Excel. When you define the criteria for the statement, you are essentially saying, IF the defined condition is true, then return this value, otherwise (if false) return this value.

The syntax of IF Statements is:

=IF(logical_test, [value_if_true],[value_if_false])

Uses of IF Statements

As you use Excel, you might come across various instances where IF statements will be useful. Below are two examples based on real-world issues where these functions come in handy.

Example 1: Making Purchase Decision Based on Margins

Let’s say you received the following spreadsheet:

Your boss is trying to determine if the company should purchase wholesale products from the new vendor. You have your wholesale price the company will pay and the expected retail price the company can charge. Your boss wants you to find the net gain and profit margin. For any product where the profit margin is 30% or greater, you need mark it “Purchase.” For any product with a profit margin less than 30%, you need to mark it “No”

So, here is your step-by-step process:

• Subtract the Wholesale amount from the Retail amount under the Net column
• Divide the Whole amount by the Net amount in the Margin column – change number to a percentage
• Highlight both cells and use the fill handle to drag the Net amount and Margin percentage all the way down.

Now, at this point, you could manually enter Purchase or No by eyeing up the margin percentage to see if it’s greater or equal to 30%. But you can save time and ensure accuracy by using an IF Statement.

Here is how you would do that:

• Under the Purchas column, enter your formula: =IF(E2>=30%, “Purchase”, “No”)

What this formula states is “If the value in column E is greater than (>) or equal to (=) 30%, then return the value Purchase. If not, return the value NO.

Once the formula is entered, you can again use the fill handle to drag it all the way down.

Example 2: Determining Commission Bonus Payout

You received the following spreadsheet:

Your job is find the commission rate of 5%. For any sale over $300, the salesperson receives the 5% sales commission bonus. Otherwise, they don’t get a bonus.

Step-by-step:

• In the Percent of Sale column (E), enter the following formula:  =d2*0.5
• This will yield the 5% sales amount
• Copy the formula down using the fill handle
• Under the Commission Payout column (F), enter your IF Statement:  =IF(D2>300, “E2”, “No Bonus”)

What this formula states is: If the sale amount in column D is greater than 300, then return the commission value in column E, else, state No Bonus

As you can see, there are a variety of uses for IF statements. It really depends on what you need to use Excel for. Coming next week: Nested IF Statements!

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now