Briefly:

(F3:F37<0)

returns an array of 35 TRUE/FALSE values.

--(F3:F37<0)

converts the array to 1/0 (see

http://www.mcgimpsey.com/excel/doubleneg.html

for more explanation).

--(MOD(ROW(F3:F37),8)=2)

returns an array of 35 1s/0s depending on whether each row's row number,
when divided by 8, has a remainder of 2.

Those arrays are multiplied to return an array of 1s/0s (1 for rows for
which the remainder<2 and the value<0).

In the denominator, SUMPRODUCT() then adds those 1/0 to get a count of
valid values.

In the numerator, the arrays are further multiplied by the array of
values in F3:F37. Where both the remainder < 2 and the value <0, the
cell's value is returned in the array. SUMPRODUCT() then adds those
values in the array.

Dividing the sum of valid values by the count of valid values gives the
average.



In article ,
DaS wrote:

That worked great. Thanks JE, also for identifying the whole "bracket"
mix-up. I hate to be a bother, but could you explain how the formula works?

"JE McGimpsey" wrote:

Well, no, it won't.

First, the ranges need to be the same size.

Second, it won't remove the cells the OP wants it to.

This should work:

=SUMPRODUCT(--(MOD(ROW(F3:F37),8)<2), --(F3:F37<0),F3:F37)/
SUMPRODUCT(--(MOD(ROW(F3:F37),8)<2), --(F3:F37<0))