Cross-Year averages by Quarter

Hi all,

Many thanks in advance for your help.

I wish to take an average of every 4th cell in a column--I've tried a few things that haven't panned out. Essentially, I have quarterly data for a number of years, and I wish to have cross-year averages of Q1, Q2, Q3, and Q4. The quarters are in column A, with a repeating Q1,Q2,Q3,Q4 pattern, the data are in column B. I'm looking for a formula, not a pivot table.

Thanks again.

Try this array* formula:

=AVERAGE(IF(MOD(ROW(B$2:B$20)-2,4)+1=1,B$2:B$20))

for quarter 1, assuming the data starts on row 2. For other quarters change the =1 to =2, =3, =4 etc.

*An array formula must be committed using the key combination of Ctrl-Shift-Enter instead of the usual <Enter>.

Hope this helps.

Pete

Thanks, Pete.

I think I'm on the right track--at least, it's working for Q1. The rest of them are off, though. My data begin in row 3, so I've adjusted the formula to

=AVERAGE(IF(MOD(ROW(B$3:B$20)-3,4)+1=1,B$3:B$20)

. Again, this works for Q1, but is off thereafter. Many thanks for your help!

AHA! Great success! I suspected that the discrepancy might have to do with your excellent code's handling of blank cells. We have Q1 data for 2012, but nothing for the other quarters (they're blank). The formula handles these blanks differently from the =AVERAGE(B3,B7,B11,15,...), such that simply adding zeroes to the blank cells cured the problem. If there is a smarter way to do it, I'd love to know.

Incidentally, it took me a while to piece together what your code was doing. I'm still not certain if there's a principle behind doing it as you did

=AVERAGE(IF(MOD(ROW(B$2:B$20)-2,4)+1=1,B$2:B$20)

instead of like this

=AVERAGE(IF(MOD(ROW(B$2:B$20)-2,4)=0,B$2:B$20)

for Q1, like this

=AVERAGE(IF(MOD(ROW(B$2:B$20)-2,4)=2,B$2:B$20)

for Q2, and so on. It seems to work without the +1 if you step the =x down commensurately, but using it that way does make adjusting the =1, =2, =3, and =4 for Q1, Q2, Q3, and Q4 more "parallel". However, it certainly didn't make it more intuitive to understand the code--but I am slow, and just learning how to do these things.

Thanks again for your very useful help.

As an aside, I just discovered that it also works to use a "search" type feature, since all of my data points have a Q1-Q4 row designation. It looks like this:

{=AVERAGE(IF($Q$3=$B$3:$B$86,$I$3:$I$86))}

This method might be more flexible for applications when one is moving or sorting the data, as it works independently of the row number. Still requires Ctrl-Shift-Enter to set it as an array formula.

The reason I had +1 in the formula is so that this is compared with =1, =2, =3, =4 etc, i.e. directly with the quarter numbers. If you omit the +1 then the values are 0, 1, 2, and 3, so it is less intuitive as to why 0 is equivalent to Q1.

Also, AVERAGE ignores blank cells - nothing to do with my clever coding !! <bg>

Anyway, glad you got it sorted in the end - please mark the thread as Solved (the FAQ describes how), and maybe cklick the "star" icon if you want to pass on thanks directly.

Pete

Originally Posted by Pete_UK

The reason I had +1 in the formula is so that this is compared with =1, =2, =3, =4 etc, i.e. directly with the quarter numbers. If you omit the +1 then the values are 0, 1, 2, and 3, so it is less intuitive as to why 0 is equivalent to Q1.

Also, AVERAGE ignores blank cells - nothing to do with my clever coding !! <bg>

Anyway, glad you got it sorted in the end - please mark the thread as Solved (the FAQ describes how), and maybe cklick the "star" icon if you want to pass on thanks directly.

Pete

That's what I suspected. I definitely wasn't blaming your coding for the omission of blank cells, either--and I very much appreciate your response. I'll think through your code some more and see if I can apply it to other problems I face; I'm still trying to think through array formulas and how and when they work.

Many thanks.