Excel if function tutorial

With the Excel function IF you can check if some condition is true / false and do different things depending on the outcome.  It is fairly simple to use with these step by step instructions i have made out for you. If you have any questions on how to use the if function, leave a comment below.

Example on how to use the IF function


Let's test if the values in the first column in the table below are greater or equal to 100 and write an answer next to each cell according the outcome. First make a following table to your Excel sheet:

Excel if function tutorial


1. Click on the first cell under heading "True or false?" Next Click the fx button under the ribbon and search for IF function. Double click on IF in the search results list.

Excel if function tutorial

Excel if function tutorial

2. In the opening dialogue: in Logical test argument input field you have to set up a simple true / false test. Click on the first value cell and type >= 100. This tests if the value in the cell is greater than or equal to 100. (Example: A2>=100).

Write "value is greater than or equal to 100" to the Value if true field.

Write "value is less than 100" to the Value if false field.

Press OK and drag copy the values down to fill the table.

Excel if function tutorial


You should see following results:

Excel if function tutorial

You can also write the function manually, the syntax is (note: depending on your regional settings, use comma (,) instead of semicolon (;) as separator):

=IF(LOGICAL TEST;VALUE IF TRUE;VALUE IF FALSE)

In our example the function syntax would have been:

=IF(A2>=100;"value is less than 100";"value is greater than or equal to 100")

Multiple IF functions - chaining IF functions

 

With Excel's IF function you can test only one condition at a timeYou can use a chain of multiple IF functions to test more conditions. 

Remember the syntax of the IF function (note: depending on your regional settings, use comma (,) instead of semicolon (;) as separator):

=IF(LOGICAL TEST;VALUE IF TRUE;VALUE IF FALSE)

When we need to test multiple conditions, we start each new IF function in the Value if false part of the previous IF function. Like this (marked with red):

=IF(LOGICAL TEST ONE;VALUE IF ONE IS TRUE;IF(LOGICAL TEST TWO;VALUE IF TWO IS TRUE;VALUE IF BOTH TESTS ARE FALSE))

You can chain up multiple IF functions, if you just remember to start every new IF function in the value if false part of previous IF function.

=IF(LOGICAL TEST ONE;VALUE IF ONE IS TRUE;IF(LOGICAL TEST TWO;VALUE IF TWO IS TRUE;VALUE IF BOTH TESTS ARE FALSE))

Example 2: chaining up multiple IF functions

Let's make two IF functions that test:
  1. if the value in the first column is greater than 50 and write "over 50" to the next column
  2. if the value in the first column is greater than 100 and write "over 100" to the next column
  3. if both arguments are false, just write false to the next column

Value
Over 50 / Over 100 / False
80
140
50
200
99



The key thing to remember here is that when any of the logical arguments is true, Excel stops running the functions and prints out the value if true of that function.

So if you start testing if the value is greater than 50 Excel will never get to test if it is greater than 100, because the first logical test was true. So here is the right syntax:


=IF(A2>100;"over 100";IF(A2>50;"over 50";"false"))


Multiple conditions with AND function


With Excel's IF function you can test only one condition at a time. Use the AND function to test multiple conditions inside one IF function. Here are instructions on how to use AND function with IF function.

Remember the syntax of IF function (note: depending on your regional settings, use comma (,) instead of semicolon (;) as separator):

=IF(LOGICAL TEST;VALUE IF TRUE;VALUE IF FALSE)

The syntax for AND function is :

=AND(CONDITION A;CONDITION B; CONDITION C;....)

AND function results to TRUE only if all conditions are true.


Example 3: Excel IF function with AND function



Look at the table below and copy it to your Excel sheet, starting from cell A1.

Let's test if value 1 is greater than 100 and value 2 is YES. Let's write TRUE in the third column only if both conditions are true.



Value 1 Value 2 True or false?
100 YES
30 YES
50 NO
30 YES
230 NO


  1. Click on the first cell under heading "True or false?
  2. Write =IF(AND(A2>100;B2="YES");"TRUE";"FALSE") and press enter.
  3. Drag copy the function down rest of the cells.
  4. You should see following results:

Value 1
Value 2
True or false?
100
YES
TRUE
30
YES
FALSE
50
NO
FALSE
230
YES
TRUE
230
NO
FALSE

Let's see that function again:

=IF(AND(A2>100;B2="YES");"TRUE";"FALSE")

Here marked with red, you can see the AND function inside the Logical test argument. After the AND function follows Value if true and Value if false accordingly.

2 comments:

  1. Try not to become a man of success, but rather try to become a man of value. See the link below for more info.


    #value
    www.ufgop.org

    ReplyDelete
  2. Good day! I was so impressed to see a very well-presented dishes that is absolutely tasty
    and delicious. You've done a great job.Well, thank you for sharing your talent and article
    it is very well appreciated. You can also visit my site if you have time.

    n8fan.net

    www.n8fan.net

    ReplyDelete