2. Calculate the age formula with birth date = trunc ((days 360 (H6, "2009/8/30", false))/360,0).
3. The formula for calculating the birth date of18 digit ID number is = Concatenate (mid (e2 e2,7,4), "/",mid (e2,0/12), "/",mid (e2,0/3).
4. Let the system automatically extract gender from the input ID number. You can enter the following formula:
= if (len (C2) = 15, if (mod (mid (C2, 15, 1), 2) = 1, "male", "female"), if(mod(C2, female).
5.Sum:= Sum(K2:K56)- Sum the areas from K2 to K56;
6. Average: = average (K2: K56)-average K2 K56
7.Rank: =RANK(K2, k $ 2:k $ 56)- Rank the scores of 55 students;
8. Grade: = IF(K2 & gt；; =85, "excellent", if (K2 & gt=74, "good", if (K2 & gt=60, "pass", "fail"))
9. Overall evaluation of the semester: = K2 * 0.3+M2 * 0.3+N2 * 0.4-suppose that column K, column M and column N store students' overall evaluation, mid-term evaluation and final evaluation respectively;
10, the highest score: = max(K2:K56)- Find the highest score from K2 to K56 (55 students);
1 1, minimum score: = min(K2:K56)- Find the minimum score from K2 to K56 (55 students);
12. Statistics of the number of people in the fractional section:
(1) = countif (K2: K56, "100")-Find the number of people whose points are100 in the area from K2 to K56; Assuming that the results are stored in K57 cells;
(2) =COUNTIF(K2:K56, "> = 95")-k57-Find the number of people with 95-99.5 points in the area from K2 to K56; Assuming that the results are stored in K58 cells;
(3)=COUNTIF(K2:K56, "> = 90")-sum (k57: k58)-Find the number of people from K2 to K56 in 90 ~ 94.5 minutes; Assuming that the results are stored in K59 cells;
(4)=COUNTIF(K2:K56, "> = 85")-sum (k57: k59)-find the number of people from K2 to K56 in 85 ~ 89.5 minutes; Assume that the result is stored in K60 cell;
(5)=COUNTIF(K2:K56, "> = 70")-sum (k57: k60)-find the number of people from K2 to K56 in 70 ~ 84.5 minutes; Assume that the result is stored in K6 1 cell;
(6)=COUNTIF(K2:K56, "> = 60")-sum (k57: k61)-find the number of people from K2 to K56 in 60 ~ 69.5 minutes; Assume that the result is stored in K62 cell;
(7) =COUNTIF(K2:K56, "& LT60")-Find the number of people below 60 points in the area from K2 to K56; Assume that the result is stored in K63 cell;
Note: COUNTIF function can also calculate the number of boys and girls in a certain area.
For example, =COUNTIF(C2:C35 1, "male")-find the number of males in C2 to C35 1 area (* * * 350);
13, excellent rate: =SUM(K57:K60)/55* 100.
14, pass rate: =SUM(K57:K62)/55* 100.
15, standard deviation: =STDEV(K2:K56) —— Find the fluctuation of grades in the area from K2 to K56 (55 students) (the smaller the value, the smaller the difference in grades of students in this class, otherwise there is polarization in this class);
16, conditional sum: =SUMIF(B2:B56, "male", K2: K56)-Assuming that column B stores the gender of students and column K stores the grades of students, the result returned by this function represents the sum of the grades of boys in this class;
17, multi-conditional sum: {= sum (if (C3:C322 = "male", if (G3:G322 = 1, 1, 0))}- Assume that the gender of students is stored in column C (C3:C322 area). This is an array function. Press CTRL+SHIFT+ENTER ({...} ") after entering." {} "cannot be entered manually, but can only be generated by key combination.
18. Automatically calculate the age according to the date of birth: = trunc ((days 360 (d3, now ()))/360,0)-Assuming that the date of birth of a student is stored in column D, the age of the student will be generated after the function is entered in column E..