Calculation method of age in excel _excel common function formula encyclopedia
Calculation method of age in excel:
Age calculation steps 1: In the following table, column A is the name, column B is the date of birth, and column C is the age, which is the age we want to calculate.
Step 2: Age calculation: In the case that English input method is case-insensitive, enter the formula "=DATEDIF(B2, NOW ()," y ")" in cell C2, as shown below.
Step 3 of age calculation: Enter again when filling in the form to get the age of the persons listed in A2.
Step 4: Select C2 cell and drag column C to get the age of the people you need.
Note: This algorithm takes the year as an example! Others, such as length of service and teaching experience, can be the same.
Excel common function formula
First, conditional judgment: IF function.
Objective: To judge the grade of grades.
Method:
1, select the target cell.
2. Enter the formula in the target cell: = IF(C3 & gt；; =90, "excellent", if (C3 >; =80, "good", if (C3 >; =60, "Pass", "Fail").
3.Ctrl+Enter filling.
Explanation:
IF function is a conditional judgment function, which returns the corresponding value according to the judgment result. If the judgment condition is true, the first parameter is returned, and if it is false, the second parameter is returned.
Excel function formula: the most commonly used 12 function formula in work.
Second, conditional summation: SUMIF and SUMIFS functions.
Objective: To find the total score of boys and the total score of boys above 80.
Method:
1. Enter the formula =SUMIF(D3:D9, "male", C3:C9) or =SUMIFS(C3:C9, C3:C9, "& gt=80", D3:D9, "male").
Explanation:
1, SUMIF function is used for single condition summation. There can only be one summation condition. The easy-to-understand grammatical structure is SUMIF (conditional scope, condition and scope).
2.SUMIFS function is used for multi-condition summation. There can be multiple summation conditions. Easy-to-understand grammatical structure: SUMIFS (sum range, condition 1 range, condition 1 range, condition 2 range, condition 2, ... condition n range, condition n).
Excel function formula: the most commonly used 12 function formula in work.
Third, conditional counting: COUNTIF and COUNTIFS functions.
Objective: To calculate the number or achievement of boys >: =80 points.
Method:
1. enter the formula =COUNTIF(D3:D9, "male") or =COUNTIFS(D3:D9, "male", C3:C9, "& gt=80").
Explanation:
1 and COUNTIF functions are used for single condition counting, and there can only be one counting condition. The easy-to-understand grammatical structure is: COUNTIF (conditional range, condition).
2. The 2.COUNTIFS function is used for multi-condition counting, and there can be multiple counting conditions. The easy-to-understand grammatical structure is: COUNTIFS (condition range 1, condition range 1, condition range 2, condition 2 ... condition range n, condition n).
Excel function formula: the most commonly used 12 function formula in work.
Fourth, data query: VLOOKUP function.
Objective: To inquire the corresponding level of relevant personnel.
Method:
Enter the formula =VLOOKUP(H3, B3: C9,2,0) in the target cell.
Explanation:
The basic function of VLOOKUP is data query. The easy-to-understand grammatical structure is VLOOKUP (the value of the search, the search range, which column in the search range, exact match or fuzzy match).
Excel function formula: the most commonly used 12 function formula in work.
5. Reverse query: search function.
Objective: To inquire the corresponding student number according to the students' names.
Method:
Enter the formula = lookup (1, 0/(B3: B9 = H3), A3: A9) in the target cell.
Explanation:
The syntax structure of the formula LOOKUP function is lookup (lookup value, lookup condition, return value range). The usage of bit variation used in this example. The value of the search is 1, and the condition is 0. According to the characteristics of LOOKUP function, if LOOKUP function cannot find lookup_value, it will match the maximum value of lookup_vector that is less than or equal to lookup_value.
Excel function formula: the most commonly used 12 function formula in work.
Six, query a good partner: index+matching function
Objective: To inquire the corresponding grade according to the name.
Method:
Enter the formula =INDEX(E3:E9, MATCH(H3, B3: B9,0)) in the target cell.
Explanation:
1, INDEX function: Returns the intersection value of rows and columns in a given range.
2.MATCH function: gives the position of the specified value within the specified range.
3. Formula: =INDEX(E3:E9, MATCH(H3, B3: B9,0)), query the value of the MATCH(H3, B3: B9,0) line in E3:E9, and return it.
Excel function formula: the most commonly used 12 function formula in work.
Seven, extract the date of birth: TEXT+MID function.
Objective: To extract the departure date from the specified ID number.
Method:
1, select the target cell.
2. Input formula: =TEXT(MID(C3, 7, 8), "00-00-00").
3.Ctrl+Enter filling.
Explanation:
1. Use the MID function to extract a string of length 8 from the seventh cell of C3.
2. Use the TEXT function to convert the format of characters into "00-00-00" format and 1965-08-2 1.
Excel function formula: the most commonly used 12 function formula in work.
Eight, calculate the age: DATEDIF function.
Objective: To calculate the corresponding age according to the given ID number.
Method:
1, select the target cell.
2. Input formula: = datedif (text (mid (C3, 7, 8), "00-00-00"), today (), "y")&; "One year old".
3.Ctrl+Enter filling.
Explanation:
1. Use MID to get an 8-length string from the seventh cell of C3.
2. Use the Text function to convert the string into: 00-00-00 format. And 1965-08-2 1.
3. Use the DATEDIF function to calculate the year (y) different from the current date (TODAY ()).
Excel function formula: the most commonly used 12 function formula in work.
Nine, Chinese ranking: SUMPRODUCT+COUNTIF function.
Objective: To rank the grades.
Method:
1, select the target cell.
2. Enter the formula = sum product ((c $3: c $9 > C3)/COUNTIF (Canadian 3: Canadian 9, Canadian 3: Canadian 9))+ 1.
3.Ctrl+Enter filling.
Explanation: the first half of the formula (C $ 3:C $9 >；; C3) Returns an array, and the number of cells in the C$3:C$9 area is greater than C3. The second half of COUNTIF (C$3:C$9, C$3:C$9) can be understood as: X 1/COUNTIF(C$3:C$9, C$3:C$9), and the formula COUNTIF(C$3:C$9, c $ C3) X 1).
Related articles of age calculation method in excel:
★ The method of calculating age and one year old by using the function formula in Excel.
★ excel table How to calculate age with function formula
The method of calculating age with year function in Excel.
★ Operation method of calculating age by using date-time function in Excel
★ excel methods and steps for calculating age with functions
★ Tutorial of Finding Age Formula with Excel Form
★ Excel function How to use DATEDIF to calculate age
★ Excel common spreadsheet formula daquan summary
★ excel table function formula daquan
★ The most commonly used Excel function formula daquan
var _ HMT = _ HMT | |[]； (function(){ var hm = document . createelement(" script ")； hm.src = "/hm.js？ 1 fc3c 5445 c 1 ba 79 CFC 8 B2 d 8 178 C3 C5 DD "； var s = document . getelementsbytagname(" script ")[0]； s.parentNode.insertBefore(hm，s)； })();