Excel AVERAGEIF Function
The Excel AVERAGEIF function returns the average of the numbers in a specified range based on a single criteria
Example: Excel AVERAGEIF Function
|
=AVERAGEIF(B8:B13,"Shop A",D8:D13)
|
Result in cell F8 (707) - returns the average number in range (D8:D13) that is associated with Shop A from range (B8:B13).
|
|
=AVERAGEIF(B8:B13,C5,D8:D13)
|
Result in cell F8 (707) - returns the average number in range (D8:D13) of numbers that are associated with the value in cell (C5) from range (B8:B13).
|
METHOD 3. Excel AVERAGEIF function using the Excel built-in function library with hardcoded values
EXCEL
| =AVERAGEIF(C5:C10,"Bread",D5:D10) Note: in this example we are averaging the numbers in range (D8:D13) that are associated with Shop A from range (B8:B13). |
 |
METHOD 4. Excel AVERAGEIF function using the Excel built-in function library with links
EXCEL
| =AVERAGEIF(B8:B13,C5,D8:D13) Note: in this example we are averaging the numbers in range (D8:D13) that are associated with the value in cell (C5) from range (B8:B13). |
 |
Dim ws As Worksheet
ws.Range("F8") = Application.WorksheetFunction.AverageIf(ws.Range("B8:B13"), "Shop A", ws.Range("D8:D13"))
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named AVERAGEIF.
Average Range: Ensure that the data you want average is captured in range ("D8:D13")
Range: Ensure that the corresponding range to the average range is captured in range ("B8:B13")
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the range reference ("F8") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Average Range: Select the range that you want to average by changing the range ("D8:D13") to any range in the worksheet, that doesn't conflict with the formula.
Range: Select the corresponding range to the average range by changing the range ("B8:B13") to any range in the worksheet, that doesn't conflict with the formula.
Dim ws As Worksheet
ws.Range("F8") = Application.WorksheetFunction.AverageIf(ws.Range("B8:B13"), ws.Range("C5"), ws.Range("D8:D13"))
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named AVERAGEIF.
Average Range: Ensure that the data you want average is captured in range ("D8:D13")
Range: Ensure that the corresponding range to the average range is captured in range ("B8:B13")
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the range references ("F8") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Average Range: Select the range that you want to average by changing the range ("D8:D13") to any range in the worksheet, that doesn't conflict with the formula.
Range: Select the corresponding range to the average range by changing the range ("B8:B13") to any range in the worksheet, that doesn't conflict with the formula.
The Excel AVERAGEIF function returns the average of the numbers in a specified range based on a single criteria.
=AVERAGEIF(range, criteria, [average_range])
range: (Required) The range of cells you want to test the criteria against.
criteria: (Required) The criteria that is used to determine which of the cells, from the specified range, should be averaged.
average_range: (Optional) The range of cells you want to average from.
ADDITIONAL NOTES
Note 1: The AVERAGEIF function allows the use of logical elements (>,<,<>,=).
Note 2: The AVERAGEIF function allows the use of wildcards:
* searches to find a match for the sequence of characters.
? searches to find a match to any single character.
Note 3: When the average_range argument is omitted the function use the range argument as the range of cells to average from.