Averaging every 5th cell while omitting zeros

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.

Erica,

I don't like this formula but it works until someone comes up with something
better. It's an ARRAY see below on how to enter it

=AVERAGE(IF(ISNUMBER(MATCH(ROW(A7:A272),{7,12,17,2 2,27,32,37,42,47,52,57,62,67,72,77,82,87,92,97,102 ,107,112,117,122,127,132,137,142,147,152,157,162,1 67,172,177,182,187,192,197,202,207,212,217,222,227 ,232,237,242,247,252,257},0))*(A7:A2720),A7:A272) )

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.



--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Erika" wrote:

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.

An alternative to Mike's solution
=SUMPRODUCT(--(MOD(ROW(A7:A272),5)=2),--(A7:A2720),A7:A272)/SUMPRODUCT(--(MOD(ROW(A7:A272),5)=2),--(A7:A2720))
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:A2720) will exclude zeros but include negative values
Change it to --(A7:A2720) to include only positive non-zero numbers
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Erika" wrote in message
...
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.

Here's another one...

Array entered** :

=AVERAGE(IF(MOD(ROW(A7:A272)-ROW(A7),5)=0,IF(A7:A2720,A7:A272)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Note that this formula is robust against row insertions *before* the
referenced range. However, if you insert new rows *within* the referenced
range then that will change the interval and the formula may return an
incorrect result.

--
Biff
Microsoft Excel MVP


"Erika" wrote in message
...
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.

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:A2720),A7:A272)/SUMPRODUCT(--(MOD(ROW(A7:A272),5)=2),--(A7:A2720))
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:A2720) will exclude zeros but include negative values
Change it to --(A7:A2720) to include only positive non-zero numbers
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Erika" wrote in message
...
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.

.

How about making the numerator
=SUMPRODUCT(--(MOD(ROW(A7:A272),5)=2),--(A7:A2720),--(ISNUMBER(A7:A272)))
Seems to work on my test data
Bernard

"Mike H" wrote in message
...
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:A2720),A7:A272)/SUMPRODUCT(--(MOD(ROW(A7:A272),5)=2),--(A7:A2720))
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:A2720) will exclude zeros but include negative
values
Change it to --(A7:A2720) to include only positive non-zero numbers
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Erika" wrote in message
...
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.

.