Just some guesses / thoughts ..

=AVERAGE(IF(Northeast!B:B<0, Northeast!B:B,""))
The above will return #NUM! anywhere if it is *correctly* entered with
CTRL+SHIFT+ENTER (instead of just pressing ENTER). It's an array formula
which requires that special way of entering the formula. And think the error
#NUM! results because you can't use entire column references (B:B, C:C, etc)
in the formula

Try instead, something like:
=AVERAGE(IF(Northeast!B1:B10<0, Northeast!B1:B10,""))
and array-enter the formula with CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Wowbagger" <none wrote in message
...
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?