Array Formula - Average from every other cell

  • Thread starter Thread starter Oscar Munero
  • Start date Start date
O

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}
 
=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
 
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.
 
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!
 
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.
 
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.
 
Back
Top