Return different TRUE and FALSE results from logical tests

=IF(logical_test,[value_if_true],[value_if_false])

---

Return different TRUE and FALSE results from logical tests. For example, =IF(C2>=75%,"Pass","Fail") means if cell C2 contains 75% or greater, return a "Pass" otherwise return a "Fail".

Logical tests can be used on text and numbers and to evaluate errors. A formula can contain many logical tests by nesting up to 64 different IF statements.

Logical tests can return text, numbers, cell references or other formulas and calculations.

IF syntax requires at least 1 but better 3 arguments: the logical test to be applied, the value to return if TRUE and the value to return if FALSE.

---

IF examples.

In the examples below, the IF function is used to test text, numbers and cell references.

---

Syntax

IF(logical_test,[value_if_true],[value_if_false])

The IF function consists of at least 1 argument, better 3:

1. logical_test. Required. A value or expression that returns a TRUE or FALSE response.
2. [value_if_true]. Optional. The value to return when the logical test returns TRUE.
3. [value_if_false]. Optional. The value to return when the logical test returns FALSE.

---

Try it now!

• Enter your formulas into the grey cells.

---

Scope

IF can be used to test logical conditions to return different values.

Text:	=IF(C2="Pass","Well done","Better luck next time") || result is "Well done" if cell C2 contains "Pass", otherwise "Better luck next time"
Numbers:	=IF(SUM(C2:C3)>=100,">= "&100,"<" & 100) || result is concatenation of >=100 if the sum of cell range C2:C3 is greater than or equal to 100, otherwise result is concatenation of <100.
Dates:	=IF(AND(2/1/2022>1/1/2022,TODAY()<"31-Dec-2022"),TRUE,FALSE) || result is TRUE as both AND conditions are TRUE (assuming TODAY() is 9th February 2022). Note numerical, textual and functional dates can be used.
Formulas:	=IF(C2>=100000,C2*0.01,0) || result is 1000 if cell C2 is greater than or equal to 100,000 or 0 if not.
Cell references:	=IF(C2>=75%,A1,B1) || result is the contents of cell A1 if the logical test returns TRUE, otherwise the contents of cell B1.
Error handling:	=IF(ISERROR(C2),"Error","No Error") || result is "Error" if cell C2 contains an error, otherwise "No Error".

Caveats

• A [value_if_false] does not need to be supplied. Excel will return a FALSE statement if the logical test requires. It is best though to add both a [value_if_true] and [value_if_false] argument or TRUE / FALSE statements.
• Text used in IF formulas needs to be wrapped in quotes (e.g. "Text"). TRUE and FALSE are the exception.
• Combine the IF function with the AND, OR and NOT functions or comparison operators to enhance the logical test.
• The following operators can be used to enhance the logical test:
  • = equal to
  • < less than
  • <= less than or equal to
  • > greater than
  • >= greater than or equal to
  • <> not equal to
• Nest up to 64 iterations of the IF function to enhance the logical test.
• The logical flow of a test can be reversed by using different operators. For example, IF(C2>75%,"Pass","Fail") is the same as IF(C2<75%,"Fail","Pass"), the difference being the > (greater than) and < (Less than) operators and the reversal of the TRUE/FALSE returns./li>
• #NAME? usually means the formula has been misspelled.

---

Related Functions

• AND() returns TRUE or FALSE statements if both logical conditions are met.
• OR() returns TRUE or FALSE statements if either logical condition is met.
• NOT() reverses the logic of an argument.

---

Functions Category

• Logical

 

---