Calculate an Average in Excel While Excluding Negative Values
Question details
Calculate the average of values in an Excel range while ignoring negative numbers, without using VBA.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs a worksheet formula that averages only positive values in selected cells.
- Observed behavior
- Negative values must not affect the calculated average; zeros can be either excluded or included depending on the formula criteria.
Confirm that the cells to be averaged contain real numbers rather than numbers stored as text. Decide whether zero should be excluded with a >0 criterion or included with a >=0 criterion.
Use AVERAGEIF to average only positive values
Use this simple formula when the values are in one range and you want to exclude all negative numbers without VBA.
AVERAGEIF evaluates the range against a condition and averages only the values that meet it. It is broadly compatible with Excel versions and avoids the need for dynamic-array functions.
Click the empty cell where you want the average to appear, such as F2.
Type =AVERAGEIF(A2:E13,">0") and press Enter. Replace A2:E13 with the range containing your values.
To average zero and positive values while still excluding negatives, change the criterion to =AVERAGEIF(A2:E13,">=0").
Use FILTER and TOCOL in Microsoft 365 or Excel for the web
Use this option when you want to flatten a multi-row or multi-column range into one list before averaging positive values.
Average Positive Values Easily in WPS Spreadsheets
WPS Spreadsheets lets you calculate averages with criteria-based formulas directly in your worksheet. Use AVERAGEIF to exclude negative values, adjust the condition to include zero when required, and keep your Excel workbooks ready to share.
- 1. Open the worksheet in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the .xlsx or .xls workbook containing the values.
- 2. Select an output cell: Click an empty cell where you want the calculated average to appear.
- 3. Enter the AVERAGEIF formula: Type =AVERAGEIF(A2:E13,">0"), replacing A2:E13 with the range to evaluate.
- 4. Confirm the result: Press Enter to calculate the average using only positive values; use ">=0" if zeros should be included.
Frequently Asked Questions
What Excel formula calculates an average without negative numbers?
Use =AVERAGEIF(A2:E13,">0"). It averages only values greater than zero in the specified range.
How do I include zero but exclude negative values from an average?
Use the criterion ">=0", for example: =AVERAGEIF(A2:E13,">=0"). This includes zero and positive values only.
Can AVERAGEIF work with a non-contiguous selection?
AVERAGEIF accepts one continuous range. For separate ranges, calculate each range separately or combine the values into a helper range before applying the formula.
Why does my average formula return #DIV/0!?
The error appears when no cells meet the criterion. Check that the range contains numeric values greater than zero, or change the criterion if zeros should be included.
Does the FILTER and TOCOL average formula work in all Excel versions?
No. FILTER and TOCOL require Microsoft 365 or Excel for the web. For older Excel versions, use AVERAGEIF for a single continuous range.
