Learn to use IF formula in Excel in simple ways for you to use whenever you need.
Microsoft Excel is one of the most widely used spreadsheet program available today. It is a strong tool that can be used for a wide range of tasks, from simple ones like text wrapping to more complex ones like concatenation in Excel.
The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For instance, to “pass” scores above 40: =IF(A1>40,”Pass”,”Fail”).
You can test more than one condition by nesting IF functions and it can be combined with logical functions like OR and AND to extend the logical test.
Purpose
Test for a specific condition
Return value
The values you supply for TRUE or FALSE
Syntax
=IF (logical_test, [value_if_true], [value_if_false])
Arguments
- logical_test – A value or logical expression that can be evaluated as TRUE or FALSE.
- value_if_true – [optional] The value to return when logical_test evaluates to TRUE.
- value_if_false – [optional] The value to return when logical_test evaluates to FALSE.
Table of Contents
How To Use If Formula In Excel
Usage notes
The IF function is used to run a logical test and reacts differently depending on whether the result is TRUE or FALSE. The first argument, logical_test, is an expression that returns either TRUE or FALSE. Both value_if_true and value_if_false are optional, but one of them must be provided. The result from IF can be a value, a cell reference, or even another formula.
In the example shown above, we want to assign either “Pass” or “Fail” based on a test score. A passing score is 40 or higher. The formula in D6, copied down, is:
=IF(C6>=40,”Pass”,”Fail”)
Translation: If the value in C6 is greater than or equal to 40, return “Pass”. Otherwise, return “Fail”.
The logical flow of this formula is reversed. The formula below returns the same result:
=IF(C6<40,”Fail”,”Pass”)
Translation: If the value in C6 is less than 40, return “Fail”. Otherwise, return “Pass”.
Both formulas above, when copied down, will return correct results.
Another formula
The IF function can return another formula as a result. For example, the formula below will return A1*5% when A1 is less than 100, and A1*7% when A1 is greater than or equal to 100:
=IF(A1<100,A1*5%,A1*7%)
Also Read
How to apply Round Off Formula in Excel
How to remove Scroll Lock in Excel
Nested IF statements
The IF function can be “nested”. A “nested IF” refers to a formula where at least one IF function is nested inside another in order to test for more conditions and return more possible results. Each IF statement needs to be carefully “nested” inside another so that the logic is correct.
For example, the following formula can be used to assign a grade rather than a pass / fail result:
=IF(C6<70,”F”,IF(C6<75,”D”,IF(C6<85,”C”,IF(C6<95,”B”,”A”))))
Up to 64 IF functions can be nested. However, in general, you should consider other functions, like VLOOKUP function or HLOOKUP function for more complex scenarios, because they can handle more conditions in a much more streamlined fashion.
IF with AND, OR, NOT
The IF function can be combined with the AND function and the OR function. For example, to return “OK” when A1 is between 7 and 10, you can use a formula like this:
=IF(AND(A1>7,A1<10),”OK”,””)
Translation: if A1 is greater than 7 and less than 10, return “OK”. Otherwise, return nothing (“”).
To return B1+10 when A1 is “red” or “blue” you can use the OR function like this:
=IF(OR(A1=”red”,A1=”blue”),B1+10,B1)
Translation: if A1 is red or blue, return B1+10, otherwise return B1.
=IF(NOT(A1=”red”),B1+10,B1)
Translation: if A1 is NOT red, return B1+10, otherwise return B1.
CONCLUSION
To summarize, the IF function is one of the most commonly used functions in Excel, allowing you to make logical comparisons between a value and what you expect. As a result, an IF statement can have two outcomes. If your comparison is True, the first result is True; otherwise, the second result is False.
The IF() function is one of Excel’s most useful and versatile functions. There are numerous applications for it after you comprehend how it functions.
It is also an indispensable tool for Microsoft Excel developers due to its flexibility to be nested and to be utilized with other worksheet functions. Apart from this, MS Excel is most typically used for doing basic calculations, creating pivot tables, and enabling macros.
EasySheet is a tech blog that features amazing Excel hacks, tips, and tricks. Our mission is to help beginners learn Excel and get answers to all of their questions using the EasySheet.
We are the best place to go for anyone who needs assistance with Microsoft Office products such as Excel, Word, PowerPoint, Publisher, and more!
