[Excel] Evaluating Branch Performance with the IF Function★

Hello, this is HARU!

In practical work, it is common to evaluate branch performance or contract counts per representative based on plan achievement rates or year-over-year growth rates.
By converting these into specific expressions rather than just comparing numbers, it becomes easier to intuitively judge whether the figures are good or bad.

In this article, I will introduce how to arrange the notation according to specified conditions.

The sample uses the "Year-over-Year Growth Rate" of branch sales status.

For Evaluation 1, set it to return "○" for branches with a growth rate of 100% or more, and "×" for those under 100%. For Evaluation 2, set it to return
"◎" for branches with a growth rate of 120% or more, "○" for 100% or more, and "×" for under 100%.

The concept of "conditional branching", where you change the response method according to certain patterns based on specific conditions, is an essential theme in Excel work, so please be sure to read on!

Evaluating branch performance

To change the displayed content according to specified conditions, use the IF function.

There are multiple functions in Excel that include "IF" in their name.
As "if" implies, keep in mind that these functions play the role of changing expressions or calculation targets based on conditions.

IF function

First, in the Evaluation 1 column, instruct it to return "○" for branches with a growth rate of 100% or more, and "×" for those under 100%.

1. Enter the IF function.

2. In the first argument, "Logical_test", enter the formula that serves as the judgment criteria.
Since the criteria here is whether the "growth rate is 100% or more", reference cell D4 where the growth rate is entered and connect it to "100%" using the comparison operator ">=".

↓ Check here for details on comparison operators! ↓

3. In the second argument, "Value_if_true", specify the value or string to return if the criteria are met.
Since we want to express it as "○" if the growth rate is 100% or more, enter "○".
* Remember that when incorporating a string into a formula, you must always enclose it in double quotation marks!

4. In the third argument, "Value_if_false", specify the value or string to return if the criteria are not met.
Since we want to express it as "×" if the growth rate is under 100%, enter "×".

5. Press [Enter] to confirm.
Since the growth rate exceeds 100%, "○" is returned.

6. Copy the formula downward.
(Center-align the text if necessary.)

You have successfully displayed the O/X evaluation for each branch based on the specified criteria.
Make sure to master these steps as a fundamental use case for the IF function and the concept of conditional branching.

Also, if you enter the reference values or evaluation results in cells outside the main table, you can build an IF function by referencing those cells.

This allows for more efficient updates when you want to change the criteria or display content, rather than manually editing fixed values one by one.

IF function (nested)

Next, in the Evaluation ② column, set it to return "◎" for branches with a growth rate of 120% or more, "○" for 100% or more, and "×" for less than 100%.

Since a single IF function can only specify two outcomes for one logical expression, you need to combine multiple IF functions to set three or more branches.

This method of placing a function inside another function is called "nesting".
(nest: to place inside or a bird's nest)

Let's take a look at the structure of the function.

1. Enter the IF function.

2. Set the first argument "logical_test" and the second argument "value_if_true".
For this first conditional branch, we want to display "◎" if the "growth rate is 120% or more," so enter it as shown in the figure below.

3. Enter another IF function in the third argument "value_if_false".
This serves to provide the next judgment criteria if the condition of 120% or more is not met.

4. In the second IF function, set the first argument "logical_test" and the second argument "value_if_true".
For this second conditional branch, we want to display "○" if the "growth rate is 100% or more," so enter it as shown in the figure below.

5. In the second IF function, set the third argument "value_if_false" to the third conditional branch.
In this case, if the growth rate is neither 120% or more nor 100% or more—in other words, if it is "less than 100%"—we want to display "×," so enter "×".

6. Finally, close the parentheses for the second IF function, close the parentheses for the first IF function, and press [Enter] to confirm.
Since the growth rate is less than 120% but over 100%, the second conditional branch "○" is returned.

* When nesting functions or performing prioritized calculations as in this case, if you are using double or triple parentheses in a formula, make sure to close the same number of parentheses before confirming.

7. Copy the formula downward.
(Center-align the text if necessary.)

Compared to the evaluation ① obtained with a single IF function, we were able to increase our repertoire of expressions in evaluation ② by combining IF functions!

How was it?

We introduced the concept of "conditional branching," which changes how to handle things based on specific patterns according to certain conditions, and how to use the IF function to arrange notations according to specified conditions.

This is a theme you will definitely encounter in Excel work, so let's master it while we have the chance!

↓↓ The content of this article is explained in a video ↓↓
*The details of this article may differ from the version and explanation content at the time the video was recorded.

↓↓ For those who want to speed up their Excel operations as much as possible ↓↓