Excel averageif function usage(7 examples, include Range is inconsistent with Average_Range)

Averageif function in excel is used to average with a Criteria, it must have at least two arguments, and can only have at most three arguments; if there are only two arguments, the first argument is both the Criteria range and Average_Range; If there are three arguments, the first argument is the Criteria range and the third argument is the Average_Range.

The Criteria range and the Average_Range in the AverageIf function can be the same size or not. If they are inconsistent, the actual average range will be determined by the Criteria range; in addition, the start cells of the two ranges must be the same, otherwise they will return error value.

I, Syntax of the Excel AverageIf function

1. Expression: AVERAGEIF(Range, Criteria, [Average_Range])

2. Description:

A. Argument Range can be either a Criteria range or an averaging range. If Average_Range is omitted, Range is the averaging area; if Average_Range is present, Range is the Criteria range.

B. Average_Range is the average range; if the ranges that are selected by Range and Average_Range are inconsistent, the Average_Range will be determined based on the Range; for example, Range is B2:B5, Average_Range is C2:C3, the actual average range is C2:C5; if the Range is B2:B3 and the Average_Range is C2:C5, the actual average range is C2:C3.

C. If there are logical values True or False in Range or Average_Range, they will be ignored. If the values in Range are all null, it will return the divisor to 0 error #DIV/0; if Range is the Average_Range and the selected range has text (refers to text that cannot be converted to a numeric value) , will return the divisor to 0 error #DIV/0. If the range selected by Average_Range has empty cells, they will be ignored.

D. Criteria can be numbers, text (such as "employees"), expressions (such as ">=2" or ">="&2) and reference to cell or cells. In addition, wildcard question marks(?) and asterisks (*) can also be used in the Criteria. The question mark indicates any character, the asterisk indicates one or a string of characters. If you want to find a question mark or an asterisk, you need to add an escape character ~ before them, such as ~?, ~*.

E. If there is an empty cell in the Criteria, it will be treated as 0; if the selected range does not have a cell that satisfies the Criteria, it will return divisor to 0 error #DIV/0!.

II, The examples of Excel AverageIf function

(I) Range is the average range

1. Double-click the cell D8, copy the formula =AVERAGEIF(D2:D7,">2000") to D8, press Enter, return to the average result 4423.6; the operation steps are as shown in Figure 1:

Figure 1

2. The Average_Range in Formula =AVERAGEIF(D2:D7,">2000") is omitted, so Range is used as the Average_Range, that is, D2:D7 is used as the Average_Range; ">2000" is Criteria, the formula means: return the average of all sales greater than 2000 in D2:D7.

(II) Range is the Criteria range and Average_Range is average range

1. Double-click the cell D8 and copy the formula =AVERAGEIF(B2:B7,"Stone fruits",D2:D7) to D8, press Enter, return to the average result 4294; the operation steps, as shown in Figure 2:

Figure 2

2. Formula =AVERAGEIF(B2:B7,"Stone fruits",D2:D7), B2:B7 is the Criteria range, "Stone fruits" is the Criteria, D2:D7 is the Average_Range; the formula means: Find all "Stone fruits" in B2:B7, and if found, return the corresponding values in D2:D7.

(III) With logical values True or False in Range and Average_Range

1. Double-click the cell C1, copy the formula =AVERAGEIF(A1:A5,TRUE,B1:B5) to C1, press Enter, return the divisor to 0 error #DIV/0!; double-click C1 again, change TRUE to 1, Press Enter to return to the averaged result 100; the operation steps are as shown in Figure 3:

Figure 3

2. The Criteria of formula =AVERAGEIF(A1:A5,TRUE,B1:B5) is TRUE. Although there is TRUE in A1 to A5, the result returns #DIV/0!, indicating that TRUE cannot be used as the Criteria because the AverageIf function ignores the logical values in Range. The Criteria of the formula =AVERAGEIF(A1:A5,1,B1:B5) is 1, the values in A2 and A4 are 1 in the A1:A5, they correspond to B2 and B4, B2 is 100, B4 is TRUE, and the average result is 100, indicating that TRUE is ignored.

(IV) The values in Range are all text or null

1. Double-click the cell D8, copy the formula =AVERAGEIF(B2:B7,"Stone fruits",D2:D7) to D8, press Enter, return to the average result 4294; select B2:B7, press Delete to delete the content in B2:B7, the value in D8 becomes divisor to 0 error #DIV/0; press Ctrl + Z to cancel, double-click D8 again, delete ",D2:D8", press Enter, return divisor to 0 error #DIV/0; input 50 in B4, the value in D8 is still #DIV/0; operation process steps, as shown in Figure 4:

Figure 4

