Microsoft Excel IF Formula
The IF statement in Excel is a powerful logical function that allows you to make comparisons between values and make decisions based on the results. The IF statement takes three arguments: a logical test, a value if the test is true, and a value if the test is false. For example, you could use the IF statement to check if a number is greater than 10, and then return “Pass” if it is, or “Fail” if it is not.
So, it returns one value for a TRUE result and another one for FALSE.
For example, a formula like =IF(A1=“Yes”,1,0) will return 1 if the cell A1 has a value of “Yes”. Otherwise, it will return 0.
Another Example, a formula like =IF(A>=0, “Value is positive”, “Value is negative”) will return a text stating “Value is positive” if the cell A1 has a value of zero or greater than zero. Otherwise, it will return a text stating “Value is negative”.
Microsoft Excel IF Formula Syntax
|
1 |
=IF (logical_test, [value_if_true], [value_if_false]) |
- logical_test: A value or logical expression that can be evaluated as TRUE or FALSE.
- value_if_true: The value to return when logical_test evaluates to TRUE.
- value_if_false: The value to return when logical_test evaluates to FALSE.
Logical operators
When you are constructing a test with IF, you can use any of the following logical operators:
| Comparison Operator | Meaning | Example |
|---|---|---|
| = | Equal to | =IF(A1=1, TRUE, FALSE) |
| > | Greater than | =IF(A1>1, TRUE, FALSE) |
| >= | Greater than or equal to | =IF(A1>=1, TRUE, FALSE) |
| < | Less than | =IF(A1<1, TRUE, FALSE) |
| <= | Less than or equal to | =IF(A1<=1, TRUE, FALSE) |
| <> | Not equal to | =IF(A1<>1, TRUE, FALSE) |
Combining Microsoft Excel IF Formula with AND, OR, NOT Functions
The IF function can also be combined with the AND, OR, and the NOT functions to test multiple conditions.
- AND =IF(AND(Something is True, Something else is True), Value if True, Value if False)
- OR =IF(OR(Something is True, Something else is True), Value if True, Value if False)
- NOT =IF(NOT(Something is True), Value if True, Value if False)
Examples of combining the IF formula with AND, OR NOT functions
=IF(AND(A1>0,B1<10),TRUE, FALSE)
Above formula will return true only if A1 cell has value greater than 0 and less than 10. Otherwise, it will return false.
=IF(OR(A1>0,B1<10),TRUE, FALSE)
Above formula will return true if A1 cell has a value greater than 0 or it has a value less than 10. Otherwise, it will return false.
=IF(NOT(A1>0),TRUE, FALSE)
The above formula will return true only if A1 cell has a value less than 0. Otherwise, it will return false. The equivalent formula for this example is =IF(A1<0,TRUE, FALSE)
IFERROR Function
The IFERROR function returns a value you specify if a formula evaluates to an error. Otherwise, it returns the result of the formula. The syntax of IFERROR function is IFERROR(value, value_if_error) where value is required and represents the value, reference, or formula to check for an error. The second argument, value_if_error is also required and returns a value if an error found. The type of errors that are evaluated are: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.
Example:
The formula =IFERROR(C5/D5,0) checks for an error in the formula in the first argument (divide the value of C5 by the value of D5). If it finds no error, then it returns the results of the formula. Otherwise, it returns zero.
If you are interested in Microsoft Access, then the post MS Access If-Then-Else Statement is for you.
More information about IF Formula