IF formula in Excel checks whether a given condition evaluates TRUE or FALSE.

Syntax of IF Formula
Example of IF Formula
Possible Errors returned by the IF Formula

IF Formula Syntax

IF Formula has three parts:

IF(criteria, value_when_true, value_when_false)

criteria

criteria is the condition that we would like to evaluate for being TRUE or FALSE.

value_when_true

value_when_true is the value that will be returned by the function when the above criteria evaluates to TRUE. This can be a number (Ex. 15) , string (Ex. “Yes”) or a combined expression (Ex. “Pass Level “&10).

value_when_false

value_when_false is the value that will be returned returned by the function when the above criteria evaluates to FALSE. This can be a number, string or a combined expression. Interestingly, this parameter is optional and if not specified, the IF formula will use the Boolean value of FALSE instead. So if you were to write =if(1>2,”Yes”), leaving the third parameter blank, the result will be FALSE.

Example of a IF Formula

Using IF to find if sum if greater than a particular value or not

Let’s look at an example of the IF formula. Suppose we wanted to find out whether the sum of Sales figures in the above data set was greater than 4000 or not. You could write =IF(SUM(D2:D10)>4000,”Yes”,”No”) and if the sales were greater than 4000, the result will be “Yes” otherwise “No”. Notice that we have used “Yes” or “No” as the return parameters depending on whether the test condition evaluates to TRUE or FALSE. We could have used other string such as “Greater than 4000″ and “Less than 4000″ or even combined expressions such as “Greater than ” & 4000 and “Less than ” & 4000 as well.

Using the IF formula with AND and OR operators to specify multiple conditions

Let’s take an example where we wanted to check if a particular value in a cell is lies between two numbers (say 100 and 200). We could simply write =IF(AND(D2>100,D2<200),”Between 100 and 200″, “Not between 100 and 200″). What we have done is to use the AND operator to combine two conditions so that the first condition is returned only when both are true.

Similarly if we wanted to know if the value in a particular cell was beyond the upper limit or lower than the minimum permissible (to flag it off as an exception in both the cases), we could’ve written =IF(OR(D2>1000,D2< =0),”Exception”, “Normal”). In this case the OR operator checks both the conditions and even if the one of the conditions evaluates to TRUE (either sales greater than 1000 or equal to 0), returns second parameter as the output.

Let’s take another example. Suppose you wanted to check if sales today were less than 100. You could write =IF(AND(C2=TODAY(),D2<100),”Today sales were less than 100″,”"). Notice the use of the AND operator where we have used two conditions. Also see the use of null (“”) as the value to return if the condition were to be false. This way you can ensure that the message appears only when the condition is met, otherwise the cells remain blank (and clean).

Maximum number of times nested IF functions can be used in a cell

In Excel 2003 you can nest the IF formula only upto 8 times in a cell (Ex. =IF(2<1,”TRUE”, IF(2<1,”TRUE”, IF(2<1,”TRUE”, IF(2<1,”TRUE”, IF(2<1,”TRUE”, IF(2<1,”TRUE”, IF(2<1,”TRUE”, IF(2<1,”TRUE”,”NO”)))))))) ). Any more IF formulas nested after eight will start throwing an error.

How to enter the IF formula in an Excel Sheet

1. Select the cell in which you want to place the formula

2. Type the formula as =IF(

3. The first parameter in an IF function is the condition that we are trying to evaluate. (Let’s take the case of comparing a single cell value.) Using the keyboard up-down and left-right arrow key, move the cursor to the cell whose value we would like to evaluate.

4. Press the comparison operator > (greater than), < (less than), equal to (=) or a combination (>=, < = etc). (Just keep in mind that the = sign has to be towards the right of the other symbols used)

5. Enter than value you would like to compare the value in the cell with. This can be a string (Ex. “Highest”) or a number (Ex. 15).

6. Press the comma key (,)

7. Then enter the value you would like to return if the above expression were to evaluate to TRUE.

8. Press the comma key (,) again.

9. Now enter the value you would like to return if the above expression were to evaluate to FALSE.

10. Close the formula by entering the closing bracket ).

Check out the clip above for knowing if the values you’ve entered are in the same order. In the end your formula should look something like this =IF(D2>=100, “Met Goal”, “Did not meet goal”)

Possible Errors with the IF Formula

The IF formula can result in the following error values:

IF #NAME? Error

The #NAME? Error value in IF formula can occur when you incorrectly specify the criteria in the first parameter. Suppose you mistyped A2 as AAA2 in the formula (Ex. =IF(AAA2<100) ) you will get a #NAME? error. (The cell AAA2 is not located in the worksheet). Also if you had typed the formula as =IF(SUMM(D2:D10)>4000,”Yes”,”No”), the #NAME? error value would have been returned because SUMM does not exists as a function in Excel.

You can download an example of IF formula here or click on the button below: