Prev: SUMPRODUCT and Dates
Next: Formating cell by Comparing two cells for value
From: Bernard Liengme on 11 Mar 2010 13:56
How about making the numerator
=SUMPRODUCT(--(MOD(ROW(A7:A272),5)=2),--(A7:A272<>0),--(ISNUMBER(A7:A272)))
Seems to work on my test data
Bernard

"Mike H" <MikeH(a)discussions.microsoft.com> wrote in message
news:40138C97-2914-41A8-B4F5-6316AE958A81(a)microsoft.com...
> Bernard,
>
> While that works I think the problem is it will treat text as a numeric
> value of zero and include it in the average and I was striving for a more
> bullet proof answer. Now given the OP's data is probably numeric my
> criticism
> is probably not valid but I still think there's a better solution but I'm
> going to bed.
>
> Regards,
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Bernard Liengme" wrote:
>
>> An alternative to Mike's solution
>> =SUMPRODUCT(--(MOD(ROW(A7:A272),5)=2),--(A7:A272<>0),A7:A272)/SUMPRODUCT(--(MOD(ROW(A7:A272),5)=2),--(A7:A272<>0))
>> This is NOT an array formula
>> I tested it with some data. I used this formula and some helper columns-
>> got
>> the same answer so I have faith init
>> Note the test --(A7:A272<>0) will exclude zeros but include negative
>> values
>> Change it to --(A7:A272>0) to include only positive non-zero numbers
>> best wishes
>> --
>> Bernard Liengme
>> Microsoft Excel MVP
>> http://people.stfx.ca/bliengme
>>
>> "Erika" <Erika(a)discussions.microsoft.com> wrote in message
>> news:EED627C5-A1E4-41FC-B813-AECCDF436BCB(a)microsoft.com...
>> > Greetings! Thank you for your interest in my question, I have been
>> > bashing
>> > my
>> > head in trying to figure it out. In a column, I want to add every FIFTH
>> > cell
>> > starting with row 7 and ending with row 272. Meanwhile, I need to
>> > exclude
>> > all
>> > the cells with zero so the averaging only divides by the number of
>> > cells
>> > with
>> > a numeral. I have tried entering each 5th row individually in various
>> > formulas and I have tried defining a name and using that in the
>> > formulas
>> > but
>> > nothing has worked, I keep getting an error each time. I would really
>> > appreciate any help! Thank you.
>>
>> .
>>
First  |  Prev  | 
Pages: 1 2
Prev: SUMPRODUCT and Dates
Next: Formating cell by Comparing two cells for value