IF is just one of the many popular and useful features in Excel. You usage an IF declare to test a condition and to return one value if the condition is met, and also another worth if the problem is no met.
You are watching: In cell g2 create a formula that will place the word yes if cell f2 is less than 25 of h2
In this tutorial, we space going to learn the syntax and also common usages of Excel IF function, and also then will have a closer look in ~ formula examples that will hopefully prove valuable both to beginners and experienced users.Using the IF role in Excel - formula examples
Excel IF role - syntax and usage
The IF function is one of logical features that evaluates a details condition and returns the worth you point out if the problem is TRUE, and also another worth if the problem is FALSE.
The syntax for IF is together follows:
As friend see, the IF function has 3 arguments, however only the very first one is obligatory, the other two room optional.logical_test (required) - a value or reasonable expression that can be one of two people TRUE or FALSE. In this argument, you can specify a text value, date, number, or any kind of comparison operator.
For example, your logical test can be expressed as or B1="sold", B110.value_if_true (optional) - the value to return once the logical check evaluates to TRUE, i.e. If the condition is met.For example, the complying with formula will return the text "Good" if a worth in cell B1 is better than 10: =IF(B1>10, "Good")value_if_false (optional) - the value to be returned if the logical check evaluates come FALSE, i.e. If the condition is no met.For example, if you add "Bad" together the 3rd parameter to the over formula, it will return the message "Good" if a worth in cabinet B1 is greater than 10, otherwise, it will certainly return "Bad":=IF(B1>10, "Good", "Bad")
Though the last two parameters that the IF role are optional, your formula may create unexpected outcomes if girlfriend don"t recognize the basic logic in ~ the hood.If value_if_true is omitted
If the value_if_true dispute is omitted (i.e. There is only a comma adhering to logical_test), the IF function returns zero (0) once the problem is met. Right here is an instance of together a formula:
In case you don"t want your Excel IF statement come display any type of value as soon as the condition is met, enter dual quotes ("") in the 2nd parameter, choose this: =IF(B1>10, "", "Bad"). Technically, in this case the formula returns an empty string, which is invisible to the user yet perceivable to various other functions.
The adhering to screenshot demonstrates the over approaches in action, and also the second one appears to be an ext sensible:
If the logical check evaluates come FALSE and also the value_if_false parameter is skipping (there is just a closeup of the door bracket after the value_if_true argument), the IF role returns the logical value FALSE. It"s a little unexpected, isn"t it? below is an example of such a formula:
Putting a comma ~ the value_if_true discussion forces your IF formula come return 0, which doesn"t make lot sense either:
And again, the many reasonable strategy is to put "" in the 3rd argument, in this instance you will have empty cells as soon as the problem is no met:
=IF(B1>10, "Good", "")
=IF(B1>10, TRUE, FALSE)or=IF(B1>10, TRUE)
Note. For your IF statement come return TRUE and also FALSE together the logical values (Boolean values) that various other formulas deserve to recognize, make certain you don"t enclose lock in double quotes. A intuitive indication that a Boolean is middle align in a cell, together you watch in the screenshot above.
If you want "TRUE" and also "FALSE" to it is in usual text values, enclose castle in "double quotes". In this case, the changed values will be set left and also formatted as General. No Excel formula will acknowledge such "TRUE" and also "FALSE" message as logical values.
IF formula to perform a math operation and also return a result
Instead that returning specific values, you can acquire your IF formula to test the stated condition, carry out a matching math operation and return a value based upon the result. You do this by using arithmetic operators or other attributes in the value_if_true and /or value_if_false arguments. Below are simply a couple of formula examples:
Example 1: =IF(A1>B1, C3*10, C3*5)
The formula compare the values in cell A1 and B1, and also if A1 is better than B1, it multiplies the worth in cell C3 by 10, through 5 otherwise.
Example 2: =IF(A1B1, SUM(A1:D1), "")
The formula to compare the worths in cell A1 and also B1, and if A1 is not equal come B1, the formula return the sum of worths in cells A1:D1, an empty string otherwise.
Using the IF function in Excel - formula examplesNow the you are acquainted with the IF function"s syntax, let"s look at at some formula examples and learn just how to use it in real-life scenarios.
Excel IF statement because that numbers: higher than, much less than, same to
The use of the IF role with numeric worths is based on using different comparison operator to express her conditions. Girlfriend will find the complete list of logical operators illustrated with formula instances in the table below.
|Greater than||>||=IF(A2>5, "OK",)||If the number in cell A2 is higher than 5, the formula return "OK"; otherwise 0 is returned.|
|Less than||=IF(A2||If the number in cabinet A2 is much less than 5, the formula returns "OK"; an empty string otherwise.|
|Equal to||=||=IF(A2=5, "OK", "Wrong number")||If the number in cabinet A2 is equal to 5, the formula return "OK"; otherwise the role displays "Wrong number".|
|Not equal to||=IF(A25, "Wrong number", "OK")||If the number in cabinet A2 is no equal to 5, the formula returns "Wrong number "; otherwise - "OK".|
|Greater than or same to||>=||=IF(A2>=5, "OK", "Poor")||If the number in cabinet A2 is higher than or same to 5, the formula returns "OK"; otherwise - "Poor".|
|Less than or equal to||=IF(A2||If the number in cell A2 is less than or same to 5, the formula returns "OK"; an empty string otherwise.|
The screenshot below demonstrates the IF formula through the "Greater 보다 or same to" logical operator in action:
Excel IF instances for text values
Generally, you write an IF explain with message using one of two people "equal to" or "not equal to" operator, as demonstrated in a pair of IF instances that follow.Example 1. Case-insensitive IF formula for message values
Like the overwhelming majority of functions, IF is case-insensitive by default. What it method for girlfriend is the logical test for message values perform not recognize case in usual IF formulas.
For example, the following IF formula returns one of two people "Yes" or "No" based upon the "Delivery Status" (column C):
=IF(C2="delivered", "No", "Yes")
Translated right into plain English, the formula speak Excel come return "No" if a cell in pillar C contains the word "Delivered", otherwise return "Yes". In ~ that, it does no really matter just how you form the indigenous "Delivered" in the logical_test discussion - "delivered", "Delivered", or "DELIVERED". No one does it issue whether the word "Delivered" is in lowercase or uppercase in the source table, as portrayed in the screenshot below.
Another method to achieve exactly the same result is to use the "not same to" operator and also swap the value_if_true and value_if_false arguments:
=IF(C2"delivered", "Yes", "No")Example 2. Case-sensitive IF formula for message values
If you desire a case-sensitive reasonable test, use the IF duty in mix with specific that to compare two text strings and returns TRUE if the strings are exactly the same, otherwise it returns FALSE. The precise functions is case-sensitive, though it ignores formatting differences.
You usage IF with exact in this way:
=IF(EXACT(C2,"DELIVERED"), "No", "Yes")
Where C is the pillar to which your logical check applies and "DELIVERED" is the case-sensitive text value that demands to be suitable exactly.
Naturally, friend can also use a cell reference quite than a text value in the second argument of the precise function, if you desire to.
Note. as soon as using text values as parameters for her IF formulas, psychic to always enclose them in "double quotes".
Example 3. Excel IF with wildcard (partial match)
If you want to basic your problem on a partial match rather than specific match, an instant solution that comes to mind is using wildcard personalities (* or ?) in the logical_test argument. However, this basic and evident approach won"t work. Plenty of functions accept wildcards, but regrettably IF is not among them.
A equipment is to usage IF in mix with ISNUMBER and also SEARCH (case-insensitive) or find (case-sensitive) functions.
For example, if No action is required both for "Delivered" and "Out because that delivery" items, the following formula will occupational a treat:
=IF(ISNUMBER(SEARCH("deliv",C2)), "No", "Yes")
We"ve supplied the SEARCH function in the over formula because a case-insensitive enhance suits better for our data. If you desire a case-sensitive match, simply replace search with find in this way:
Excel IF formula instances for dates
At first sight, it may seem the IF recipe for days are identical to IF statements because that numeric and also text worths that we"ve simply discussed. Regrettably, it is no so.Example 1. IF recipe for dates with DATEVALUE function
As shown in the screenshot below, this IF formula evaluate the dates in shaft C and returns "Completed" if a video game was played prior to Nov-11. Otherwise, the formula return "Coming soon".
In case you basic your problem on the existing date, you have the right to use the TODAY() function in the logical_test discussion of her IF formula. Because that example:
Naturally, the Excel IF function can know more complicated logical tests, together demonstrated in the next example.Example 3. Progressed IF formulas for future and past dates
Suppose, you want to mark only the dates that happen in more than 30 days indigenous now. In this case, you can express the logical_test argument as A2-TODAY()>30. The finish IF formula may be together follows:
=IF(A2-TODAY()>30, "Future date", "")
To point out past days that occurred much more than 30 job ago, you can use the complying with IF formula:
=IF(TODAY()-A2>30, "Past date", "")
If you desire to have both indications in one column, girlfriend will should use a nested IF duty like this:
=IF(A2-TODAY()>30, "Future date", IF(TODAY()-A2>30, "Past date", ""))
Excel IF instances for blank, non-blank cells
If you want to somehow note your data based on a certain cell(s) being empty or no empty, you deserve to either:Use the Excel IF role in conjunction with ISBLANK, orUse the logical expressions ="" (equal come blank) or "" (not equal to blank).
The table listed below explains the difference between these 2 approaches and provides formula example.
|Logical test||Description||Formula Example|
|Blank cells||=""||Evaluates come TRUE if a stated cell is visually empty, including cells with zero size strings.|
Otherwise, evaluate to FALSE.
|=IF(A1="", 0, 1)|
Returns 0 if A1 is visually blank. Otherwise return 1.
If A1 has an north string, the formula return 0.
|ISBLANK()||Evaluates to TRUE is a specified cell contains absolutely nothing - no formula, no north string changed by some various other formula.|
Otherwise, evaluate to FALSE.
|=IF(ISBLANK(A1), 0, 1)|
Returns the results similar to the above formula however treats cells v zero size strings together non-blank cells.
That is, if A1 contains an north string, the formula returns 1.
|Non-blank cells||""||Evaluates to TRUE if a specified cell includes some data. Otherwise, evaluate to FALSE.|
Cells v zero size strings are taken into consideration blank.
|=IF(A1"", 1, 0)|
Returns 1 if A1 is non-blank; otherwise returns 0.
If A1 contains an empty string, the formula return 0.
|ISBLANK()=FALSE||Evaluates to TRUE if a specified cell is no empty. Otherwise, evaluates to FALSE.|
Cells through zero length strings are considered non-blank.
|=IF(ISBLANK(A1)=FALSE, 0, 1)|
Works the exact same as the above formula, yet returns 1 if A1 includes an empty string.
Suppose, you have actually a date in shaft C only if a corresponding game (column B) to be played. Then, you deserve to use either of the following IF recipe to mark completed games:
=IF($C2"", "Completed", "")
=IF(ISBLANK($C2)=FALSE, "Completed", "")
Since there space no zero-length strings in our table, both formulas will certainly return the same results:
Hopefully, the above examples have actually helped you recognize the basic logic that the IF function. In practice, however, friend would often want a single IF formula to check multiple conditions, and our next write-up will present you exactly how to tackle this task. In addition, us will also explore nested IF functions, range IF formulas, IFEFFOR and also IFNA functions and also more. Please remain tuned and also thank you because that reading!