R..VENKATARAMAN wrote...
Though I am to some extent conversant with Excel and VBA I get into some
fundamental questions periodically. The following are a few (not
exhaustive) doubts.
sum(a1:a9) is not an array fucntion
=SUM(--(A1:A9)) is an array functin
=SUMPRODUCT(--(A1:A9)) is not an aarray function
=SUMPRODUCT(A1:A9) is not an array function
there must be some logic behind some as array functin ,some not. Any
comments. Thank you.
First, while there are array formulas, there are no array functions, at
least none named as such in Excel's documentation. But I think I know
what you mean: function calls that require the enclosing formulas to be
entered as array formulas to return the intended results.
It's not the functions per se, at least not all the time. If A1:A4
contained {1;2;3;5}, then formulas calling SUM(--A1:A4) would need to
be entered as array formulas in order for this particular call to
return 11. However, SUM({1;2;3;5}) happily returns 11 whether or not
entered as part of an array formula. In short, SUM can iterate over
ranges or array *constants* without the enclosing formula being array
entered, but it can't iterate over dynamic arrays, i.e., arrays that
are the result of expressions. I suspect but can't prove that it's the
formula parser that provides these semantics.
SUMPRODUCT and LOOKUP are the most accomodating. They both accept
dynamic arrays as arguments and return expected results without array
entry. This may not hold when there are other function calls in their
arguments, but that's because those other functions may require array
entry.
Besides SUMPRODUCT and LOOKUP, there are other functions that always
return arrays: ROW, COLUMN, FREQUENCY, TRANSPOSE, MMULT, MINVERSE,
LINEST and LOGEST.
