How to Average Positive Values in Every Fifth Row in Excel
Question details
The user needs a formula to calculate the average of only the positive numbers (greater than zero) that appear at a specific interval, such as every fifth row in a dataset.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Averaging data from a specific periodic row pattern (e.g., E22, E27, E32) while ensuring that any zero or negative values within those specific cells are excluded from the final calculation.
- Observed behavior
- The standard AVERAGEIF function returns an error or incorrect result because it cannot directly process noncontiguous cell patterns or evaluate isolated rows based on intervals.
Verify the starting row number of your dataset, as you will need it to correctly set the mathematical offset in the MOD function to target the right interval.
Use SUMPRODUCT and MOD Formulas
This is the most efficient method to dynamically identify every fifth row and calculate the average specifically for cells that contain positive values.
Because AVERAGEIF does not support array transformations or noncontiguous ranges natively, we combine SUMPRODUCT, MOD, and ROW to build a custom array logic. The first SUMPRODUCT calculates the total sum of the positive values in every fifth row, and the second SUMPRODUCT counts how many such values exist to find the average.
Click on the empty cell in your worksheet where you want the final average result to be displayed.
Type the following formula into the formula bar: =SUMPRODUCT((MOD(ROW(E22:E82)-1,5)=1)*E22:E82)/SUMPRODUCT((MOD(ROW(E22:E82)-1,5)=1)*(E22:E82>0)). Modify the range E22:E82 to match your specific data limits.
Press Enter to apply the formula. The cell will now display the average of values greater than zero for every fifth row.
Use a Helper Column with AVERAGEIFS
If complex array formulas are difficult to maintain or audit, using a helper column to tag the target rows is a much simpler and reliable alternative.
Filter the Data and Use SUBTOTAL
You can manually filter out unwanted rows and use the SUBTOTAL function to average only the visible cells containing positive numbers.
Easily Calculate Advanced Averages with WPS Spreadsheet
WPS Spreadsheet provides full support for complex array formulas like SUMPRODUCT and MOD, making it incredibly easy to process noncontiguous data and periodic row calculations without complex workarounds.
- 1. Open your file in WPS: Launch WPS Office and open your spreadsheet file containing the data you need to average.
- 2. Select the target cell: Click the cell where the final average result should be displayed.
- 3. Enter the formula: Paste the SUMPRODUCT and MOD array formula into the top formula bar.
- 4. Calculate the result: Press Enter to instantly evaluate the specific intervals and extract the average of the positive values.
Frequently Asked Questions
Why does AVERAGEIF return a #VALUE! error when selecting multiple single cells?
The AVERAGEIF function requires a single, continuous range of cells (like A1:A10) to evaluate its criteria. It cannot process a union of separate, noncontiguous cells (like E22, E27, E32), which triggers a #VALUE! error. Array formulas or helper columns are needed as a workaround.
How do I modify the formula to average every third row instead of every fifth?
In the formula MOD(ROW(E22:E82)-1, 5), change the divisor '5' to '3'. You must also adjust the offset '-1' depending on the starting row, ensuring that the calculation MOD(RowNumber - Offset, 3) equals 1 for your first target row.
Does the SUBTOTAL function ignore hidden rows when calculating averages?
Yes. If you use the function argument 1 (for AVERAGE) with an AutoFilter, it ignores rows hidden by the filter. If you manually hide rows by right-clicking and selecting 'Hide', you must use the argument 101 (e.g., =SUBTOTAL(101, range)) to ensure manually hidden rows are also excluded.
