IF Function Explained: How to Write an IF Statement Formula in Excel - Excel Campus
14

IF Function Explained: How to Write an IF Statement Formula in Excel

Bottom line: Learn how to write an IF formula in Excel using the IF function.

Skill level: Beginner

The IF Function in Excel - Traffic Light Example

Everything You Do Is An IF Statement

Everyday we do hundreds (if not thousands) of IF statements in our head. Every decision we make is an IF statement:

  • IF I eat breakfast, THEN I will have energy for work/play.
  • IF I do the laundry, THEN my wife will be happy.
  • IF I brush my teeth, THEN more people will talk to me.
  • IF I read the rest of this article, THEN I might learn something about Excel…

My favorite example is the old traffic light analogy.

 

  • IF the light is green, THEN go.
  • IF the light is red, THEN stop.
  • IF the light is yellow, THEN what???  We’ll talk about that a little later…

These IF statements are also referred to as logical tests.  We are testing for a condition (the color of the light), then taking an action based on whether the condition is TRUE or FALSE.

Writing your first IF formula in Excel is an exciting moment.  It gives you the feeling of how you can program Excel to make it interactive.

Logical Tests – You Don’t Always Need the IF Function

Before you write your first IF statement (formula), it’s best to get an understanding of how Excel makes comparisons.

Type your first name in cell A1 and your last name in cell B1.  Then type the following formula in cell C1:

=A1=B1

If your first name and last name are different, then the result will be FALSE.

Compare Two Cells in Excel with Equal Sign Comparison Operator

Now type the number 2 in cell A2 and B2.  Then type the following formula in cell C2:

=A2=B2

The result will be TRUE.

Compare Two Numbers in Excel with Equal Sign Comparison Operator

We are using the equal sign “=” to compare the two values.  The result will always be TRUE or FALSE (unless one of the cells contains an error).

This also works with the greater than “>” and less than “<” symbols.  These are called comparison operators.

Comparison Operators in Excel

Operator  Description
=Equal To
<>Not Equal To
>Greater Than
<Less Than
>=Greater Than or Equal To
<=Less Than or Equal To

Using the comparison operators in a formula is a very EASY way to compare two values in Excel.  It’s a great way to compare lists of names, text, dates, or numbers.

It’s also important to note that the case doesn’t matter when comparing text.  The following formula will return TRUE.

=”Excel Campus” = “excel campus”

The IF Function Explained

What if you want to return a result other than TRUE or FALSE?

For this we can use Excel’s IF function.

=IF(logical_test, value_if_true, [value_if_false])

The IF function is pretty simple, and contains the following three arguments.

  1. Logical Test
  2. Value If True
  3. Value If False (optional)

Excel IF Function Explained

For the 1st argument, Logical Test, we can use the same formula we used above to compare two values.

If the logical test evaluates to TRUE, then the result of the IF function will return whatever you put in the 2nd argument (Value If True).

The Value If True argument can contain just about anything:

  • A number
  • Text wrapped in double quotes – “hello”
  • A reference to another cell – A2
  • A formula with another IF function.
  • A formula with any other combination of functions.

Excel IF Function Value If True Argument Examples

The 3rd argument, Value If False, will be returned if the Logical Test (1st argument) evaluates to FALSE.  It can hold any of the same items that the Value if True argument can hold.

The 3rd argument is Optional.  This means that you don’t have to put anything in the formula for it.  You can just close the parenthesis after the 2nd argument.  If the logical test is false then the formula result will display FALSE in the cell.

Nested IF Formulas – Yellow Light!!!

So we can see how to program our decision for the green light and red light, but what if the light is yellow???

This is where we can get in trouble in life (and Excel).

You have a decision to make, and you have to make it fast!  Your brain does a series of logical tests within a second or two.

Yellow Light - Multiple IF Statements in Excel

The first test is probably, “Do I have enough distance to stop?”  Then, “Are there any cops around?”

Then it’s something like, “If I stop I definitely won’t risk anything.  Plus, I could check Facebook, send that text, or see if there are any new blog posts on Excel Campus…”

“If I go, then I’ll have a chance to finally get to work on time…”

It’s never an easy decision.  But more importantly, how would we write a formula for this decision?

