Forum Discussion
Average of Every Nth Row Problem.
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.
I've been looking at the code trying to understand how it works. I understand the MOD function and Row, but I'm confused at what is happening in the IF function.
What is being multiplied? Is this a test of if the row is blank for the purpose of the AVERAGE function?
=AVERAGE(IF((MOD(ROW(C6:C1000),12)=6)*(C6:C1000<>""),C6:C1000))
MOD(ROW(C6:C1000),12) returns an array; for each cell in C6:C1000 it returns the remainder of the row number of the cell after division by 12. So for C12, C24, ... it returns 0; for C13, C25, ... it returns 1, and for C6, C18, ... it returns 6.
MOD(ROW(C6:C1000),12)=6 returns TRUE for C6, C18, ..., and FALSE for all other cells in the range.
IF((MOD(ROW(C6:C1000),12)=6)*(C6:C1000<>""),C6:C1000) returns the value of a cell in C6:C1000 if it is in row 6, 18, 30, ... and not blank. For all other cells it returns FALSE.
Since AVERAGE ignores FALSE values, we get exactly the average of the cells we need.
C6:C1000<>"" returns TRUE for each non-blank cell in the range, and FALSE for all blank cells.
By multiplying these conditions, we get an array of 1s for non-blank cells in C6, C18, ..., and 0s for all other cells (for TRUE*TRUE=1, TRUE*FALSE=0, FALSE*TRUE=0 and FALSE*FALSE=0).
- I had no idea about multiplying true and false values, that's really good to know! Thank you for explaining your work and helping me to appreciate the power of Excel some more. 🙂
Thank you Hans, this worked wonderfully. I also applied it to several other cells (adjusting the MOD) that calculated the averages for the other rows based on what you sent over.
I was worried that this might not work in Teams, but it seems to do the trick. The only downside is that the system slows down a bit when calculating each time I add another batch of data.
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.
