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:
Good afternoon, i really appreciate this your IF-FUNCTION FORMULAR KEEP IT UP. THANKS
December 7th, 2009 at 9:26 amGOD BLESS U
how to use the if functions to calculate the grade and remark in excel
February 27th, 2010 at 9:33 amGood evening, i really appreciate this your if function formular but i want more example how to calculate grade and remarks in excel
February 27th, 2010 at 9:39 amTHANKS
May Allah Bless U.
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!!
July 28th, 2010 at 8:31 pmThank you Thank you!!!
August 30th, 2011 at 6:33 amYou 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!
Remarks=?
April 2nd, 2012 at 9:48 amTotal=55.00
Average=13.75
Total>50,remarks=bad
total>40,remarks=good
Else Remarks=v.good
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
February 12th, 2013 at 11:50 pmI 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”
February 12th, 2013 at 11:52 pmRealised a bug, replace ‘no’ with FALSE
February 12th, 2013 at 11:56 pmtama! 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
October 9th, 2013 at 6:33 amHi Need Help Please!
I am trying to create a YES NO column (Column N)
There is a column which has values such as -, 1mth,>2 mths etc up to 11 mths (Column P)
I can get the first one done so for example:-=IF(P5″>2 mths”, “NO”, “YES”)
But I need it to cover the rest of the months as well.
Can anyone help?
November 16th, 2015 at 10:58 amCan anyone help? Looking for a formula to add a number IF the number is greater than, 500. For example IF A1 is greater than 500 I want to add 125. Don’t use Excel much and this would help. Thanks!
December 15th, 2015 at 8:25 pm