"Dave Peterson" wrote in message
...
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)

Don't array formulas always appear with {}s around them?

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.)

I tried with B1:B1000 as the range instead of B:B and I get the same
problem: and the same issue with the formula working in some cells but not
in others. Even cells that never had any formula in them at all.

Interestingly enough I've found another weird aspect to this problem.

I cut/pasted row 4 to row 6 and the formula works fine (as it always has).
Thinking that there may be some weird corruption or strange formatting
glitch I deleted row 4 from the database. Row 5 (which was now row 4)
stopped working and generated the same error.

Everything works fine as long as I leave row 4 empty. What could possibly
be causing this?


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