Copy/Paste array formulae from the newsgroup

[MartinW]

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

 

[T. Valko]

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 F2>CSE. 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.

 

[MartinW]

Thanks for the reply Biff,

As you say when you enter the formula normally it will
return 0 until you put data in A5 and it will then return 1.

However if you copy the formula from my post and
paste it into F4 and go through the same trial you will see
it correctly returns the row number of the last value.
If I then put a value in A37 the normally entered
formula in E4 will show 1, but the pasted version in F4
shows 37 the same as it would if it was committed
properly with CSE.

I'll have a play with your implicit intersection
examples and see if I can get a handle on that.

Thanks
Martin

 

[T. Valko]

However if you copy the formula from my post

I did!

I can't reproduce what you're describing.

 

[MartinW]

I'll try to explain it step by step what I am doing
as it certainly is strange....to me anyway.
I'm using XL2000 though that shouldn't matter (I think)

In a fresh worksheet I copy and paste the formula
separately into E4 F4 and G4.
I then,
Select E4, press F2 and commit with Enter
Select F4, press F2 and commit with C+S+E
Leave G4 as pasted.
All three formulae are showing 0

I then put a 1 in A1 which shows
E4 = 0
F4 = 1
G4 = 1
I continue to put 1 down col A, when I get to A4
the formulae are showing
E4 = 1
F4 = 4
G4 = 4
I then skip to A37 and put a 1 (or any number), the
formulae now show
E4 = 1
F4 = 37
G4 = 37
Skip again to A739 and put a number in, shows
E4 = 1
F4 = 739
G4 = 739

The pasted formula in G4 works in perfect unison
with the CSE formula in F4 even though it
has not been committed as an array.

Can you duplicate that or have I got something
really screwy happening here?

Regards
Martin

 

[T. Valko]

Can you duplicate that or have I got something

really screwy happening here?

I *can not* duplicate that and I followed your steps exactly. I'm using
Excel 2002 (all service packs installed)

See inline comments

--
Biff
Microsoft Excel MVP

I'll try to explain it step by step what I am doing
as it certainly is strange....to me anyway.
I'm using XL2000 though that shouldn't matter (I think)

In a fresh worksheet I copy and paste the formula
separately into E4 F4 and G4.
I then,
Select E4, press F2 and commit with Enter
Select F4, press F2 and commit with C+S+E
Leave G4 as pasted.
All three formulae are showing 0

I then put a 1 in A1 which shows
E4 = 0
F4 = 1
G4 = 1

The results I get are:

E4 = 0
F4 = 1
G4 = 0

I continue to put 1 down col A, when I get to A4
the formulae are showing
E4 = 1
F4 = 4
G4 = 4

The results I get are:

E4 = 1
F4 = 4
G4 = 1

I then skip to A37 and put a 1 (or any number), the
formulae now show
E4 = 1
F4 = 37
G4 = 37

The results I get are:

E4 = 1
F4 = 37
G4 = 1

Skip again to A739 and put a number in, shows
E4 = 1
F4 = 739
G4 = 739

The results I get are:

E4 = 1
F4 = 739
G4 = 1

The pasted formula in G4 works in perfect unison
with the CSE formula in F4 even though it
has not been committed as an array.

Can you duplicate that or have I got something
really screwy happening here?

Well, I can't duplicate that and I don't have an explanation as to why it's
doing that for you. It shouldn't.

Hopefully others will see this thread and try it then let us know how it
worked for them.

 

[MartinW]

Thanks for trying Biff. It's all very strange. Like you
say it shouldn't happen like that. Hopefully another
2000 user can confirm whether or not they get
the same behaviour.

Thanks again
Martin