Conditional Functions

Conditional functions perform calculations on a cell or range of cells only if those cells meet a certain condition. These functions test a given range and determine if the condition is true or false before continuing. A condition can be any relational comparison:

Examples:

• 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 six types of conditional functions covered by this course. Each has a specific use and syntax.

IF

=if(condition,value_if_true,value_if_false)

If the given condition is true, the cell will be set to the “value_if_true.” If the condition is false, the cell will be set to the “value_if_false,” if “value_if_false” is left blank, the cell will be set to “FALSE” by default.

Example:

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

Reads: If A2>3 is true, then set the cell to 32. Otherwise, set the cell to “Number too small”

Nested IF Functions

An interesting addition to the “if” function is the ability to nest one “if” function inside of another. A single “if” function, without the use of any other functions, can only test for a single condition. A nested if statement can be more specific by testing another condition.

Example:
Test the condition of whether the contents of cell A2 are between 1 and 3.

=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!”

COUNTIF

=countif(range,criteria)

The “countif” function will increment the active cell by one each time the criteria is true for a cell in the given range.

Example:
You want to know how many universities have “red” as one of their school colors, and have compiled a database of schools and their colors in Excel.

=countif (F1:F230, “=red”)

If a cell in F1:F230 satisfies “=red”, then increment the active cell by one.

SUMIF

=sumif(range,criteria,sum_range)

The “sumif” function utilizes a range and a corresponding sum_range of the same size. Each time the criteria is true, the active cell will be incremented by the value in the corresponding cell in the sum_range.  If “sum_range” is not specified, excel will treat “range” as “sum_range” as well.

Example:
You wish to know how many hours you have spent watching television, from a database of your time spent on various activities.

=sumif (D1:D230,”=television”, H1:H230)

If a cell in D1:D230 satisfies “=television”, then increment the active cell by the corresponding cell from H1:H230. If cell D7contains the word “television,” the active cell will be incremented from the corresponding cell from H1:H230, or cell H7.

AND and OR

“AND” and “OR” statements should be nested inside conditional statements. If they are not, the function will return either “TRUE” or “FALSE,” which is not highly informative. If we replace that first part of an “if” statement with an AND statement, we can easily use “if” statements based upon multiple conditions. “AND” and “OR” statements can also be nested inside each other.

=AND(condition, condition)
=OR(condition, condition)

Criteria vs. Condition

In excel, there is a difference between a criteria and a condition.  A condition is when there is an operator (such as =, <, >=, etc.) that has a value on either side (like this: A2 <= 3).  A criteria is when there is an operator and only one value (to the right of the operator) with the whole criteria in quotes (like this: “=15”).