IF Formula Excel – How to use Excel IF Function

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 is the condition that we would like to evaluate for being TRUE or FALSE.


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 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:

download example of IF function

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. mustapha k wrote:

    Good afternoon, i really appreciate this your IF-FUNCTION FORMULAR KEEP IT UP. THANKS

  2. Muhammad Shehu wrote:

    how to use the if functions to calculate the grade and remark in excel

  3. Muhammad Shehu wrote:

    Good evening, i really appreciate this your if function formular but i want more example how to calculate grade and remarks in excel
    May Allah Bless U.

  4. Mumzy101 wrote:

    This was absolutely terrific. Gave me the exact information I needed in terms I could understand to produce the information that was required. Unlike the unhelpful, non user friendly ‘Help’ function in Excel itself!!

  5. DN wrote:

    Thank you Thank you!!!
    You simplified the first steps to get me started correctly.
    I found the 3 sections (criteria, True, False) confusing until I read your article and it explained what I was needing to accomplish.
    Naturally, I still had to figure out each part of the formula, but to know exactly what I needed to show in each section was such a help!!!
    I am saving this! Keep up the good work!


Chinese (Simplified)DutchEnglishFilipinoFrenchGermanHebrewHindiItalianJapanesePortugueseRussianSpanish

treeemap software for excel