Excel formulas: IF function explained with Examples

      No Comments on Excel formulas: IF function explained with Examples

The ability to control the flow of your excel worksheet, letting it make decisions on what to do or function to execute, is very valuable to the advanced excel user. While using Excel, at certain times we need to check certain condition to instruct the worksheet to branch accordingly. These Excel IF examples will help you become familiar with how the Function works.

What is the IF function

It is a logical/conditional function used to check conditions and make decisions based on the results of the condition checked.

In simple terms:

IF function with Examples

Therefore, an IF function can have two results: the first result if the statement is true, and the second result if the statement is false.

Some Excel IF Examples

Study the following examples very carefully. It will not only help you understand the concept of IF function but will also provide a wider view on how and when to use it.

Example 1: Comparing two cells

This is a very simple example that tests if the values in cell A1 and B1 are equal. If they are equal, you’ll see the message [These numbers are equal] displayed in the cell. But if the values are not equal, you’ll see the message [These numbers are not equal].

=IF(A1=B1, “These numbers are equal”, “These numbers are not equal”)

Excel IF examples

Note: If you want your IF function to return results in the form of text, then you should surround the text with quotation marks. But numbers are different; you can just enter them without the quotations.

Play around with the following worksheet to understand the example better.

View this example in Excel Online

Example 2: Determine an employee commission with IF function

The following example is a commission calculator that shows the sales made by each employee and then, with the help of the IF function, determined their commissions based on their sales. If employee sales exceed $500, commission rate will be 10%. But if employee sales fall below $500, commission rate will be 5%.

The formula to calculate commission:

=IF(B2>500, B2*10%, B2*5%)

Examples of IF-function- commission calculator

Play around with the following worksheet to understand the example better.

View this example in Excel Online

Nested IF function

By far, the two examples above should do a great deal in teaching you what IF function is, when and how to use it. So now we are delving deed into IF function. By deep, I mean a more complicated way of working with the IF function, that’s the Nested IF.

The Nested IF statement executes two different functions depending on whether the expression is true or false. And this arises because sometimes a choice has to be made from more than 2 possibilities.

In simple terms, the nested IF statement means using one IF function inside another IF function. Consider studying the remaining examples.

Example 3: Identifying age group with IF function

=IF(B2<50, IF(B2>18, “Adult”, “Child”), “Old”)

What this formula actually does is very simple. It checks to see if cell B2 is less than 50. If cell B2 is really greater than 50, another function is called. But if cell B2 is not less than 50, the word [Old] will be displayed.

Examples of IF-function - Age group determination

Play around with the following worksheet to understand the example better.

View this example in Excel Online

Example 4: Student’s performance with IF function

=IF(B3>50, IF(B3>80, “Excellent”, “Verry Good”), IF(B3>40, “Good”, “Poor”))

This formula is also a nested IF function. What it does is that it checks the student’s marks and makes remarks on how they performed.

Student’s whose marks falls below 40 are the poor students.

Student’s whose marks falls between 40 and 50 are the good students.

Students who score between 50 and 80 are very good students.

And students who score above 80 are the excellent students

Play around with the following worksheet to understand the example better.

View this example in Excel Online

With these Excel IF examples, I hope that you understand what the IF function really means and how to exactly to use them. Try your hands on many examples to become familiar with it.

Leave a Reply

Your email address will not be published. Required fields are marked *