Using multicell range when single cell prescribed

[Wild Bill]

This is unusual (to me) range usage. It is NOT entered as array formula.
A1=123
A2=456
B1=left(A1:A10,1)
B2=left(A1:A10,1)

Note the results. Additional observations - you can stick dollar signs
on 1 and 10: no result change. You can (instead) copy B1 to B2: no
result change.

It's as though using a range where you "ought" to be using a single cell
[range] is interpreted as "the cell for column A of current row" (for
this choice of A1:A10).However note that B1=left(A2:A10,1) gives #VALUE.

Is this a beneficial (and reliable) tactic in some situations, for some
worksheet functions...or should it be simply considered *proscribed*?<g>

 

[Guest]

Hi Wild:

Just because
B1=left(A1:A10,1)
returns "something" does not mean it returns what you want. Lets say in A1
thru A10 we have:

a
b
c
d
e
f
g
h
i
j
If we now enter
=left(A1:A10,1) into B1 thru B10 as an array formula, we get:

a a
b b
c c
d d
e e
f f
g g
h h
i i
j j

 

[T. Valko]

It's as though using a range where you "ought" to be using a single cell

[range] is interpreted as "the cell for column A of current row" (for
this choice of A1:A10).

That is correct. It's called the implicit intersection and it applies to a
just about any formula that should be array entered but isn't.

Here's another example:

A1 = 71
A2 = 49
A3 = 65
A4 = 5
A5 = 12

Formula entered in B1 and copied down to B5:

=A$1:A$5>50

The results will be:

TRUE
FALSE
TRUE
FALSE
FALSE

Is this a beneficial (and reliable) tactic in some situations

Maybe. I've never had the need to use it but others may have.

--
Biff
Microsoft Excel MVP

This is unusual (to me) range usage. It is NOT entered as array formula.
A1=123
A2=456
B1=left(A1:A10,1)
B2=left(A1:A10,1)

Note the results. Additional observations - you can stick dollar signs
on 1 and 10: no result change. You can (instead) copy B1 to B2: no
result change.

It's as though using a range where you "ought" to be using a single cell
[range] is interpreted as "the cell for column A of current row" (for
this choice of A1:A10).However note that B1=left(A2:A10,1) gives #VALUE.

Is this a beneficial (and reliable) tactic in some situations, for some
worksheet functions...or should it be simply considered *proscribed*?<g>