Average values if contains specific value
To average values if corresponding cells contain a specific value you can apply an Excel or a VBA method using the Excel AVERAGEIF function
Example: Average values if contains specific value
|
=AVERAGEIF(B8:B14,"*"&C5&"*",C8:C14)
|
The formula uses the Excel AVERAGEIF function and the asterisk (*) to average the values in range (C8:C14) when the values in range (B8:B14) contain "re", which is specified in cell C5. The asterisk (*) represents one or more characters.
|
Dim ws As Worksheet
ws.Range("E8") = Application.WorksheetFunction.AverageIf(ws.Range("B8:B14"), "*" & ws.Range("C5") & "*", ws.Range("C8:C14"))
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 Analysis.
Data Range: Ensure that the data that you want to average from is captured in range ("C8:C14") in the Analysis worksheet.
Specific Value: Input the specific value in cell ("C5") in the Analysis worksheet that you want to average when the values in range ("B8:B14") contain that specific value.
ADJUSTABLE PARAMETERS
Specific Value: Select the specific value, by changing the value in cell ("C5"), that you want to average when the values in range ("B8:B14") contain that specific value. Alternatively, you can replace ws.Range("C5") in the VBA code with the specific value or a defined name that represents the specific value that you want to test for.
Date Range: Select the range that you want test the criteria against by changing range ("B8:B14") to any range in the worksheet that doesn't conflict with the formula.
Average Range: Select the range that you want to average from by changing range ("C8:C14") to any range in the worksheet that doesn't conflict with the formula.
Output Range: Select the output range by changing the cell reference ("E8") to any cell in the worksheet, that doesn't conflict with the formula.
To average values if corresponding cells contain a specific value you can apply an Excel or a VBA method. The formula used to average values if corresponding cells contain a specific value is driven by an Excel AVERAGEIF function.
In both the VBA and Excel examples the formula averages the values from a specified range (C8:C14) when the values in range (B8:B14) contain "re", which is the value in cell C5. This is achieved through the use of the Excel AVERAGEIF function and the asterisk (*) wildcard.
=AVERAGEIF(range, *value*, average_range)
range: The range of cells you want to test the value against and average from.
average_range: The range of cells you want to average from.
value: The value that is used to determine which of the cells should be averaged from the "average_range" when the values in "range" contain the value. If the value is referencing to a cell, as per the example in this tutorial, you need to insert the "*"& and &"*" signs before and after the cell reference (e.g. AVERAGEIF(range, "*"&value&"*", average_range)).