Average values if greater than or equal to
To average values that are greater than or equal to a specific value you can apply an Excel or a VBA method using the Excel AVERAGEIF function
Example: Average values if greater than or equal to
|
=AVERAGEIF(C8:C14,">="&C5)
|
The formula uses the Excel AVERAGEIF function to average the number of cells in range (C8:C14) that have a value greater than or equal to the value in cell C5.
|
Dim ws As Worksheet
ws.Range("E8") = Application.WorksheetFunction.AverageIf(ws.Range("C8:C14"), ">=" & ws.Range("C5"))
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 if the cell's value, in the specified range, is greater than or equal to 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 greater than or equal to 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 about the formula used to average values if greater than or equal to formula
EXPLANATION
To average values that are greater than or equal to a specific value you can apply an Excel or a VBA method. The formula used to average values that are greater than or equal to 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) that contain a value greater than or equal to 500, which is the value in cell C5. This is achieved through the use of the Excel AVERAGEIF function.
=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 greater than or equal to 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)).