Conrad Carlberg wrote...
....
Oh, man, did you ever just open a can of worms. Bob Umlas, aka the Excel
Trickster and undisputed king of array-entered formulas, just had an entire
book published on that topic. I remember an MVP colloquium in Seattle just
before Excel 95 was released, and asking other Excel MVPs for a crisp
definition of when array formulas were needed. No one could supply one.
(Certainly I couldn't; else I wouldn't have asked.)

A complete answer requires full details in each argument for each
built-in function, but certain subsets are known. The following refer
to single function calls. In real formulas with multiple function
calls, if one of those functions requires array entry, the entire
formula requires array entry.


Formulas with only arithmetic or string operators, scalar-valued terms
and array constants don't require array entry, but they'd only return
the top-left entry of the array result.

Functions don't require array entry when using array constants but
neither derived arrays not multiple cell ranges, e.g.,
=SUM({1,2}*{10;100}) returns 330 while =SUM(A1:B1*A2:A3) returns
#VALUE! entered normally and 330 entered as an array formula when A1:B1
= {1,2} and A2:A3 = {10;100}.

LINEST, LOGEST and FREQUENCY, don't require array entry per se.
MINVERSE and TRANSPOSE require array entry except when all their
arguments are array constants. MMULT requires array entry except when
both its arguments are array constants *OR* when its result is a
scalar, e.g., with A1:B1 and A2:A3 as above, =MMULT(A1:B1,A2:A3)
returns 210 entered normally. However, you'll only get the top-left
entry from the array results returned by these functions unless they're
entered as multiple cell array formulas.

ROW and COLUMN *always* return arrays, even when their arguments are
explicit or default references to single cells, and don't on their own
require array entry. Same 'however' as the last paragraph.

A few built-in functions expect array arguments and don't require array
entry, e.g., SUMPRODUCT, LOOKUP, SUMX2MY2, SUMXMY2.

Aggregation functions, e.g., SUM, COUNT, AVERAGE, require array entry
only when some arguments are derived arrays. All other functions
require array entry when some arguments are derived arrays or single
area multiple cell ranges (which evaluate to arrays).

The type testing functions, e.g., ISNUMBER, ISBLANK, ISERROR, don't
require array entry. Same 'however' as previously.

I'm intentionally remaining silent on the functions that return range
references, INDEX, INDIRECT and OFFSET, or have specific arguments that
must be (or are supposed to be) range references, N, T, CELL, SUMIF and
COUNTIF. Their semantics are so screwy that they'd need treatises on
how the type of each argument affects them. Also, the former set of
functions can produce return values that work like arrays of range
references, and the latter set are the only functions that can use such
results.

I'm also remaining silen on the list processing functions, e.g., DSUM,
DCOUNT, because I never use them, so I don't care how they behave.

There are exceptions to these rules, but you'll have to work to find
them.

Quite apart from your situation, array formulas are necessary when you're
working with certain worksheet functions such as LINEST, TRANSPOSE, certain
matrix algebra functions, and simpler functions such as FREQUENCY. . . .
....

Not entirely correct. See above. Also, e.g.,

=SUMPRODUCT(TRANSPOSE({1,2,3}),10^ROW(1:3))

returns 3210.