Array Formula - Average from every other cell

[Oscar Munero]

Hi,

I have data in A2:A325, A2,A4,A6 etc represents length and A3,A5,A7 etc
represents breadth.

I am trying to calculate average length and average breadth, so I want to
just average A2,A4,A6 etc and seperately want to average A3,A5,A7 etc.

I've tried using the array formulas below but with little success, can
anyone point out where I'm going wrong or offer an alternative?

=AVERAGE(IF(MOD(A2:A324,2),"",A2:A324)) gives #DIV/0!
=AVERAGE(IF(MOD(A3:A325,2),A3:A325,"")) gives an answer I'm not sure is
correct

Thanks,
Oscar.

PS. I have used [Ctrl][Shift][Enter] to get {around array}

 

[Gary''s Student]

=AVERAGE(IF(MOD(ROW(A1:A325),2)=0,A1:A325,0))
=AVERAGE(IF(MOD(ROW(A1:A325),2)=1,A1:A325,0))
both array entered

 

[Oscar Munero]

Absolutely terrific, that's nailed it!

Thanks a million Gary's Student,

Oscar

 

[Gary''s Student]

You are very welcome!

 

[David Biddulph]

Doesn't that throw in a lot of zeroes to the average, both for the alternate
rows and for any blank input cells? It doesn't seem to give the right
answer for me.

What does seem to give the right answer (on limited testing) is
=AVERAGE(IF(MOD(ROW(A1:A325),2)=0,IF(A1:A325="","",A1:A325),""))
=AVERAGE(IF(MOD(ROW(A1:A325),2)=1,IF(A1:A325="","",A1:A325),""))
both array entered.

 

[Gary''s Student]

You are correct!!
I forgot that AVERAGE ignores blanks, not zeros. My formulas should have
been:

=AVERAGE(IF(MOD(ROW(A1:A325),2)=0,A1:A325,""))
=AVERAGE(IF(MOD(ROW(A1:A325),2)=1,A1:A325,""))

Thanks for the correction!

 

[David Biddulph]

Doesn't your formula still have problems where there are blank cells in the
input range? Doesn't your formula effectively turn those into zeroes?
That's why I had my additional test for blank inputs.

 

[Oscar Munero]

Hi Guys,

I tried both your suggestions (Gary''s Students' amended version) and I'm
getting the same answer for average from each method.

I can see where David Biddulph is coming from though, if I extend the range
to include e.g. A1:A330 (5 blank cells) then the 2 suggested arrays begin to
return different average answers.

Luckily my original data set has neither blanks nor zeroes, so both methods
work just fine.

Thanks to you both for increasing my understanding of how arrays work,

Oscar.