For example, on the left is the employee's KPI score data, and according to the reward standard on the right, the reward data for each employee is calculated:
This kind of typical multi-condition nesting was previously done with the IF function formula
Today we are sharing a new formula method, IFS function formula, which is done more efficiently
1. Classical IF formula
If we use the IF formula, we need to use multiple for nesting
The idea is to first judge the first layer:
=IF(B2<60,0,"")
If it is less than 60, return 0, otherwise continue to judge
Then we nest the IF function into the third parameter, using the formula:
=IF(B2<60,0,IF(B2<70,1000,""))
Some friends may be curious to ask, why not add a B2>60 to the second layer
Because of the use of nesting, the first layer is less than 60, which is not qualified, and it is greater than 60 by default, so there is no need to write
And so on, after all the nests are written, and the input formula is:
=IF(B2<60,0,IF(B2<70,1000,IF(B2<80,2000,IF(B2<90,2500,3000))))
The formula as a whole is still complicated, mainly because there are many brackets, as long as the brackets are in the wrong position, it is easy to report errors, and it is difficult to find what is wrong
2. New formula IFS
It can be easily used to make multi-condition judgments
There is no need to write multiple parentheses
For the first level of judgment, we can enter the formula:
=IFS(B2<60,0)
If a second layer of judgment is required, the formula that only needs to be entered is:
=IFS(B2<60,0,B2<70,1000)
When the same second layer makes a judgment, it will assume that the condition of the first layer is not true, that is, B2 is greater than or equal to 60
So you can write b2<70 directly
If we need multiple layers of judgments, then we can write the judgment formula all the time at the end, the result, so we only need to enter the formula is:
=IFS(B2<60,0,B2<70,1000,B2<80,2000,B2<90,2500,B2>=90,3500)
Isn't it easier and more efficient, don't worry about the wrong position of the brackets, try it out!