Summary of the most commonly used formula arithmetic techniques in Excel

We often use Excel to process tables and data, in the process of processing tables and data, the use of formulas and functions, the following we have to organize some of the common Excel formulas and the use of methods, I hope to help you.

Find a duplicate content formula: =if (COUNTIF (A:AA2) >1 "Repeat" "").

Second, the date of birth to calculate the age formula: =trunc (DAYS360 (H6 "2009/8/30″false)"/3600).

Three, from the input of the 18-digit date of birth calculation formula: =concatenate (mid E274)/mid (E2112)/mid (E2132)).

Four, from the input of the ID card number within the system to automatically extract gender, you can enter the following formula: =if (LEN (C2) =15if (mod (Mid (C2151) 2) =1 "male" "female") IF (mod (Mid (C2171) 2) =1 "male" "female") "C2" in the formula Represents the cell that entered the ID number.

V. Sum: =sum (K2:K56)--The summation of K2 to K56 area;

VI. Average: =average (k2:k56)-the average for the K2 K56 region;

Vii. rank: =rank (k2,k$2:k$56)--ranked the scores of 55 students;

Viii. Grade: =if (k2>=85 "excellent" if (k2>=74 "Liang" if (k2>=60 "pass" "Fail"))

Ninth, semester overall comments: =k2*0.3+m2*0.3+n2*0.4--assumed that K-columns, m columns and n columns were stored in the student's "normal rating", "Midterm", "final" three results;

Ten, Highest score: =max (k2:k56)--K2 to K56 region (55 students) the highest score;

Xi. min: =min (k2:k56)--the lowest score for K2 to K56 area (55 students);

12, the number of fractional segment statistics:

(1) =countif (k2:k56 "100″")--the number of K2 to K56 area 100; Suppose the result is stored in K57 cell;

(2) =countif (k2:k56 ">=95″")-k57--the number of K2 K56 to 95~99.5 area; Suppose the result is stored in K58 cell;

(3) =countif (k2:k56 ">=90″")-sum (k57:k58)-The number of K2 K56 to 90~94.5 area; Suppose the result is stored in K59 cell;

(4) =countif (k2:k56 ">=85″")-sum (k57:k59)-The number of K2 K56 to 85~89.5 area; Suppose the result is stored in K60 cell;

(5) =countif (k2:k56 ">=70″")-sum (K57:K60)-The number of K2 K56 to 70~84.5 area; Suppose the result is stored in K61 cell;

(6) =countif (k2:k56 ">=60″")-sum (k57:k61)-The number of K2 K56 to 60~69.5 area; Suppose the result is stored in K62 cell;

(7) =countif (k2:k56 "<60″")-the number of K2 to the K56 region below 60; Suppose the result is stored in a K63 cell;

Note: The COUNTIF function can also calculate the number of males and females in a given region.

such as: =countif (c2:c351 "male")--C2 to C351 region (a total of 350) the number of men;

13, Excellent rate: =sum (k57:k60)/55*100

14. Passing Rate: =sum (k57:k62)/55*100

XV, Standard deviation: =stdev (k2:k56)-K2 to K56 region (55 people) of the performance fluctuations (the smaller the number, indicating that the students of the class differences in performance, on the contrary, that the class exists polarization);

16, the condition sum: =sumif (b2:b56 "male", k2:k56)--assumes that B column holds the student's sex, the K column holds the student's score, then this function returns the result to ask the class boy's result the sum;

17. Multiple conditions sum: {=sum (if (c3:c322= "man" if (g3:g322=110))}--assumes C column (c3:c322 area) holds student's sex, g column (g3:g322 area) holds student's class code (1, 2, 3, 4, 5), The result returned by this function indicates the number of boys in the class; This is an array function, after the loss to press Ctrl+shift+enter key combination (produce "{...)". ｝”)。 "{}" cannot be entered manually and can only be produced with a key combination.

18, automatically calculated according to the date of birth: =trunc (DAYS360 (D3now ())/3600)

——— assume that the D column holds the student's birth date, and the e-column enters the function to produce the same age as the child.