One way is to use a bunch of IF functions in the same formula.  This is called a nested IF formula.  As you can see in the image below, the formula can get long and ugly.

Nested IF Formulas In Excel

The nested IF formula also becomes difficult to read and interpret.

Fortunately, there are lots of alternatives to nested IF formulas in Excel.  We can use:

  • Lookup functions like VLOOKUP, INDEX, MATCH
  • Logical functions like CHOOSE, AND, OR, NOT
  • Math functions like SUMIFS, COUNTIFS, AGGREGATE

Each of these alternatives require an explanation, and I will write separate articles for those.  They can be much more efficient to read, write, and calculate.

Check out my free video training series on the lookup functions to learn more.

Programming Excel with the IF Function

Writing IF formulas gives us a powerful feeling with Excel.  You start to see how you can use it to display results based on criteria or user inputs.  This can make your financial models, charts, and dashboards interactive.

You are basically programming Excel with formulas to build spreadsheet applications.  If you are interested in programming and automating Excel even further, checkout my free video training series on Macros and VBA.

Please leave a comment below with any questions.  Thanks and have a great day!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 14 comments
Benny - June 30, 2017

Hi Jon
I am trying to figure this one out, so I can write a formula.
If the field C1 contains the value between 1 an 4, return “Peasant, else if C1 is between 5 and 9, return “Squire(ss), else if C1 is between 10 and 24, return “Sir/Dame”, else if C1 is between 25 and 49…. and so on, up to 10, that says if C1 is >999, return “Prince”.
How can I make this?

Reply
    J - July 19, 2017

    The best way to do this (in my opinion) is with a vLookup formula. Create a table with the lower edge of the range and its matching item:

    1 Peasant
    5 Squire(ss)
    10 Sir/Dame
    etc.

    Format it as a table and name the table something like tblTitles.

    Then use =vlookup(C1,tblTitles,2) as your formula.

    (This will look up the value of C1 in tblTitles and return the value from column 2 of that table.)

    Reply
Tanya - April 13, 2017

I am trying to write a formula for the following:

If J28 = 3% and is less than 5% the multiply J22 by 1.25; if not the “N/A”

this is the formula I used, and J28 = 3.9%, however, N/A is the result:

=IF((AND(J28=3%,J28<5%)),J22*1.25,"N/A")

Please help me! Thanks

Reply
    Mohsen - May 13, 2017

    Hi Tanya,

    You can change formula to this:
    =IF(5%>J28>=3%,J22*1.25,”N/A”)
    OR
    =IF(0.05>J28>=0.03,J22*1.25,”N/A”)

    Reply
Roger - March 8, 2017

I need a formula where if the value of a cell is greater than/equal to the value of a second cell the answer is “above threshold”. And vice versa for “below threshold”.

Reply
    Jon Acampora - March 13, 2017

    Hi Roger,
    The following should work. The formula compares the value is cell A2 to see if it is greater than or equal to the value in cell B2.
    =IF(A2>=B2,”above threshold”,”below threshold”)

    Reply
Zion - October 5, 2016

I think this question may relate to this?

How about if one column is greater than another for example if F3>G3 Copy the entire row A3 thru G3 and paste to sheet 2?

Reply
Niall - August 29, 2016

The is the best explanation I found.

Can I ask a question on an expansion on this??

I have this working great when used to search a single column but I would like to know how to make this work if A1 had to match a within a range.

What I use is:
=IF($B$3=’External Audit’!$F$1,IF(F4=’External Audit’!A3,IF(‘External Audit’!F3=”EA”,”*”,””)))

What I would like to do is if A1 is September, It can search the headings for September, then match the row with what I have in F4, and get a value * or – returned if the cell contains EA.

Thanks
Niall

Reply
just started - February 18, 2016

great work man ,,, thank you so much, it is so useful

Reply
Amit - January 21, 2016

Very very interesting example.

Reply
Debjit - June 28, 2015

great

Reply
Adam - June 23, 2015

Neat explanation.
With many examples and a touch of humor

Reply

Leave a Reply:

Grab My Free Cheat Sheet!

VLOOKUP Essentials Guide​

Vlookup Essentials Guide 317x407
Plus: Free Video Training Series on the Lookup Formulas!
x