Need a formula to average every other value in a column.

C

Coach J

I have columns with 100+ values. I need averages of the odd numbered rows
and then averages of the even numbered rows. Any ideas?
 
D

David Biddulph

=AVERAGE(IF(MOD(A1:A100,2),A1:A100,"")) for the odd rows
=AVERAGE(IF(MOD(A1:A100,2),"",A1:A100)) for the even rows

In each case, enter as an array formula, with Control Shift Enter.
 
G

Gary''s Student

for the even rows:

=AVERAGE(IF(MOD(A1:A100,2)=0,A1:A100)) as an array formula

for the odd rows:

=AVERAGE(IF(MOD(A1:A100,2)=1,A1:A100)) as an array formula
 
D

Dave Peterson

I'd use:

Odd Rows:
=AVERAGE(IF((ISNUMBER(A1:A100)*MOD(ROW(A1:A100),2))=1,A1:A100))

Even Rows:
=AVERAGE(IF((ISNUMBER(A1:A100)*MOD(ROW(A1:A100),2))=0,A1:A100))

Both array entered.
 
Top