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.

AndrewDick, 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.

Jan NordgreenThanks 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.

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

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

John MansfieldAn excellent explanation. Appreciate it.

Ola SandströmI 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

Jamie CollinsIt’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.

Jamie Collins… that should be F9, of course.

David ShippI 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?

HARINDER BHATTIVERY NICE EXPALANATIONS.

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

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