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 Comments

  1. Andrew says:

    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. Thanks for a nice blog! I enjoy the down to earth style.

    Maybe your readers will enjoy my article ‘Ignorance – an excellence starting point for learning’ where array formulas play a leading role. It is at http://mumnet.tripod.com/thoughts/ignorance.htm.

  3. Jon says:

    Hey Dick, don’t forget MVP Bob Umlas’ white paper on array formulas:

    http://www.emailoffice.com/excel/arrays-bobumlas.html

  4. An excellent explanation. –Appreciate it.

  5. Ola Sandström says:

    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

  6. Jamie Collins says:

    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.

    –

  7. Jamie Collins says:

    … that should be F9, of course.

  8. David Shipp says:

    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?

  9. HARINDER BHATTI says:

    VERY NICE EXPALANATIONS.

  10. […] 2. This is probably a very well thought out solution and easier in terms of operational complexity but requires more work in terms of code. It’s best read directly at Dick Kusleika’s blog – http://www.dailydoseofexcel.com/archives/2004/04/05/anatomy-of-an-array-formula/. […]

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: