[Excel] Adding Performance Ranks to Branch Evaluations★

Hello, this is HARU!

In practical work, in addition to evaluating sales status by branch or representative, it is sometimes necessary to also list performance ranks or the need for additional measures.

The sample evaluates the "year-on-year growth rate" of sales by branch.

For Evaluation ①, I have used the IF function to set it to return "◎" for branches with a growth rate of 120% or more, "○" for 100% or more, and "×" for less than 100%.
For Evaluation ②, I have used the IF function to set it to return "1" for branches with a growth rate of 120% or more, "2" for 100% or more, and "3" for less than 100%.

↓Click here for how to use the IF and IFS functions↓

Now, we have decided to add performance ranks to column G based on the branch evaluations.
You could set up conditional branches from scratch using the IF or IFS functions, but since the evaluation results like "◎, ○, ×" or "1, 2, 3" are already displayed in adjacent columns, it would be ideal to utilize them.

Therefore, in this article, I will introduce two functions that allow you to set multiple branches based on referenced strings or values.

I will also describe the differences from the IF and IFS functions later, so please be sure to read until the end!

Adding performance ranks based on evaluation results

To change the notation by pattern based on referenced strings or values, use the SWITCH function or the CHOOSE function.
(switch: switch, exchange / choose: select, pick)

In both cases, we will set it to return "SS" rank for branches with a growth rate of 120% or more, "A" rank for 100% or more, and "B" rank for less than 100%.

First, let's look at the basic structure of each function.

SWITCH function

① Insert the SWITCH function.

② For the first argument "expression", reference the data in the column where the evaluation result is entered as a string (symbol).

③ Enter "◎" for the second argument "value1" and "SS" for the third argument "result1".
This sets it to return "SS" when the reference result of the first argument "expression" is "◎".

④ Enter "○" for the fourth argument "value2" and "A" for the fifth argument "result2".
This sets it to return "A" when the reference result of the first argument "expression" is "○".

5. Enter "×" for the 6th argument "value 3" and "B" for the 7th argument "result 3".
This sets it to return "B" when the reference result of the 1st argument "expression" is "×".

Note that the final branch works even without specifying a "value".
In this case, by setting it to return "B" for anything that does not match "◎" or "○", you can omit the "×" in "value 3" as shown in the figure below.

Copying this formula downward will return the performance rank according to the result of the referenced evaluation 1.

The SWITCH function was implemented starting with Excel 2016.
If you are using an earlier version, you can use the CHOOSE function, which will be explained next, as a substitute.

CHOOSE function

1. Insert the CHOOSE function.
2. For the 1st argument "index", reference the data in the column where the evaluation result is entered as a value.

3. Enter "SS" for the 2nd argument "value 1", "A" for the 3rd argument "value 2", and "B" for the 4th argument "value 3".

The CHOOSE functionreturns the 2nd argument "value 1" if the result is 1, the 3rd argument "value 2" if the result is 2, and the 4th argument "value 3" if the result is 3, by referencing a cell that generates a sequential number starting from 1 in the 1st argument "index"
.

In this case, it is instructing to return "SS" for "value 1" if the evaluation result in column F is 1, "A" for "value 2" if it is 2, and "B" for "value 3" if it is 3.

Copying this formula downward will return the performance rank according to the result of the referenced evaluation 2.

Summary (How to use logical functions)

The SWITCH function and CHOOSE function are classified as the same type of logical functions as the IFS function.

The features of each function are as follows.

In addition to the general usage guidelines shown above, let's utilize them according to the situation from perspectives such as:
・Whether you can use comparison operators in the 1st argument (the condition) or if you can only specify it by cell reference.
・Whether you can explicitly set the processing for when none of the conditions are met.

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

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