Learn Excel Now

Conquer the Fear of Excel

  • Home
  • Training
    • Excel
    • MS Office
    • Outlook
    • PowerPoint
    • Word
    • OneDrive
    • Google
  • All-Access
  • eLearning
    • Excel Foundations
    • Micro Courses
      • Simply Excel
  • Resources
  • Login

Excel IF Statements Part 2 – Intro to Simple IF Function

August 3, 2016 by Tyrone Pernsley

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:

IF Statement Example 1

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.

IF Statements GIF 1

Example 2: Determining Commission Bonus Payout

You received the following spreadsheet:

IF Statements Example 2

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

IF Statements GIF 2

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

All Access Subscription
Our 1-year all-access subscription provides step-by-step guidance for mastering Microsoft Office applications, with workshops, exercises and quick reference eGuides. It's perfect for anyone that needs to brush up on certain skills or even folks who need to learn the programs - from a beginner’s level.
Excel Foundations
Take the fear out of Excel with this 20 module self-paced training course. You will cover the most essential topics to develop a solid foundation of Excel or a lifetime of mastery.

Learn Excel Now helps you conquer the fear of Excel. By providing self-paced and instructor-led training and free strategies and guides, we leave no Excel mystery unsolved.

  • Home
  • Training
  • All-Access
  • Excel Foundations
  • Resources

Connect with Us

  • Facebook
  • Linkedin

Contact

Learn Excel Now
questions@learnexcelnow.com
1-484-259-7664 or 1-800-964-6033
660 American Ave
Suite 203
King of Prussia, PA 19406

Microsoft® Office Excel® is a registered trademark of the Microsoft Corporation in the United States and other countries. All rights reserved.

Copyright © 2023 ­Learn Excel Now

Terms of Service

Privacy Policy

Copyright © 2023 · News Pro Theme on Genesis Framework · WordPress · Log in

WELCOME BACK!

Enter your username and password below to log in

Forget Your Username or Password?

Reset Password

Lost your password? Please enter your username or email address. You will receive a link to create a new password via email.

Log In