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
=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))
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.
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.