Average only negative numbers
To average only negative numbers in a specified range you can apply the Excel AVERAGEIF function
Example: Average only negative numbers
|
=AVERAGEIF(C5:C11,"<0")
|
The formula uses the Excel AVERAGEIF function to average only the negative numbers from the specified range (C5:C11).
|
Sub Average_only_negative_numbers()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to average only negative numbers
ws.Range("E5") = Application.WorksheetFunction.AverageIf(ws.Range("C5:C11"), "<0")
ws.Range("E5") = Application.WorksheetFunction.AverageIf(ws.Range("C5:C11"), "<0")
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.
Range: Ensure that the data you want average and test against is captured in range ("C5:C11").
Worksheet Name: Have a worksheet named Analyst.
Range: Ensure that the data you want average and test against is captured in range ("C5:C11").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Range: Select the range that you want to average and test against by changing the range ("C5:C11") to any range in the worksheet, that doesn't conflict with the formula.
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Range: Select the range that you want to average and test against by changing the range ("C5:C11") to any range in the worksheet, that doesn't conflict with the formula.
EXPLANATION
To average only negative numbers in a specified range you can apply the Excel AVERAGEIF function.
To average only negative numbers in a specified range you can apply the Excel AVERAGEIF function.
FORMULA
=AVERAGEIF(range, "<0")
=AVERAGEIF(range, "<0")
ARGUMENTS
range: The range of cells you want to average from and test the criteria against.
"<0": The criteria that is used to determine which of the cells, from the specified range, should be averaged.