Forum Discussion
Average of Every Nth Row Problem.
How do I display the average of the values from every 12 rows of column C starting from row 6? (I.e. rows 6, 18, 30, 42...) I want the average to be displayed in cell J6. I have tried to figu...
Formula:
=AVERAGE(IF((MOD(ROW(C6:C1000),12)=6)*(C6:C1000<>""),C6:C1000))
- If your data extend below row 1000, adjust the ranges in the formula; it doesn't matter if the data range is smaller.
- If you don't have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.
AVERAGE ignores blanks, so not sure you need the part (C6:C1000<>"")? Also, I find it's better to generalize these types of solutions, rather than make them row-dependent. For example, what if the OP changed the source range from C6:C1000 to, say, C4:C998? Would they necessarily know to change the 6 to a 4 for MOD?
As such, I would prefer
=AVERAGE(IF(MOD(ROW(C6:C1000)-MIN(ROW(C6:C1000)),12)=0,C6:C1000))
For interest's sake, for O365 we can also use a set-up without MOD:
=AVERAGE(TAKE(WRAPROWS(C6:C1000,12),,1))
Regards
It first tried it without (C6:C1000<>""). Excel converted blanks to zeros that were included in the AVERAGE calculation, skewing the result.
- Ah, yes, of course. Thanks.