Excel commonly used formulas and techniques

A collection of commonly used excel formulas is brought to you by Xixi. Excel is a tool that everyone often uses in daily life and work. It is officially created by Microsoft and is widely used around the world. However, excel requires everyone to be familiar with the relevant formulas before it can be used. Well, it improves efficiency, but most people just do simple operations and do not understand or learn in depth. This collection of commonly used formulas can be used immediately.

Comprehensive list of commonly used formulas in excel:

1. Find duplicate content formula: =IF(COUNTIF(A:A,A2)>1,"Duplicate","").

2. Use the birth year and month to calculate the age formula: =TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0).

3. Calculation formula from the birth year and month of the entered 18-digit ID number: =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2, 13,2)).

4. To let the system automatically extract the gender from the entered ID number, you can enter the following formula:

=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"male","female"),IF(MOD(MID(C2,17,1), 2)=1,"Male","Female")) "C2" in the formula represents the cell where the ID number is entered.

1. Sum: =SUM(K2:K56) - Sum the area from K2 to K56;

2. Average: =AVERAGE(K2:K56) - Calculate the average of the area K2 K56;

3. Ranking: =RANK(K2, K$2:K$56) - Rank the results of 55 students;

4. Grade: =IF(K2>=85,"Excellent",IF(K2>=74,"Good",IF(K2>=60,"Pass","Fail")))

5. Semester overall evaluation: =K2*0.3+M2*0.3+N2*0.4 - Assume that column K, column M and column N store the students' three grades of "usual overall evaluation", "midterm" and "final" respectively.

Comprehensive list of commonly used formulas: