AVERAGEIF

AVERAGEIF(range, criteria [,average_range])

Returns the arithmetic mean of the values in a list, table or cell range that satisfies one condition.

rangeThe range of cells you want evaluated.
criteriaThe expression that contains the criteria.
average_range(Optional) The actual cells to average.

REMARKS
* AVERAGEIFS was added in Excel 2007 to replace this function.
* This function includes hidden cells.
* This function is not case sensitive when matching text strings.
* This function supports wildcards (? and *).
* Wildcard character: ? = a single character.
* Wildcard character: * = multiple characters.
* To include the actual wildcard characters use a tilde prefix (~?), (~*) and (~~).
* This function uses the actual cell values and not the displayed formatted values.
* If "average_range" is left blank, then the cells in the "range" are used for both the criteria and the average.
* If no cells in "average_range" meet the criteria, then #DIV/0! is returned.
* Arguments that are zero are included.
* Arguments that are logical values or text are excluded.
* If any cell references are empty, then these are excluded.
* If any cell references contain an error, then that error is returned.
* The "criteria" can contain wildcard characters.
* This is similar to the COUNTIF and SUMIF functions.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

 ABCD
1=AVERAGEIF(B1:B5,">28000") = 360007000200one
2=AVERAGEIF(B1:B5,"=14000", C1:C5) = 40014000400two
3=AVERAGEIF(B1:B5,"14000", C1:C5) = 40021000600three
4=AVERAGEIF(B1:B5,14000, C1:C5) = 40028000800four
5=AVERAGEIF(B1:B5,">14000", C1:C5) = 800360001000five
6=AVERAGEIF(B1:B5,">14000") = 28333   
7=AVERAGEIF(B1:B4,"="&SUM(B1:B4), 10) = 7000   
8=AVERAGEIF(B1:B5,">"&AVERAGE(B1:B5)) = 32000   
9=AVERAGE(IF(ISERROR(SEARCH("o", D1:D5))=FALSE,C1:C5,0)) = 280   
10=AVERAGE(IF(ISERROR(SEARCH("h", D1:D5))=FALSE,C1:C5,0)) = 120   
11=AVERAGEIF(B1:B4,">16000", C1:C4) = 700   

1 - What is the average of all the values in the range "B1:B5" which are greater than 28,000.
2 - What is the average of all the values in the range "C1:C5" which have 14,000 in the range "B1:B5".
3 - This is the same as 1 except the "criteria" uses a cell reference.
4 - This is the same as 1 except the "criteria" does not include an equal sign.
5 - This is the same as 1 except the "criteria" is not contained in speech marks.
6 - If the "sum_range" is left blank, then the "range" of cells is summed.
7 - What is the average of all the numbers in the range "B1:B4" that have (7000+14000+21000+28000)/10 in the range "B1:B4".
8 - What is the average of all the numbers in the range "B1:B5" that have a value greater than the average of the values in the range "B1:B5".
9 - What is the average of all the numbers in the range "C1:C5" that have the letter "o" in the range "E1:E5".
10 - What is the average of all the numbers in the range "C1:C5" that have the letter "h" in the range "E1:E5".

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top