Anatomy of an Array Formula

By in Uncategorized on .

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