Conditional statements

IF function basics

Conditional statements, conditional expressions, conditional processing, IF-THEN or IF-THEN-ELSE are all names for a programming concept where conditions are tested and actions are performed if conditions are met. If a certain condition is met, something is calculated. If that certain condition is not met, either nothing happens, or something else is calculated.

In Excel, when conditions are met, something is TRUE.

When conditions are not met, something is FALSE.

TRUE and FALSE are logical values. They are not text, and you will even notice that Excel centers them if you return them to unformatted cells – meaning they are neither text, which is aligned left, nor numbers, which are aligned right.

 

The IF function will check IF something is TRUE, and THEN, if it is TRUE, perform a calculation. ELSE, when something is FALSE, different calculation will be performed.

Syntax of the IF function is as follows:

= IF ( logicat_test; value_if_true; value_if_false )

Logical test will always result in logical value TRUE or logical value FALSE.

Value if true can be either a number, text string, another formula or a function.

Value if false can also be either a number, text string, another formula or a function.

This is how the basic application of the IF function looks like:

excel if function equal to

Here, we are comparing numbers in column E with zero.

Logical test reads as follows: if the number in column E is zero than the statement is TRUE, else the statement is FALSE.

If TRUE then text “active” will be returned, else text “inactive” will be returned.

 

If we don’t specify what will be returned for either TRUE or FALSE, the IF function will return zero.

However, if we don’t want zero returned, we can return empty text field with empty quotation marks [“”]:

excel if function zero vs empty
Here, we are comparing text in column F with the text “inactive”.

Logical test reads as follows: if the text in the column E is the same as the text “inactive”, then the statement is TRUE, else the statement is FALSE.

If TRUE then text “” will be returned, else not defined, so zero will be returned.

Note that IF function is not case-sensitive, we would get the same results with the text “Inactive”.

 

Finally, we’ve already shown that you can perform logical tests with both numbers and text.

While writing you statements make sure you are comparing two of the kind types of values – rules from previous lessons apply here.

1=1 is TRUE as both parts of the statements are numbers and the number in both cases is 1.

“1”=”1″ is TRUE as those two text strings are the same.

But, 1=”1″ is FALSE as one part of the statement is number, other part is text, and they are thus not the same.

 

Comparison operators

Simplest logical tests are performed with the help of Comparison operators (also known as Logical operators).

These are Comparison operators available:

Operator Description
= equal to
> greater than
>= greater than or equal to
< less than
<= less than or equal to
<> not equal to

 

Here is one example of how to use less than or equal to operator [<=]:

excel if function less than or equal to

Here, we are testing if numbers in column E are less than or equal to number 2 – we consider users not active for 3 or more days to be inactive.

Logical test reads as follows: if the number in column E is less than or equal to number 2 then the statement is TRUE, else the statement is FALSE.

If TRUE then text “active” will be returned, else the number of days inactive in column E will be returned.

Nothing is stopping us from writing our statement the other way around:

excel if function greater than or equal to

Here, we are testing if numbers in column E are greater than or equal to number 3.

Logical test reads as follows: if the number in column E is greater than or equal to number 3 then the statement is TRUE, else the statement is FALSE.

If TRUE then the number of days inactive in column E will be returned, text “active” will be returned, else text “active” will be returned.

 

Nested IF statements

The IF function also supports “nesting” of other functions.

This works with any general-purpose function.

Let’s say we want to give incentive to our user which give us poor rating (3 or less stars). Incentive would be 10 gold (our application “currency”) per day of inactivity. We also want to know how much EUR would that gift represent, and our “gold” is currently traded for EUR 0,04517 for 10 gold:

excel if function another function if true

Here, we are testing if numbers in column D are less than number 4 because we want to consider users that give us poor rating (3 or less stars) for incentive.

Logical test reads as follows: if the number in column D is less than 4 then the statement is TRUE, else the statement is FALSE.

If TRUE then number of inactive days will be multiplied with 10 gold and multiplied with gold exchange rate of 0,04517, rounded to two decimal points and then returned, else zero will be returned.

We have thus successfully nested common ROUND function inside of the IF function.

 

More importantly, we can also “nest” one or more IF functions inside the IF function. This enables us to perform multiple different actions depending on the tested condition.

Let’s say we want to convert our number of stars into descriptive rating, with 1 star being Abysmal, 2 Bad, 3 Passable, 4 Good and 5 Excellent. While there are other ways to do that, we will now do it by “nesting” IF functions to illustrate the principle:

excel nested if function

As you can see, every FALSE return starts a new logical test until all options are exhausted, and every FALSE statement except the last one contains new IF function.

Things can get complicated really fast, and it’s easy to make an error, especially for new users. Don’t avoid sketching flowcharts representing your process first, if they will help you constructing your statements. This is how a flowchart for the function shown above would look:

excel nested if function flowchart

 

Simple logical tests

Other than performing logical test using comparison operators, or just typing TRUE or FALSE inside IF function, you can also return TRUE and FALSE with functions.

If you just want to return TRUE or FALSE, easiest way is to use =TRUE() or =FALSE().

However, you will rarely want to do that. What you will want to do is to perform a logical test via function with the end goal of “nesting” that function inside IF function. We’ve already mentioned some functions that enable this:

  • the EXACT function which compares whether two text strings are identical, and returns TRUE or FALSE depending on the outcome
  • the ISTEXT function which checks whether the value is text or not, and returns TRUE or FALSE depending on the outcome
  • the ISNUMBER function which checks whether the value is number or not, and returns TRUE or FALSE depending on the outcome.

Let’s mention a few more of them:

  • the ISLOGICAL function which checks whether the value is logical value (TRUE or FALSE) or not, and returns TRUE or FALSE depending on the outcome
  • the ISBLANK function which checks whether the cell is empty or not, and returns TRUE or FALSE depending on the outcome
  • the ISERROR function which checks whether the cell or formula return is an error (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!), and returns TRUE or FALSE depending on the outcome
  • the ISERR function which does the same but ignores #N/A error, and the ISNA function which returns TRUE only for #N/A. Difference can sometimes matter with array formulas.

We will now test figures in column E with these logical functions. Data contained should be text, but there are many empty cells and data that Excel recognized as something other than text at import:

excel logical test functions istext isnumber islogical isblank iserror

We will now “nest” the IFERROR function inside of the IF function:

excel if iserror functions

We want to add columns A and B, but given many errors in column B, to at least partially perform this, first we have to check for errors in column B. If the function returns TRUE, i.e. there is an error in column B, we will only take number from column A. If the function returns FALSE, there is no error in column B, and we will sum columns A and B.

However, you will notice and issue with cell D21. Given that cell B21 contains space ” ” sum of cells A21 and B21 results in a new error.

To avoid such issues, we can write our formula the following way:

excel if iserror functions v2

Here, we first check if sum of column A and column B returns an error. If the function returns TRUE, i.e. instead of sum we receive an error, we will only take number from column A. If the function returns FALSE, there is no error in column B, and we will sum columns A and B.

 

Complex logical tests

Sometimes multiple conditions need to be meet for certain calculation to be performed. In that case, we can perform our logical tests “nested” inside specialized functions.

The AND function tests multiple conditions and returns TRUE if all tested conditions are TRUE.

Let’s try to identify users that are at level 10 or less, AND rate our application as three stars or less, AND have never received any incentive:

excel and function

Here, we are first checking whether a number in column C is less than or equal to 10, next, whether a number in column D is less than or equal to 3, next, whether a number in column E is equal to zero. When these test result in =AND(TRUE;TRUE;TRUE) TRUE will be returned. If there is one FALSE, or an error, FALSE will be returned.

In the end, this is useful if we “nest” this function inside the logical test part of the IF function:

excel if function nested and function

Here, we are first checking whether a number in column C is less than or equal to 10, next, whether a number in column D is less than or equal to 3, next, whether a number in column E is equal to zero. When all these test results are true, AND function will also return TRUE and 100 gold will be added to user. Else, zero will be returned.

 

The OR function tests multiple conditions and returns TRUE if one of the tested conditions are TRUE.

Let’s try to identify users that are at level 10 or less, OR rate our application as three stars or less, OR have never received any incentive, and add them 100 gold:

excel if function nested or function

Here, we are first checking whether a number in column C is less than or equal to 10, next, whether a number in column D is less than or equal to 3, next, whether a number in column E is equal to zero. When any of these tests is true, OR function will also return TRUE and 100 gold will be added to user. Else, zero will be returned.

As you can see, almost all of our users are at level 10 or less, OR rate our application as three stars or less, OR have never received any incentive, OR same combination of those criteria.

There is just one user that is not at level 10 or less, rates our application 4 or 5 stars and has already received incentive from us.

 

Those functions can also be nested one inside another:

excel if function nested or function nested and function

Here, we first identify users that are at level 10 or less, AND rate our application as three stars or less, AND have never received any incentive. Next, we use ISBLANK function to identify users that aren’t bothered enough to leave a comment about our application. If one is true, OR another is true, we calculate incentive.

There are several users that didn’t try to leave us a comment and thus qualify [green, OR(FALSE;TRUE)], there is one user that qualifies via first criteria [yellow, OR(TRUE;FALSE)], and there is one user that qualifies under both criteria [red, OR(TRUE;TRUE)]. All of them are marked for incentive.

Users that don’t qualify are [white, OR(FALSE;FALSE)].

 

Worth mentioning are also:

  • the XOR function, or exclusive OR function, which returns TRUE when individual returns differ (TRUE;FALSE), and FALSE when individual returns are the same (TRUE;TRUE or FALSE;FALSE)
  • the NOT function which returns FALSE when something is TRUE, and TRUE when something is FALSE. You can also use not equal to operator [<>] to accomplish the same return, i.e. =NOT(“a”=”a”) which returns FALSE is the same as “a”<>”a” which also returns FALSE
  • the IFS function which is designed to provide functionality of multiple nested IF statements, and in theory should be more readable. However, note that in this function, if no TRUE conditions are found, #N/A error is returned – you have to anticipate every possible condition while writing it in order to avoid errors.

 

Leave a Reply

Your email address will not be published. Required fields are marked *