New multi-condition functions in Excel 2007

In Excel 2007, the AverageIf, AverageIfs, Sumifs, Countifs, and iferror Five functions are added, which can be calculated on a per-condition basis within a certain range. In particular, multiple conditional function averageifs, countifs and sumifs to our work has brought great convenience, such as in the grade of the total score table to calculate the average score of each class, passing rate and excellent rate, you do not have to first by class, divided into classes and then calculate the average and two rates. The implementation methods are as follows:

1, first in the "Ticket number" of this column cell below the blank cells into the class, and then to the "name" column below the input of the average score, pass rate and excellent rate.

2. Enter =averageifs (c2:c132, $G $: $G $132, "= 1") in the cell corresponding to the "average score" below the "language" column. (Figure 1)

AverageIfs is a function that calculates the average of a cell in a specified range using multiple criteria. Its syntax format is: AverageIfs (average_range,criteria_range1,criteria1,criteria_range2,criteria2 ...), average_range Is the range of cells for which you want to calculate averages, including numbers or names, arrays, or references that contain numbers; Criteria_range1, criteria_range2, ... refers to which range of cells is specified; Criteria1, criteria2, ... Refers to what the specific conditions are.

The average_range in the formula refers to all the achievements of the c2:c132, that is, "language". Criteria_range1 refers to the g2:g132, which is the class data, and G plus before and after the $ is for the next can drag fill another math and English discipline ($ for absolute application, drag the formula when the applied cell is not changed). Criteria1 refers to "= 1", that is, the condition for class is class one.

3. Enter =countifs (c2:c132, ">=90", $G $: $G $132, "=1")/countif ($G $: $G $132, "= 1") in the cells corresponding to the "pass rate" in the "Language" column. (Figure 2)

Countifs is the number of cells in a range that meet multiple criteria. Its grammatical format is: countifs (Range1, Criteria1,range2, criteria2 ...), Range1, Range2, ... Refers to the areas where the conditions are applied in statistics. Criteria1, criteria2, ... Refers to what the specific conditions are. The Range1 in the formula refers to the c2:c132, that is, the score of "language"; Criteria1 refers to ">=90", i.e. the condition is a fraction greater than or equal to 90; Range2 refers to the g2:g132, that is, "class" This column of data, plus $ is also to facilitate the drag to fill other disciplines; Criteria2 refers to "= 1", that is, the condition for class is class one.

The COUNTIF function is a function that counts the number of cells in a specified range that meet a given condition. The syntax format is: COUNTIF (Range,criteria), range is one or more cells to count, including numbers or names, arrays, or references that contain numbers, and null and text values are ignored. Criteria is the criteria by which statistics can be counted in the form of numbers, expressions, cell references, or text. The range in the formula refers to the g2:g132, that is, "class" All the cells in the column; the criteria refers to "= 1", which means that the class is a class of students.

4, according to the calculation of the passing rate of the rate of excellence, that is, 90 to 120 (out of 150 when 120 points above for excellence).

5, select the pass rate and excellent rate of these two lines, under the Start tab, under the Number control group, click% (Percent Style button) to convert to a percentage display. You can also click the "Add Decimal places" and "Reduce decimal places" buttons to increase or decrease the number of decimal places. (Figure 3)

In addition, we can use COUNTIFS to count the number of fractions, such as the number of people in statistical languages between 120 and 130, Input: =countifs (c2:c132, ">=120", c2:c132, "<130")