IF Function

Description

In its basic usage, the IF function tests for a condition and returns a result depending on whether the condition is true or false.

Simplified syntax:

=IF (Condition, if True , [If_False] )

Example 1:

The condition in this example is a formula. If the score is greater than 70 enter the text “Pass” in the cell, if not greater than 70 (False) enter the text “Fail” in the cell.

When using the IF function it’s important to ensure your condition, the formula, is written to do what you want it to. In this example, you may really want to evaluate if the score is greater than or equal to 70.

As such the formula would read:

=IF (B2=>70, “Pass”, “Fail”)

The If True argument is required and the If False argument is optional. For example, leaving out the False argument:

=IF (B2=>70, “Pass”)

If a Fail condition is experienced, Excel will enter its default FALSE which is a different display from the above formula where “Fail” is entered but has the same meaning.

Example 2:

Another basic IF function, using the same basic logic in Example 1.  If the gain is greater than $5, enter the text “Sell”, if not, enter the text “Hold”:

Again, this is a very basic example, the formula would most likely be enhanced to fit your specific needs.

Example 3:

Let’s say you receive a download of invoice amounts and you are tasked with calculating the related sales tax. If  Y  is in the column B cells, then multiply the invoice amount by the sales tax rate. If the column B cell is not a  Y  then enter the number  0 :

Note that in Example 1 and 2 that text to be entered is surrounded by double quotation marks (“”).  In this example numbers are to be entered which are entered without the double quotation marks.

Example 4

Another good practice example is an aged accounts receivable. Here’s an abbreviated aging:

As with Example 3, numbers are entered resulting from the IF function. 

Let’s now looks at what is expected in column E which results in a Nested IF function:

If C6 is greater than 30 AND less than 61, enter D6 or:

=IF(E6>30,IF(E6<61,C6,0),0)

A full aging exercise is available at this link that goes into great detail for all the issues that may come up with the IF function:

IF Function with Nested IFs used in an Aging exercise.xlsx

Summary:
This page presents the IF function using very basic scenarios. More advanced examples are presented elsewhere on this site.

The technical syntax:
This is the Syntax as presented by Excel. The Simplified Syntax at the top of the page is the “English” version of the Syntax.

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

Author: Jim Colville

Date: 7/18/1997 – Updated: 9/4/2020