Quasi array formula
 

Hi Dave


"Dave" skrev i en meddelelse
...
I've played around a fair bit with array formulas and arry formulas
using SUMPRODUCT. Today I mucked about a bit with SUM wondering if I
could return an array with entering it with CTRL-Shift-Enter

I guess, you mean "without" entering??



If I enter =SUM({1;2;3;4;5;6;7;8;9;10}) the formula returns 55

Here you have entered an array as argument, so Excel works on that array.


, If I
enter =SUM((ROW(1:10)<"")*(ROW(1:10))) as a normal formula it returns
1, the first element of the array.

Yet if I highlight the =((ROW(1:10)<"")*(ROW(1:10)) part in the formula
bar it shows ({1;2;3;4;5;6;7;8;9;10}) and the Fx builder shows 55 as the
answer. Why and how does the SUM formula return 1 not 55?

Since the SUM function (in contrast to the SUMPRODUCT *function*) doesn't
expect
an array as argument, you must explicitly tell it, if you want that to be
the case (by
using <Shift<Ctrl<Enter or by using an array of constants).

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only, please.

Quasi array formula
 

Hi Leo,

Yes, without entering as CTRL-Shift-Enter, was what I intended to write.
:)

I thought it was curious that SUM evaluates an explicit array but not an
implied one. Its also interesting that the function builder returns the
result of the implied array but the final function does not.

I guess as you say that is the design, but I feel that its inconsistent.
Back to SUMPRODUCT I go.

Cheers

Dave


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!