Today, we share several groups of formulas that we often use in our work. If we encounter similar problems in the future, we can apply them directly, which can greatly improve our work efficiency. Your colleagues will certainly look at you with new eyes and will never treat you as a whitewash again.
1、 Multi condition judgment.
As shown in the figure below, we will give a reward of 1000 yuan to those whose sales volume is more than 10000 and whose attendance exceeds 25 days. Otherwise, there will be no reward.
There are two conditions: sales volume and attendance.
This requires the formula: IF (AND (B3>10000, C3>25), 1000, "")
AND means that the two conditions must be met at the same time before there is a reward, otherwise there is no reward. If only one condition needs to be met, change AND to OR.
2、 Sum by keyword
As shown in the figure below, if I only need to ask for sales in Beijing, I need to use this formula
=SUMIF (A2: A8, "Beijing", C2: C8)
SUMIF means conditional summation. If A2 to A8 is Beijing, then the sales from C2 to C8 will be summed.
Of course, if the sales in Shanghai or Guangzhou want to sum up, just change Beijing to Shanghai or Beijing.
3、 Calculate length of service
Calculating length of service is the most common way we encounter in our work. Of course, there are many ways, but I think we can use DATEDIF This formula is the best
As shown in the figure below, if we require the length of service of these employees, just use this formula
=DATEDIF(B2,TODAY(),”y”)
TODAY stands for today, Y stands for year, which means how many years is the difference between B2 and today
4、 Determine whether the ID card number is repeated
Sometimes, when there are many ID card numbers, we need to determine whether there are duplicate numbers, and then we need to use this formula:
IF (COUNTIF ($C $2: $C $8, C2)=1, "", "Yes")
Here, COUNTIF ($C $2: $C $8, C2)=1 is used to count whether there is only one number, and IF is used to return the result. If it is 1, it returns blank. If it is not 1, it indicates that there are duplicate numbers, and it returns Yes.
5、 Extract the date of birth from the ID card number
As shown in the figure below, we need to extract the date of birth from the ID card. We know that the date of birth is the eight digits starting from the seventh digit of the ID card number, so we need to use the following formula
TEXT(MID(C2,7,8),”0000-00-00″)
First, use MID to extract 8 numbers of the birth date, and then use TEXT to display the date format.
6、 Use ID number to calculate age
As shown in the figure below, if we want to calculate age, we first use the above formula to extract the date of birth, then use DATEDIF to judge the difference with today, and then use year (Y) to display.
These are the six common formulas to be shared today. If you have similar problems in your work, you can apply them directly.
