Array formula that worked with just enter

[psrs0810]

I used the following array formula to sum the top 3 values, but instead of finishing it with ctrl+shift+enter, I used enter and it came up with the same information. Why is this?
=SUM(LARGE(A2:A12,{1,2,3}))

 

[mikerickson]

SUM will work on an array. (e.g. =SUM(A1:B5) )
Similarly MAX

 

[psrs0810]

everything that I read said I needed to use ctrl+shift+enter. What I am trying to figure out is why the formula worked when I just hit enter.

thanks

 

[Jon von der Heyden]

SUM, in the context that you provided, will work because the array passed is an inline array constant (the bit in the curly brackets).

Had you used:

=SUM(LARGE(A1:A12),ROW(1:3))

Then you would have needed to confirm with CSE, or substitute for SUMPRODUCT.

 

[psrs0810]

where can I get more information about the inline array constant?

 

[Jon von der Heyden]

Well there isn't really a hell of alot more to say on the subject. I'll try and explain why.

An inline array constant is (from back to front):

• A constant (because the values will no change)
• An array (because it contains many values)
• Inline (because it has been fed directly to the formula)

Excel knows to treat your formula as an array formula because it has been fed an array constant, by mere virtue that it contains an array.

Passing a range to a formula does not explicitly tell Excel that the formula is an array formula because it might not be.

Hope this makes sense...

 

[psrs0810]

let me read it a couple of times. hopefully it sinks in

thanks

 

[Jon von der Heyden]

I think the best I can offer you is the way it was explained to me originally. Colin is far better as explaining these things than I am.

http://www.mrexcel.com/forum/showpost.php?p=1822381&postcount=7

 

[mikerickson]

Jon, thanks for the explaination and the link.

 

[Jon von der Heyden]

The whole story of how excel processes these sorts of arrays is actually quite interesting. So although the original question has been addressed, I think you might find this an interesting read Mike. Particularly post #14