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" 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: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: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.

.