• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

What is An Array formula

I am still not completely clear as to what an array formula is in excel.
What is the benefit of clicking CTRL+ENTER and getting the {} before the formula.
Searched extensively but I am not getting the complete picture.
 
A Normal formula works as entered
eg: =sum(A1:A10) Enter
=55
will return the sum of the range A1:A10

upload_2017-6-28_8-20-1.png

An array formula processes the formula and applies it to all elements in the array within the formula
eg: =sum((A1:A10)*2) Ctrl+Shift+Enter
=110
This will sum all the values in A1:A10 but only after each value in A1:A10 is multiplied by 2

There are lots of articles on Array Formulas
you could start here:
Http:chandoo.org/forums/topic/plain-language-definition-of-array-formulas-vs-other-formulas

http://www.cpearson.com/excel/arrayformulas.aspx

I also write about array formulas in the Formula Forensics series:
http://chandoo.org/wp/formula-forensics-homepage/
 
ANY formula that has an array in it REQUIRES using Control+Shift+Enter or it won't work correctly.
Excel automatically inserts the formula between { } (a pair of opening and closing braces).

Note: Manually typing braces around a formula will not convert it into an array formula — you must press Ctrl+Shift+Enter to create an array formula. Any time you edit the array formula, the braces ({ }) disappear from the array formula, and you must press Ctrl+Shift+Enter again to incorporate the changes into an array formula which adds the braces.


Arrays look like A1:F10 or in Conditional Formatting $A$1:$F$10
See attachment that looks like the picture below:

upload_2017-6-27_20-45-23.png
 

Attachments

  • Chandoo - array explanation.xlsx
    10.5 KB · Views: 11
ANY formula that has an array in it REQUIRES using Control+Shift+Enter or it won't work correctly.
Hi ,

Not necessarily so ; it entirely depends on the native Excel function which is used.

The SUMPRODUCT natively works on arrays , and does not require the usage of CTRL SHIFT ENTER unless it has another function within it which requires usage of CTRL SHIFT ENTER.

The SUMIF family of functions natively work on arrays , as do several others.

Narayan
 
Hi ,

Not necessarily so ; it entirely depends on the native Excel function which is used.

The SUMPRODUCT natively works on arrays , and does not require the usage of CTRL SHIFT ENTER unless it has another function within it which requires usage of CTRL SHIFT ENTER.

The SUMIF family of functions natively work on arrays , as do several others.

Narayan
Thank you for making those clarifications! :)

The examples I gave in the array formula that gives a single result were:
=SUM(B3:F3*B4:F4) vs. {=SUM(B10:F10*B11:F11)}
OR
=SUM(array * array) vs. {=SUM(array * array)}

...and...

=array * array

across the bottom of the chart yielding a multiple result.
 
Back
Top