Welcome guest

Conditional Functions

When peforming calculations on large sets of data, the built-in functions discussed earlier are very useful. However, those functions are also very static. count(range) will always return the number of cells in the range. If a user wanted a count of a specific type of cell or cell content, he or she would have to either hand-count each of the appropriate cells, use a wide range of uncontinuous ranges in a count function, or format the entire spreadsheet to run an unhindered count function.

For example, assume you want to create a database for your personal movie collection. A smaller IMDB if you will. In your database, you want to keep track of a wide range of information: movie title, genre, cast, etc. You happen to be a huge Fracis Ford Coppola fan, and wish to know how many of the movies in your collection he directed. You could hand count each director cell his name appears in, but that is tedious and prone to error. Using uncontinuous ranges in a count function is quicker, but Coppola has movies spanning many decades and genre. Using the third option above, you could sort the entire spreadsheet by the director then use the count function. However, this procedure becomes tedious if tomorrow you wish to search by movie genre, release debut, or producer.

Conditional functions perform calcuations on a cell or range of cells only if those cells meet a certain criteria, or condition. A condition can be any relational comparision:

  • A3>14 Whether the contents of cell A3 are larger than the number 14
  • D5<=2 Whether the contents of cell D5 are less than or equal to the number 2
  • T47="cheese" Whether the cell T47 contains the the word cheese

There are three conditional functions of particular use. Each has a specific use and syntax.

IF

if(condition,value_if_true,value_if_false)

The if function will test a condition to see if it is true or false. If the condition is true, then the cell will be set to the value_if_true. If the condition is false, then the cell will be set to the value_if_false.

Conditional functions can be confusing at first. An easy way to set these functions up is to ask yourself the question:

If condition is true, then set the cell to value_if_true. Otherwise, set the cell to value_if_false.

If A2>3 is true, then set the cell to 32. Otherwise, set the cell to "Number too small"

In the example, the active cell will be set to the value 32 if the value of A2 is larger than 3. If the value of A2 is 3 or less, the active cell instead will be set to the text phrase Number too small. Now, take this sentence and insert it into the conditional function.

=if (A2>3,32,"Number too small")

Take note that values can be placed without parenthesis, but text requires them. If no values are set for value_if_true or value_if_false, Excel will place TRUE or FALSE respectively in the cell by defalut.

Nested IF Functions

An interesting addition to the IF function is the ability to nest an IF function inside of another. In the IF section above, the example tested the condition of whether the contents of cell A2 were larger than the value 3. Take that a step further. What if you wanted to test the condition of whether the contents of cell A2 are between 1 and 3? The IF function can't test two conditions at once, so begin with one of them.

If condition is true, then set the cell to value_if_true. Otherwise, set the cell to value_if_true.

If A2>3 is true, then set the cell to "Number too large".

If the value of A2 is greater than 3, the active cell is set to Number too large.

=if (A2>3 , "Number too large")

However, we don't want to set a value_if_false, because we still want to test to see if the value of A2 is less than 1. So, instead of setting a value to value_if_false, we will set it to another IF function.

If condition is true, then set the cell to value_if_true. Otherwise, set the cell to value_if_false.

If A2<1 is true, then set the cell to "Number too small". Otherwise, set the cell to "Just right!".

This is the second half of our combined function. If the value of A2 is less than 1, the active cell is set to Number too small. If the value of A2 is greater than 1, the active cell is set to Just right!

=if (A2<1 , "Number too small","Just right!")

Now, let us combine these two IF functions together. Place the second in the value_if_false position of the first.

=if (A2>3 , "Number too large" , if (A2<1 , "Number too small","Just right!"))

The completed function will test whether the value of A2 is larger than 3. If it is, the active cell will be set to Number too large. If A2 is less than 3, it will be tested to see if the value of A2 is less than 1. If it is, the active cell is set to Number too small. Otherwise, we know that the value of A2 must be between 1 and 3, so the active cell is set to Just right!.

It is easy to see that nested IF functions can be very powerful, yet very easy to confuse. It is always beneficial to write down the individual conditions for testing, then nest the needed functions into the value_if_true or value_if_false fields.

COUNTIF

countif(range,condition)

The countif function will test a condition in a range of cells to see if it is true or false. Each time the condition is true, the active cell will be incremented by one. This is a fine solution to the original problem with the movie database. If the user wanted a count of the number of movies that Coppola directed, a countif function could be used to search for each time his name appears in the range of director cells.

If a cell in the range satisfies the condition, then increment the active cell by one.

If a cell in F1:F230 satisfies "=Francis Ford Coppola", then increment the active cell by one.

In the example, the active cell will be incremented if a cell in F1:F230 contains "Francis Ford Coppola". Now, take this sentence and insert it into the conditional function.

=countif (F1:F230 , "=Francis Ford Coppola")

 

SUMIF

sumif(range,condition,sum_range)

 

The sumif function utilizes a range and a corresponding sum_range of the same size. The function will test a condition in the range of cells to see if it is true or false. Each time the condition is true, the active cell will be incremented by by the value in the corresponding cell in the sum_range. For example, you are interested in how many hours of horror movies you have accumulated.

If a cell in the range satisfies the condition, then increment the active cell by the corresponding cell from sum_range.

If a cell in D1:D230 satisfies "=Horror", then increment the active cell by the corresponding cell from H1:H230.

In the example, the active cell will be incremented if a cell in D1:D230 contains "Horror". If cell D7 contains the word "Horror," the active cell will be incremented from the corresponding cell from H1:H230, or cell H7. In the end, you will have the accumulated length of time for each horror movie in your database. Now, take this sentence and insert it into the conditional function.

=sumif (D1:D230,"=Horror", H1:H230)

 

AND

AND(condition,condition)

 

The AND function will test all conditions listed within its parameters and report back to the user a value of true or false.

In the example, we assume that the cells D1 and D2 contain the values Monday and Tuesday respectively. The following statement, if entered as followed (with the assumptions listed above) will return true.

 

=AND (D1="Monday" , D2="Tuesday")

 

Likewise with our look at conditional statements, it is possible to nest an AND statement inside of an IF statement for more functionality. Remember that the function AND returns a true or false, much like the first part of the IF syntax. If we replace that first part with an AND statement then we can run conditional IF statements based upon multiple conditions easily, without nesting IF statements.

The same thing can be done with AND and OR statements recursively, that is, you can nest AND and OR statements within each other for even more flexibility when working with spreadsheets.

OR

OR(condition,condition)

 

The OR function will iterate through all of the conditions listed within its parameters and report back to the user a value of true or false, with only the requirement that one has to be true to report a true and false otherwise.

In the example, we assume that the cells D1 and D2 contain the values Baseball and Basketball respectively. The following statement, if entered as followed (with the assumptions listed above) will return true.

 

=OR (D1="Baseball" , D2="Basketball")

Likewise with our look at conditional statements, it is possible to nest an OR statement inside of an IF statement for more functionality. Remember that the function OR returns a true or false, much like the first part of the IF syntax. If we replace that first part with an OR statement then we can run conditional IF statements based upon multiple conditions easily, without nesting IF statements.

The same thing can be done with AND and OR statements recursively, that is, you can nest AND and OR statements within each other for even more flexibility when working with spreadsheets.