How to use IF() and Nested IF() Functions in Excel

The IF() function in Excel is used to make logical comparisons. You can use this in-built function in your worksheet and its one of most popular functions in Excel. Here in this post, I’ll show you few examples on how you can apply or use the IF() function as formula on text, numbers etc., how to use nested IF’s in a cell and finally how to use other in-build functions like TODAY() and DATEVALUE() with IF function.

The IF() Syntax

IF(logical_test, [value_if_true], [value_if_false] )

The IF() function takes three parameters.

logical_test: The first parameter is the logical_test or in simple words, it means, If something is true. Its a condition, for example …

IF(A5>=5000, …

value_if_true: This is the second parameter, which says, do this if the logical_test is true.

value_if_false: The third parameter will execute if the logical_test returns false.

Let’s see few Examples to understand how this function works. The first example show the basic usage of IF() function and second example shows how to apply nested IF() function in a Cell.

Basic IF() Function Example

I have two columns, Amount and Status. In the first column, I would enter some numeric values as Amount. The second column Status will have a formula that would check the amount in the first column and according show a message. I’ll use the IF() function to check if the Amount entered meets a condition, that is, if the amount >= 5000, then show a message Paid in Full. Else, the message shows a status as Pending.

I have five rows of data. In the second column I have applied a formula using the IF() function. The function checks the value in the first column of each row.

=IF(A2>=5000,"Paid in Full","Pending")

Simply, write the above condition in the second column of the second row and later drag the second column down until the last row (with a value).

Nested IF() Function Example

Now, there will be situations, when you will need multiple conditions. I’ll extent the above example and add another condition.

If my users have paid 50% percent (or half) the amount, then it should notify me with another message, which says Paid Half.

=IF(A2>=5000,"Paid in Full",IF(A2>=2500,"Paid Half","Pending"))

I have added a second IF() method as the False value for the first method. In the above example, the first two rows shows the status as Paid Half as it falls under the second IF() condition.

1) You do not have to start the second IF() function with an equal to (=) sign.
2) In Excel 2007 and above, you can add up to 64 IF() methods.

Using IF() Function with Strings and Other Functions

You can concatenate string values inside the IF() function. Also, you can add other functions inside the IF() as conditions.

For example, I want to write a formula that will wish Happy Birthday to my user after checking today’s Date, with a given Birth date. I have the name of the user and birth date in two different cells. Here’s what I’ll do.

=IF(TODAY() = DATEVALUE(B2),"Happy Birthday " & A2,"")

I am using the TODAY() function to check the date in the cell B2. If the condition is true, that is, if today’s date is equal to the date in the second cell, wish the user, Happy Birthday. I have concatenated the string with value in the first cell.

I have not set any value as the false condition. You may add anything you wish.

Previous - Highlight an Entire Row in Excel Based on a Cell Value using VBANext - Check if a Cell in an Excel Worksheet has numbers using VBA

Related Posts:

Join our Google Plus Community and be a part of a discussion!