Average values if less than
To average numbers that are less than a specific value you can apply an Excel or a VBA method using the Excel AVERAGEIF function
Example: Average values if less than
|
=AVERAGEIF(C8:C14,"<"&C5)
|
The formula uses the Excel AVERAGEIF function to average the values in range (C8:C14) that have a value less than the value in cell C5.
|
Sub Average_values_if_less_than()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to average the values that have a value less than the value in cell (C5)
ws.Range("E8") = Application.WorksheetFunction.AverageIf(ws.Range("C8:C14"), "<" & ws.Range("C5"))
ws.Range("E8") = Application.WorksheetFunction.AverageIf(ws.Range("C8:C14"), "<" & ws.Range("C5"))
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 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 if the cell's value, in the specified range, is less than this value.
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 if the cell's value, in the specified range, is less than this value.
ADJUSTABLE PARAMETERS
Specific Value: Select the specific value, by changing the value in cell ("C5"), that you want to average if a cell's value, from a specified range, is less than this 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 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.
EXPLANATION
To average values that are less than a specific value you can apply an Excel or a VBA method. The formula used to average values that are less than a specific value is driven by an Excel SUMIF function.
In both the VBA and Excel examples the formula averages the values from a specified range (C8:C14) that contain a value less than 500, which is the value in cell C5. This is achieved through the use of the Excel AVERAGEIF function.
To average values that are less than a specific value you can apply an Excel or a VBA method. The formula used to average values that are less than a specific value is driven by an Excel SUMIF function.
In both the VBA and Excel examples the formula averages the values from a specified range (C8:C14) that contain a value less than 500, which is the value in cell C5. This is achieved through the use of the Excel AVERAGEIF function.
FORMULA
=AVERAGEIF(range, "< value")
=AVERAGEIF(range, "< value")
ARGUMENTS
range: The range of cells you want to test the value against and average from.
value: The value that is used to determine which of the cells should be averaged if the cells' value, from a specified range, are less than this value. If the value is referencing to a cell, as per the example in this tutorial, you need to insert the & sign before the cell reference (e.g. AVERAGEIF(range, "<"&value)).
range: The range of cells you want to test the value against and average from.
value: The value that is used to determine which of the cells should be averaged if the cells' value, from a specified range, are less than this value. If the value is referencing to a cell, as per the example in this tutorial, you need to insert the & sign before the cell reference (e.g. AVERAGEIF(range, "<"&value)).