## 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:

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

**Step 2:** D2:D9 are evaluated into its values

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

**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

**Step 5:** C2:C9 is evaluated into its values

**Step 6:** That equality comparison is performed

**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

**Step 8:** E2:E9 is evaluated into its values

**Step 9:** The two arrays are then multiplied together

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

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.

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.

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.

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

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

An excellent explanation. Appreciate it.

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

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.

… that should be F9, of course.

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?

VERY NICE EXPALANATIONS.

[…] 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/. […]

[…] http://www.dailydoseofexcel.com/archives/2004/04/05/anatomy-of-an-array-formula/ […]