Are you sure it's working *exactly* as described?

If I enter the formula (normally entered) in E4 and I then enter a random
number in A1 the result of the formula is 0. If I then start entering random
numbers in cell A2 and continue down the column, the formula continues to
return 0 until there is an entry in cell A4. Then the formula result is 1
which is correct.

This due to what's called the implicit intersection rule in regards to
arrays.

The formula and a piece of data are entered on the same row (could also be a
column) and the data cell is within the referenced range of the formula.
This is called an implicit intersection. Since the formula references an
array but is not array entered it only evaluates the first element of the
array.

=MAX(IF(A1<"",ROW(A1)))

Here's another example of an implicit intersection.

Enter the numbers 1,2,3,4,5 in A1:A5.

(Normally) enter this formula in B6:

=OFFSET(A1,,,5)

The result will be an error. Now, drag the formula up to B5 and note the
result, then drag up to B4, B3, B2, B1.

Now, drag the formula back down to B5 then F2CSE. Note the result.

These were 2 different formulas doing 2 totally different things so the
results are different but the implicit intersection rule dictated those
results.


--
Biff
Microsoft Excel MVP


"MartinW" wrote in message
...
Hi Group,

Can anyone explain this behaviour? Often when I copy
and paste an array formula from the newsgroups, the
formula will work straight away even though it hasn't
been committed with Ctrl+Shift+Enter and there are no
curly braces around it. If I then do an F2+Enter the formula
will fall down and requires a C+S+E to recommit it.

Take this example from a recent Bob Phillips post.

=MAX(IF(A1:A1000<"",ROW(A1:A1000)))

If I paste it anywhere outside column A, and then start
putting data in column A, the formula will correctly
return last row that has a number in col A, even though
it is an array formula and has not been committed
with C+S+E and there are no curly braces around it.

Any ideas why?

Regards
Martin