Search Contextures Sites

 

30 Excel Functions in 30 Days

 

 

Contextures
Excel news
by email

 

 

 

Learn how to create Excel dashboards.

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

30 Excel Functions in 30 Days

 

Learn how to create Excel dashboards.

 

Excel IF Function Examples

Use the IF function when you want to test something, and show one result if the test result is True, and a different result if the test result is False. For example:

  1. If the sales total is $20,000 or greater, show "Good", otherwise, show "Poor".
  2. If no quantity is entered in cell D2, show an empty string "". Otherwise, multiply the quantity by the unit price.
  3. If no quantity is entered in cell D2, OR no unit price is entered in cell E2, show an empty string "". Otherwise, multiply the quantity by the unit price.

IF Function Arguments

The IF function has three arguments:

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

  1. logical_test: Required. Any value or expression that can be evaluated to TRUE or FALSE.
  2. value_if_true: Optional. The value that you want to be returned if the logical_test argument evaluates to TRUE.
  3. value_if_false: Optional. The value that you want to be returned if the logical_test argument evaluates to FALSE.

Example 1: Text message based on value

With the IF function, you test something, such as the value in a cell. In the second argument, you can specify the result if the test result is TRUE. In the function's third argument, specify the result if the test result is FALSE.

For example, this formula tests the sales total in cell B2, to see if it is $20,000 or greater.

  • If B3 is $20,000 or greater, the test result is TRUE, and the formula result is "Good"
  • If B3 is not $20,000 or greater, the test result is FALSE, and the formula result is "Poor"

=IF(B2>=20000,"Good","Poor")

Example 2: Nested IF Functions

You can nest an IF function within another IF function, to create more options for the results.

In the previous example, if the sales total in cell B2 is $20,000 or greater, the result is "Good", otherwise, the result is "Poor".

=IF(B2>=20000,"Good","Poor")

We can add another test, to see if the result is $10,000 or greater. If so, show "Average" as the result. If not, show "Poor" as the result.

On its own, the new IF formula would be:

=IF(B2>=10000,"Average","Poor")

To add this new test, to the existing formula, we'll nest this new IF statement into the formula, as the third argument, replacing the existing third argument.

All the red text from the new formula will replace all the red text in the existing formula:

=IF(B2>=20000,"Good","Poor")

becomes

=IF(B2>=20000,"Good",IF(B2>=10000,"Average","Poor"))

Each of the IF functions has three arguments, and the second IF function is the third argument for the first IF function.

nested if function

Order of Nested IF Functions

NOTE: When nesting IF functions, be sure to put the hardest test first. In this example, there are two tests:

  1. B2>20000
  2. B2>10000

If the amount in cell B2 is 25000, it will pass both of the tests. However, if the test order is changed,

=IF(B2>=10000,"Average",IF(B2>=20000,"Good","Poor"))

The result of the formula would be "Average", because the results of the first test are TRUE.

Download the Sample File

Download the sample IF workbook

Excel Functions Tutorials

SUM Functions  
VLOOKUP Function  
INDEX function and MATCH Function   
Count Functions  
INDIRECT Function  

 

 

 

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: June 9, 2014 10:51 PM