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

Average only negative numbers

METHOD 1. Average only negative numbers

EXCEL

=AVERAGEIF(C5:C11,"<0")
The formula uses the Excel AVERAGEIF function to average only the negative numbers from the specified range (C5:C11).

METHOD 1. Average only negative numbers using VBA

VBA

Sub Average_only_negative_numbers()
'declare a variable
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")

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.
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").
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.

Explanation about the formula used to average only negative numbers

EXPLANATION

EXPLANATION
To average only negative numbers in a specified range you can apply the Excel AVERAGEIF function.
FORMULA
=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.