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. 🙂