The formula in F4:
=AVERAGE(IF(Northeast!B:B<0, Northeast!B:B,""))
is an array formula (you used ctrl-shift-enter instead of just entering it into
the cell)

But array formulas don't work on whole columns:
=AVERAGE(IF(Northeast!B1:B65535<0, Northeast!B1:B65535,""))

Should work ok. (But I'd use a range that was big enough--but not too big.)

Wowbagger wrote:

I have a multi-sheet 2003 workbook.

Sheet 1 is a summary that displays data from the other 4 sheets, the name of
one of which is "Northeast" (though experimentation proves that it doesn't
matter what the sheet is named)

On my summary sheet this particular sheet is referenced in row 4. Column A
is nothing but the text "Northeast", the formulas in each of the next
columns are as follows (without the B: C: D: E: and F:)

B: =SUMIF(Northeast!C:C,"0",Northeast!B:B)
C: =SUM(Northeast!C:C)
D: =SUM(Northeast!D:D)
E: =1-B4/ABS(C4)
F: =AVERAGE(IF(Northeast!B:B<0, Northeast!B:B,""))

The other rows have identical formula constructs with only the referenced
sheet name changed and all work perfectly.

As is F4 displays "#VALUE". I have verified that the formula is exactly as
it should be and aside from the reference to that particular worksheet is
identical to the formula in column F in the other rows.

To further confuse me I did a little experimenting and discovered that the
formula works in some rows but not in others. I copied the formula and
pasted it into several other rows. Starting with row 6 this is how the
sheet now appears:

56.16666667
#VALUE!
56.16666667
56.16666667
#VALUE!
#VALUE!
56.16666667
56.16666667
56.16666667
56.16666667
56.16666667
#VALUE!

To be very clear, in F6 the formula

=AVERAGE(IF(Northeast!B:B<0, Northeast!B:B,""))
results in 56.16666667

In F7 the formula
=AVERAGE(IF(Northeast!B:B<0, Northeast!B:B,""))
results in #VALUE!

Can anybody shed some light on the situation? Why does the result of a
formula depend on which cell it is in?

--

Dave Peterson