AVERAGEIF |
AVERAGEIF(range, criteria [,average_range]) |
Returns the arithmetic mean of all the numbers in a range that satisfies one condition. |
range | The range of cells you want evaluated. |
criteria | The 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 |
|
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 - What is the average of all the values in the range "C1:C5" which have more than 14,000 in the range "B1:B5". (600 + 800 + 1000) 7 - This is the same as 5 except the "criteria" uses a cell reference. 8 - If the "sum_range" is left blank, then the "range" of cells is summed. 9 - 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". 10 - 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". 11 - What is the average of all the numbers in the range "C1:C5" that have the letter "o" in the range "E1:E5". 12 - What is the average of all the numbers in the range "C1:C5" that have the letter "h" in the range "E1:E5". |
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited Top