Average values if cells are not equal to
To average values if corresponding cells are not equal to a specified value you can apply the Excel AVERAGEIF function
Example: Average values if cells are not equal to
|
=AVERAGEIF(C8:C13,"<>"&C5,D8:D13)
|
The formula uses the Excel AVERAGEIF function to average the numbers that are not 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 D9, D10, D12 and D13 which amounts to 643. If you want to hardcode the specific value to test against in the formula you will need to insert the value in the double quotation mark and remove the & sign (e.g. =AVERAGEIFS(C8:C13,"<>Bread",D8:D13).
|
Sub Average_values_if_cells_are_not_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 not 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 not 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 not 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 not 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 not 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 criteria against.
value: The value that is used to determine which of the cells, from the specified range, should not be averaged. In this example we have specified this criteria to be "Bread". This formula assumes you are using a cell reference as the value, however, if you want to hardcode the value in the formula you will need to insert the value in the double quotation mark and remove the & sign (e.g."<>Bread").
average_range: The range of cells you want to average from.