software

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