Conditional Functions in Excel
In this module, you’ll learn:
1. What are conditional functions ?
2. Use of Conditional Function
3. Type of conditional functions in excel
4. Criteria vs. Condition
5. Operators
Conditional Function
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
Use of Conditional Function
At some point in time, when you are faced with a lot of data in your spreadsheets, you may
want to find a way to highlight or “filter” out some of your data based on specific criteria. For
example, if you wanted to see if the value of a cell in column A is equal to the value of a cell in
column B (duplicates), you can use a conditional formula in column C to give you a TRUE or
FALSE result.
Type of conditional functions in excel
1. IF
An IF function in Excel consists of 3 components:
A logical test, using an operator sign for logical comparison;
A value to return if the logical test results in TRUE;
A value to return if the logical test results in FALSE.
Format: =IF(A1>B2; “TRUE”; “FALSE”)
Example:
We own a greengrocery and we have a list of fruits in stock. Whenever the amount in stock is
less than 20, we need to place a new order with the wholesaler. The IF function can help us with
this task. IF the amount of a certain fruit is under 20 (TRUE), we need to place an order. IF the
amount is 20 or more (FALSE), we don’t have to take any action.
We call the function in the ‘status’ column: =IF(B2<20;”Order”;”No action”):
When we press enter, the function will publish the result for FALSE: No action, because the
value in cell B2 isn’t less than 20.
2. Nested IF Functions
It is possible to use an IF statement as a TRUE or FALSE value inside another IF function. This
way you can test for more than one condition within one function and return more than two
results.
Example
We will use the same list of fruits, but this time we will reorder when the amount is less than 20
and we will put the fruits on sale when the amount is 30 or greater than 30.
In this case our value for FALSE will be replaced by a second IF function:
=IF(B2<20;”Order”;IF(B2=>30;”Sale”;”No action”)):
Pressing enter will display the value in cell C2. We will extend the function to the relevant cell
range in column C, to find out which fruits need to be re-ordered, which fruits need to be put on
sale and which fruits require no further action:
3. SUM
The SUM function in excel adds the numerical values in a range of cells. Being categorized under
the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to
be summed. The values supplied to the function can be numbers, cell references or ranges.
Example:
We want to sum the cells A2 and A3 shown in the image.
4. SUMIF
The SUMIF function is a premade function in Excel, which calculates the sum of values in a
range based on a true or false condition.
5. AVERAGE
The AVERAGE function in Excel calculates the arithmetic mean of the supplied values. Such
values can be numbers, percentages or times. In the mean (or average), the sum of all the items
is divided by the number of items on the list.
Example:
6. AVERAGEIF
The Microsoft Excel AVERAGEIF function returns the average (arithmetic mean) of all
numbers in a range of cells, based on a given criteria.
7. COUNTIF
COUNTIF is an Excel function to count cells in a range that meet a single condition. COUNTIF can
be used to count cells that contain dates, numbers, and text
EXAMPLE:
8. AND and OR and NOT
AND : The AND function is the most popular member of the logic functions family. It comes in
handy when you have to test several conditions and make sure that all of them are met.
Technically, the AND function tests the conditions you specify and returns TRUE if all of the
conditions evaluate to TRUE, FALSE otherwise.
Example:
OR : As well as AND, the Excel OR function is a basic logical function that is used to compare two
values or statements. The difference is that the OR function returns TRUE if at least one if the
arguments evaluates to TRUE, and returns FALSE if all arguments are FALSE. The OR function is
available in all versions of Excel 2016 - 2000.
NOT: You use the NOT function in Excel to reverse a value of its argument. In other words, if
logical evaluates to FALSE, the NOT function returns TRUE and vice versa. For example, both of
the below formulas return FALSE:
9. MINIFS and MAXIFS functions
10. SWITCH
The Microsoft Excel SWITCH function compares an expression to a list of values and returns the
corresponding result.
Example
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”).
Operators
Operators specify the type of calculation that you want to perform on elements in a formula
such as addition, subtraction, multiplication, or division.
Types of operators: There are four different types of calculation operators:
[Link] operators
[Link] operators
[Link] concatenation operators
[Link] operators