How to average only positive or negative numbers in Excel?
In Excel, calculating the average of only positive or only negative numbers in a range containing both types of values is a common requirement, especially when you need to analyze gains vs. losses, profits vs. expenses, or other scenarios where positive and negative numbers have different interpretations. If you use the standard AVERAGE function, it will include both positive and negative numbers in its calculation, which may not be what you need. This tutorial explores practical methods to average only positive or only negative numbers in Excel, offering step-by-step instructions for formulas, built-in tools, and VBA code to meet various user needs.
Average only positive or negative numbers with formulas
View the average of only positive or negative numbers with Kutools for Excel 
Automatically calculate the average of only positive or negative numbers with VBA Code
Average only positive or negative numbers with formulas
To average only the positive numbers in a range, Excel provides array formulas that selectively include only the values matching your specified condition. This is appropriate when you do not wish to use add-ins or additional tools and want to quickly compute the average using formulas within the worksheet itself.
1. Enter the following formula in a blank cell where you want the result to appear:
=AVERAGE(IF(A1:D10>0,A1:D10,""))In this example, A1:D10 represents the data range containing both positive and negative numbers.
2. After entering the formula, press Ctrl + Shift + Enter simultaneously to confirm it as an array formula. If done correctly, curly braces { } will appear around your formula in the formula bar, as shown below:
Formula explanation and adaptability:
- This technique works for both horizontal and vertical ranges. Adjust the range to suit your worksheet.
- If there are no positive numbers in your range and you use this formula for positive values, the result will display a #DIV/0! error, since there are no qualifying numbers to average. The same applies for negative numbers when using the negative formula shown below. You can avoid this by wrapping the formula in
IFERRORfor more robustness:
=IFERROR(AVERAGE(IF(A1:D10>0,A1:D10,"")), "")To calculate the average of only the negative numbers, use the following formula:
=AVERAGE(IF(A1:D10<0,A1:D10,""))- Remember to press Ctrl + Shift + Enter after entering the formula for it to work properly.
Notes:
1. A1:D10 is the range you want to calculate the conditional average for; adjust as needed for your data.
2. If you want the average to ignore zero or specific values, you can further customize the logical test within the formula.
3. You can use this approach in Excel 365 or Excel 2019 and later versions without pressing Ctrl + Shift + Enter, as dynamic arrays are supported natively. For earlier versions, you must use the key combination for array formulas.
4. If your data includes errors (such as #DIV/0! or #N/A), the formula may return errors as well. Consider using the IFERROR function to handle exceptions gracefully.
View the average of only positive or negative numbers with Kutools for Excel
If you have installed Kutools for Excel, its Select Specific Cells feature allows you to quickly select only the positive or only the negative numbers within a range. The average is then conveniently displayed directly in the Excel status bar, eliminating the need to enter extra formulas. This method is especially suitable for users who prefer a visual and interactive way to summarize specific types of data without complex formulas.
1. Select the data range that includes both positive and negative numbers you wish to analyze.
2. Go to Kutools > Select > Select Specific Cells as shown below:
3. In the Select Specific Cells dialog box, perform the following:
- Select the Cell option under Selection type.
- Set the condition for positive numbers by choosing Greater than in the Specific type drop-down, and enter 0 in the value field.
- For negative numbers, choose Less than and again enter 0.
Click OK, and Kutools will automatically select cells matching your criteria and a dialog will confirm the selected cells.
4. After cells are selected, you can simply look at the Excel status bar at the bottom right of the window to see the average value of the selected cells. This calculation is updated in real-time and no formula input is necessary.
 |  |
| The result of only the positive numbers | The result of only the negative numbers |
Advantages and considerations:
- No need to write or remember formulas—ideal for quick, on-the-fly analysis.
- The method works well for small to medium datasets, but if your selection includes many non-numeric cells or errors, manually review the status bar summary to ensure accuracy.
- You can also right-click the status bar to customize which calculations (average, sum, count, etc.) you wish to display.
Demo: Sum/Average/Count Positive Or Negative Numbers Only with Kutools for Excel Kutools for Excel: Over 300 handy tools at your fingertips! Enjoy AI-powered features for smarter and faster work! Download Now!
Automatically calculate the average of only positive or negative numbers with VBA Code
For users who frequently need to compute these averages for different ranges or wish to automate the process, using a simple VBA macro can save time and improve accuracy. This approach is best if you have repetitive tasks or complex data layouts, and are comfortable using the Visual Basic for Applications (VBA) editor in Excel.
1. Click Developer Tools > Visual Basic to open the Microsoft Visual Basic for Applications window. In the editor, click Insert > Module, then copy and paste one of the following codes into the new module.
To calculate the average of only positive numbers in a selected range, use the following macro:
Sub AveragePositiveNumbers()
Dim rng As Range
Dim cell As Range
Dim sum As Double
Dim count As Long
Dim result As Variant
xTitleId = "KutoolsforExcel"
On Error Resume Next
Set rng = Application.Selection
Set rng = Application.InputBox("Please select the range to average positive numbers", xTitleId, rng.Address, Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
sum = 0
count = 0
For Each cell In rng
If IsNumeric(cell.Value) And cell.Value > 0 Then
sum = sum + cell.Value
count = count + 1
End If
Next cell
If count > 0 Then
result = sum / count
MsgBox "The average of only the positive numbers is " & result, vbInformation, xTitleId
Else
MsgBox "No positive numbers found in the selected range.", vbExclamation, xTitleId
End If
End SubTo calculate the average of only negative numbers, use the code below:
Sub AverageNegativeNumbers()
Dim rng As Range
Dim cell As Range
Dim sum As Double
Dim count As Long
Dim result As Variant
xTitleId = "KutoolsforExcel"
On Error Resume Next
Set rng = Application.Selection
Set rng = Application.InputBox("Please select the range to average negative numbers", xTitleId, rng.Address, Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
sum = 0
count = 0
For Each cell In rng
If IsNumeric(cell.Value) And cell.Value < 0 Then
sum = sum + cell.Value
count = count + 1
End If
Next cell
If count > 0 Then
result = sum / count
MsgBox "The average of only the negative numbers is " & result, vbInformation, xTitleId
Else
MsgBox "No negative numbers found in the selected range.", vbExclamation, xTitleId
End If
End Sub2. After entering the macro, return to Excel. Press F5, or click Run. In the dialog that pops up, you can select the range for your calculation, and the average (or a warning if no qualifying numbers are found) will display in a message box.
Tips and troubleshooting:
- Be sure to save your workbook as a macro-enabled file (
.xlsm) if you want to keep and reuse your macros. - This macro only averages numerical cells—cells with text, empty cells, or errors are ignored automatically.
- If your data set includes very large amounts of data or frequent changes, automating with VBA helps avoid manual errors and saves time.
- If you encounter a macro security warning, adjust your macro settings under Excel Options > Trust Center to allow macros to run.
When choosing a method, consider your own workflow and Excel skills:
- Formulas are fast and flexible but require array entry and correct referencing.
- Kutools is efficient for interactive tasks and avoids manual formula entry.
- VBA Macros suit recurring or automated reporting environments.
If you experience calculation errors or unexpected results, check that your ranges do not contain unwanted data types or errors, confirm you are applying the correct condition (greater or less than zero), and adjust references as needed. For more advanced exclusions (like ignoring errors or certain values), nest appropriate error-handling or filtering logic in your formulas, or VBA.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in