IF function in Excel is an inestimable ally when we need to implement conditional logic, that is when we need different results depending on a condition.
The syntax is
IF(logical_test, [value_if_true], [value_if_false]), where
logical_testis an expression that evaluates to
value_if_trueis an optional argument, and it is what the expression evaluates to in case
logical_testis false, and
value_if_falseis an optional argument that determines the value in the case that
How to conditionally set the value of a cell in Excel
Let’s see the if statement in action in the simplest use case, when the value of a cell is determined between two options based on the value of a different cell.
For example, let’s say that we have a list of projects, the percentage progress on each of them, and we want to automatically set the string to “In progress” or “Finished”. Then we can write
IF(B2=100, "Finished", "In progress") (where
B2 is the first cell with the progress).
After writing the formula in the first cell we can just double click on the green handle that appears when the cell is selected and the formula will populate to all other cells in the column.
How to use a nested if statement to conditionally set the value of a cell with more options in Excel
Continuing from the example above, we may want to break down the progress status even more. This time we want to have 7 different status strings depending on the progress of the project.
We need to use nested if statements, writing an if statement in place of
value_if_false (it can be used also in place of
value_if_true but it becomes messier).
Let’s try to build a formula in case we want to have all these progress statuses: Not Started, Started, First Half, Halfway Through, Second Half, Almost Finished, Finished.
In this case we need a total of 6 if statements, so we could write something like this:
=IF(B2=0, "Not started", IF(B2<10, "Started", IF(B2<50, "First Half", IF(B2=50, "Halfway through", IF(B2<90, "Second half", IF(B2<100, "Almost finished", "Finished"))))))
Excel allows a max of 7 nested if statements. If we wanted to expand our list of possible statuses, we could add only one more condition and one more status. But fortunately we can add more using a different function.
How to use
IFS() for more than 7 conditions in Excel
IFS() function was introduced in Excel 2016, and it allows up to 127 conditions. The syntax is
IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2] ... [logical_test127, value_if_true127]).
The logical test expressions are evaluated consequentially. When the first one that returns TRUE is found, the corresponding
value_if_true is given as output.
The previous expression that we wrote with nested if statements can be written like this:
=IFS(B2=0, "Not started", B2<10, "Started", B2<50, "First Half"B2=50, "Halfway through", B2<90, "Second half", B2<100, "Almost finished", B2=100, "Finished")
When you need to set conditionally, you’ll often use
IF(). You can nest multiple
IF() statements to have complex logic chains.
But if you need to use more than 7 nested
IF() statements, then you can use