Array Formula - Average from every other cell
 

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}

Array Formula - Average from every other cell
 

=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
--
Gary''s Student - gsnu200792

Array Formula - Average from every other cell
 

Absolutely terrific, that's nailed it!

Thanks a million Gary's Student,

Oscar

"Gary''s Student" wrote:

=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
--
Gary''s Student - gsnu200792

Array Formula - Average from every other cell
 

You are very welcome!
--
Gary''s Student - gsnu200792


"Oscar Munero" wrote:

Absolutely terrific, that's nailed it!

Thanks a million Gary's Student,

Oscar

"Gary''s Student" wrote:

=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
--
Gary''s Student - gsnu200792

Array Formula - Average from every other cell
 

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.
--
David Biddulph

"Gary''s Student" wrote in message
...
=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
--
Gary''s Student - gsnu200792

Array Formula - Average from every other cell
 

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!
--
Gary''s Student - gsnu200792


"David Biddulph" wrote:

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.
--
David Biddulph

"Gary''s Student" wrote in message
...
=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
--
Gary''s Student - gsnu200792

Array Formula - Average from every other cell
 

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.
--
David Biddulph

"Gary''s Student" wrote in message
...
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!
--
Gary''s Student - gsnu200792


"David Biddulph" wrote:

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.
--
David Biddulph

"Gary''s Student" wrote in
message
...
=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
--
Gary''s Student - gsnu200792

Array Formula - Average from every other cell
 

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.


"David Biddulph" wrote:

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.
--
David Biddulph

"Gary''s Student" wrote in message
...
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!
--
Gary''s Student - gsnu200792


"David Biddulph" wrote:

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.
--
David Biddulph

"Gary''s Student" wrote in
message
...
=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
--
Gary''s Student - gsnu200792