2. When all the contents of D2:D8 are deleted, the formula =AVERAGEIF(B2:B7,"Stone fruits",D2:D7) returns the divisor to 0 error #DIV/0, indicating that the Range is empty, the AverageIf function returns a divisor to 0 error; the formula =AVERAGEIF(B2:B7, "Stone fruits") returns the divisor to 0 error #DIV/0, indicating that Range is the Criteria range and the Average_Range, if the selected range With text, the AverageIf function in excel also returns a divisor to 0.

(V) The values in Range does not meet the Criteria, and return divisor to 0 error #DIV/0!

1. Double-click the cell A8, copy the formula =AVERAGEIF(A2:A7,"litchi") to A8, press Enter, return the divisor to 0 error #DIV/0; the operation steps are as shown in Figure 5:

Figure 5

2. Formula =AVERAGEIF(A2:A7,"litchi") Find "Litchi" in A2:A7, but A2:A7 does not have "Litch", so the return divisor to 0 error #DIV/0.

(VI) Examples of using wildcards ? and *

(1) Use wildcards ?

1. Double-click the cell B9, copy the formula =AVERAGEIF(B2:B8,"?????Shirt",E2:E8) to B9, press Enter, return to the average result 892; double-click B9 again, Change the Criteria "?????Shirt" to "Pink?", press Enter, return the divisor to 0 error #DIV/0!; operation steps, as shown in Figure 6:

Figure 6

2. The Criteria is "?????Shirt" in the formula =AVERAGEIF(B2:B8,"?????Shirt",E2:E8) , meaning that the search in B2:B8 starts with any five characters and ends with "Shirt" The clothing, only B5 is eligible; the Criteria "Pink?" means looking for clothing that starts with "Pink" and has only one character after "Pink" in B2:B8. Since there is no eligible clothing, returns divisor to 0 error.

(2) Use wildcards *

1. Double-click the cell B9, copy the formula =AVERAGEIF(B2:B8,"*Shirt",E2:E8) to B9, to B9, press Enter, return to the average result 718; double-click B9 again, change the Criteria "*Shirt" to "Pink*", press Enter to return to the averaged result 661; the process steps are shown in Figure 7:

Figure 7

2. The Criteria is "*Shirt" in the formula =AVERAGEIF(B2:B8,"*Shirt",E2:E8), which means that the search in B2:B8 starts with any one or more characters and ends with "Shirt", B2, B3, B5 and B6 are eligible; the Criteria "Pink*" means looking for clothing starting with "Pink" in B2:B8, with B5, B7 and B8 meeting the Criteria.

(VII) Range is inconsistent with the Average_Range

(1) Range is greater than Average_Range

1. Double-click the cell A8, copy the formula =AVERAGEIF(B2:B7,"Stone fruits",D2:D3) to A8, press Enter, return to the average result 4294; the operation steps are as shown in Figure 8:

Figure 8

2. The Criteria Range B2:B7 is inconsistent with the Average_Range D2:D3 in the formula =AVERAGEIF(B2:B7,"Stone fruits",D2:D3), the Range is longer than the Average_Range, and the average result is 4294; the classification is "Stone fruits and corresponds to D2:D3 is only B3, its value is 2890, which means that more than B3 is included in the averaging, and 5698 in D4 which is also "Stone fruits" is also included, that is, the average range is not D2:D3, but D2:D7.

(2) Range is less than Average_Range

1. Double-click the cell B8, copy the formula =AVERAGEIF(B2:B5,"Stone fruits",D2:D7) to B8, press Enter, return to the average result 2890; the operation steps are as shown in Figure 9:

Figure 9

2. The Criteria Range B2:B5 is smaller than the Average_Range D2:D7 in the formula =AVERAGEIF(B2:B5,"Stone fruits",D2:D7), and the actual averaged range is D2:D5, from the return the average result 3500 is known.

(3) Range and Average_Range start inconsistency return error value

1. Double-click the cell B8, copy the formula =AVERAGEIF(B2:B7,"Stone fruits",D3:D5) to B8, press Enter, return to the average result 4975; double-click B8 again, change the Criteria "Stone fruits" to "Citrus", press Enter, return divisor to 0 error #DIV/0!, operation steps are as shown in Figure 10:

Figure 10

2. Criteria range B2:B7 and the Average_Range D3:D5 starting row is inconsistent int the formula =AVERAGEIF(B2:B7,"Stone fruits",D3:D5),
one is B2, another D3, this will return an error value; 4975 = D4(3450) + D7(6500), but D4 and D7 don't belong to "Stone fruits", they are under D3 and D6 that belong to "Stone fruits", because the actual Average_Range is D3:D8.

The formula =AVERAGEIF(B2:B7,"Citrus",D3:D5) returns disivor to 0 error #DIV/0!, because it averages D8.

As can be seen from the above three examples, if the Criteria Range is inconsistent with the Average_Range, their starting cells must be the same; in addition, the actual Average_Range only contains the Range corresponding to the Criteria Range.