Average values if cells are equal to
To average values if corresponding cells are equal to a specified value you can apply the Excel AVERAGEIF function
Example: Average values if cells are equal to
|
=AVERAGEIF(C8:C13,C5,D8:D13)
|
The formula uses the Excel AVERAGEIF function to average the numbers that are assigned to a specific value in cell C5, which in this example is "Bread", in the corresponding cells in range (C8:C13). Therefore, it averages the values in cells D8 and D11 which amounts to 790.
|
Sub Average_values_if_cells_are_equal_to()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to average values if cells are equal to
ws.Range("F8") = Application.WorksheetFunction.AverageIf(ws.Range("C8:C13"), ws.Range("C5"), ws.Range("D8:D13"))
ws.Range("F8") = Application.WorksheetFunction.AverageIf(ws.Range("C8:C13"), ws.Range("C5"), ws.Range("D8:D13"))
End Sub
OBJECTS
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.
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.
PREREQUISITES
Worksheet Name: Have a worksheet named Analyst.
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 ("C8:C13").
Worksheet Name: Have a worksheet named Analyst.
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 ("C8:C13").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F8") in the VBA code to any cell in the worksheet, that doesn't conflict with the 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 ("C8:C13") to any range in the worksheet, that doesn't conflict with the formula.
Output Range: Select the output range by changing the cell reference ("F8") in the VBA code to any cell in the worksheet, that doesn't conflict with the 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 ("C8:C13") to any range in the worksheet, that doesn't conflict with the formula.
EXPLANATION
To average values if corresponding cells are equal to a specified value you can apply the Excel AVERAGEIF function. In this example the AVERAGEIF function is used to average the numbers in range (D8:D13) that are assigned to a specific value in cell C5, which is "Bread", in the corresponding cells in range (C8:C13).
To average values if corresponding cells are equal to a specified value you can apply the Excel AVERAGEIF function. In this example the AVERAGEIF function is used to average the numbers in range (D8:D13) that are assigned to a specific value in cell C5, which is "Bread", in the corresponding cells in range (C8:C13).
FORMULA
=AVERAGEIF(range, value, average_range)
=AVERAGEIF(range, value, average_range)
ARGUMENTS
range: The range of cells you want to test the value against.
value: The value that is used to determine which of the cells, from the specified range, should be average. In this example we have specified this value to be "Bread".
average_range: The range of cells you want to average from.