Anatomy of an Array Formula

If you’ve never used an array formula, you owe it yourself to read Chip Pearson’s page on array formulas. It’s the best explanation that I’ve seen, and it’s how I learned them.

Let’s get under the hood of array formulas. Take this worksheet example:

ArrayRange2.gif

The formula in G4 finds the total sales for Tom for months 1 and 2. The braces around the formula tell us that it was array-entered with Control+Shift+Enter, instead of just Enter. Excel converts each array equality into TRUE or FALSE, and in Excel, TRUE = 1 and FALSE = 0. Here’s how this formula breaks down:

Step 1: Start with the original formula

=SUM((C2:C9="Tom")*((D2:D9=1)+(D2:D9=2))*(E2:E9))

Step 2: D2:D9 are evaluated into its values

=SUM((C2:C9="Tom")*(({1;1;2;2;2;3;3;3}=1)+({1;1;2;2;2;3;3;3}=2))*(E2:E9))

Step 3: The equality comparison is performed and a TRUE or FALSE is returned for each element of the array

=SUM((C2:C9="Tom")*(({TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})+({FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}))*(E2:E9))

Step 4: When the two arrays of TRUES and FALSES are combined with the + operator, Excel converts the TRUES and FALSES to 1’s and 0’s, then adds them

=SUM((C2:C9="Tom")*({1;1;1;1;1;0;0;0})*(E2:E9))

ArrayArrow.gif

TRUE + FALSE = 1 + 0 = 1
FALSE + FALSE = 0 + 0 = 0

Step 5: C2:C9 is evaluated into its values

=SUM(({"Tom";"Dick";"Tom";"Dick";"Harry";"Tom";"Dick";"Harry"}="Tom")*({1;1;1;1;1;0;0;0})*(E2:E9))

Step 6: That equality comparison is performed

=SUM(({TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE})*({1;1;1;1;1;0;0;0})*(E2:E9))

Step 7: The first array of TRUES and FALSES is multiplied to the second array of 1’s and 0’s. Remember that TRUE = 1 and FALSE = 0

=SUM({1;0;1;0;0;0;0;0}*(E2:E9))

Step 8: E2:E9 is evaluated into its values

=SUM({1;0;1;0;0;0;0;0}*({12200;7100;10600;9500;17300;15000;10300;16800}))

Step 9: The two arrays are then multiplied together

=SUM({12200;0;10600;0;0;0;0;0})

Step 10: The SUM operation is performed on the remaining array

22800

Once you understand how the TRUES and FALSES are converted to 1’s and 0’s, then combined with * representing AND and + representing OR, then writing array formulas will be a breeze. Hopefully this got you a little closer.

11 thoughts on “Anatomy of an Array Formula

  1. Andrew

    Dick, I remember asking about why arrays worked like they do on a forum a while back. Nobody answered my question at the time but you sure have. Thanks bud.

  2. Ola Sandström

    I have not thought about this before.
    They will give the same answer.

    ABCD
    1ExcelWordPPT
    2Apple123
    3Orange456
    4Kiwi789
    5
    6Orange
    7Word

    Normal:
    =INDEX(B2:D4;
    MATCH(A6;A2:A4;0);
    MATCH(A7;B1:D1;0))

    Array formula:
    {=SUM(
    (A2:A4=A6)*
    (B1:D1=A7)*
    (B2:D4))}

    Ola

  3. Jamie Collins

    It’s worth pointing out that if you want to evaluate parts of a formula as Dick has done e.g. convert

    D2:D9

    into the array of values

    {1;1;2;2;2;3;3;3}

    simply highlight the relevant part and press F5. Don’t forgot to press escape (or equivalent) when done or the evaluated text will remain in the formula.

    Jamie.

    –

  4. David Shipp

    I haven’t had a close look at what you have written but think I use pivot tables to obtain similar results. I do have a complex problem, however, and wondered if you might suggest if arrays could be a way of solving it.

    If there is a lottery in which 7 winning numbers and 2 supplementary numbers are drawn from 45 numbers, given these numbers and 8 “chosen” numbers, what are all the unique combinations of 7 possible (there should be 8)? In addition, what is the highest combination on winning and supplementary numbers for each unique combination?

    Further again, what if we want to look at 9 “chosen” numbers (a system “9?) or 10 (a system “10?)? Can we use Excel arrays to extract this information?

  5. Pingback: Array Formulas in Excel – Using VBA « One Stop Analytics

  6. Pingback: Array Formulas in Excel – A Quickie « One Stop Analytics

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax