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

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

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

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" <(E-Mail Removed)> wrote in message
news:C649EB5B-5759-4CBA-A30F-(E-Mail Removed)...
> =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

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" <(E-Mail Removed)> wrote in message
> news:C649EB5B-5759-4CBA-A30F-(E-Mail Removed)...
> > =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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:B3EDC164-AE60-407F-A2FA-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
>> message
>> news:C649EB5B-5759-4CBA-A30F-(E-Mail Removed)...
>> > =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
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:B3EDC164-AE60-407F-A2FA-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in
> >> message
> >> news:C649EB5B-5759-4CBA-A30F-(E-Mail Removed)...
> >> > =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
> >>
> >>
> >>
>
>
>