IF Formula Excel – How to use Excel IF Function




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

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)

if-formula-example

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

if-formula-in-excel

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

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

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=""> <strike> <strong>


Comments and Trackbacks

  1. mustapha k wrote:

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

  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
    THANKS
    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!

  6. Fahad wrote:

    Remarks=?
    Total=55.00
    Average=13.75
    Total>50,remarks=bad
    total>40,remarks=good
    Else Remarks=v.good

  7. Mon wrote:

    Thank you, this was the most helpful site I found for what I was looking for
    I was able to create this formula for excel 2010 thanks to your post:
    =IF(A2=D2,TRUE, IF(B2=D2,TRUE, IF(C2=D2,TRUE,no)))

    Thank you so much

  8. Mon wrote:

    I should say I used the formula to compare if three different variables in the same row all exactly equalled a forth variable(D2)

    =IF(A2=D2,TRUE, IF(B2=D2,TRUE, IF(C2=D2,TRUE,no)))

    If there is even one variable that does not match D2, the answer will come up as “no”

  9. Mon wrote:

    Realised a bug, replace ‘no’ with FALSE

  10. Shaenavil wrote:

    tama! tama!

    thanks for the answer but I’m searching for the foemula of remark that shows if it is passed or failed and I want more examples…

    :) thanks

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel