Home » Excel » Write an IF Statement in Excel

Write an IF Statement in Excel

Navigate

The IF statement is one of the most used functions in Excel, Access, and various programming languages, allowing you to setup a logical comparison for your data and identify two possible outcomes.

At it’s simplest form, the IF statement can be broken down into:
If something is true, then do it, otherwise do something else

The formula itself is as simple as:
=IF( logic , true , false )
You will see this setup commonly referred to as an If Then Else statement

A real world example would be:
If it is raining outside, wear a raincoat, otherwise wear a t-shirt
=IF( raining , wear raincoat , wear t-shirt )

Few real world scenarios are as simple as the above raincoat / t-shirt choice. What if it’s snowing outside? You’d likely want to put something on other than a raincoat or t-shirt.

You can also combine IF statements together. This is called Nesting.

Let’s add in the option for a Heavy Coat by saying:
If it is raining outside , wear a raincoat, IF it is snowing outside, wear a Heavy Coat, otherwise wear a t-shirt
=IF( raining, wear raincoat, IF( snowing , wear heavy coat, wear t-shirt ))

 


IF in Excel…

Use the below file if you’d like to follow along
Starting File: ExcelIfThenElse

Let’s apply what we just learned to columns G and H in the attached spreadsheet.

IF 1: Column G
I want to identify every person that worked Full Time this week.
IF 2: Column H
I also want to take PTO into account if billed hours were not 40.

IF 1 Steps:

  • Type =IF( into cell G3
  • Type F3>=40 to make your logic statement and a , to end it
  • Type "Yes", to place what value you wish to see if F3 is greater than or equal to 40
  • Type "No" to place what value you wish to see if F3 is not greater than or equal to 40
  • Close your formula with a closing parenthesis )
  • Your G3 formula should look like:
    =IF(F3>=40,"Yes","No")
  • Fill this formula down
    Excel_if_then_1

IF 2 Steps (nested):

  • Type =IF( into cell H3
  • Type F3>=40 to make your logic statement and a , to end it
  • Type "Yes", to place what value you wish to see if F3 is greater than or equal to 40
  • Instead of typing "No" this time, we want to add in another IF statement for PTO hours
  • Type IF(E3>0, "Yes", "No"
  • This time, close your formula with two closing parenthesis )), one for each IF statement.
  • Your H3 formula should look like:
    =IF(F3>=40,"Yes",IF(E3+F3>=40,"Yes","No"))
  • Fill this formula down
    Excel_if_then_2

Review:

Your first IF statement flagged Doe,John and Gram,Lilly as not working a full time week. While this is true, Doe,John did not work full time because he was out part of the week on Paid Time Off.

If you are looking to identify which of your employees did not work a full time week, the second, nested IF statement, might be more accurate for you. It all depends on the metric you are trying to define.

It is also worth noting that if you changed all of the "Yes" to 1 and all of the "No" to 0, you could SUM the numbers together to figure out the number of people that did or did not work Full Time; very useful if you’re working with hundreds or thousands of employees.

Excel_if_then_3

Leave a Reply

%d bloggers like this: