An Array Formula is difficult to understand, to write and also to explain but once you get it, you open a whole new land of productivity and effectiveness with Excel. In this article, I will make sense of AFs in a different way so you can enjoy the productivity that this Excel workaround will surely give you.
I hope this new explanation contributes to a better understanding of this precious and not so exploited Excel technique.
Well, let’s begin…
An Array Formula is an Excel mechanism that evaluates a kind of imaginary/temporal array inside a classical function. They also allow you to build formulas without extra rows or columns.
While you could avoid AFs by creating a Helper Column, an Array Formula creates this array internally without cluttering your spreadsheets.
We will use the table below for the explanation…
Imagine you want to calculate the Total cost (shown in F23 above) with a single formula and without adding the Total column (F column above)…
The most obvious way to do it is by using the following long formula: the sum of the products: Units x Unit Cost…
=D2*E2+D3*E3+D4*E4+D5*E5+D6*E6+D7*E7+D8*E8+D9*E9+D10*E10+D11
*E11+D12*E12+D13*E13+D14*E14+D15*E15+D16*E16+D17*E17+D18*E18+D19
*E19+D20*E20+D21*E21+D22*E22
But you can do it using an Array Formula, let’s see (for this explanation I will not use SUMPRODUCT)…
This is why Power Excel Users love AFs, turn above long formula into this one: {=SUM(D2:D22*E2:E22)}, it does the same but in a compact way, let’s see:
Array Formulas are difficult to understand because they act in a kind of imaginary array.
In above formula, this imaginary array would be a kind of “sum of the products” array.
What does imaginary mean?
It means that the array is not in the formula {=SUM(D2:D22*E2:E22)} but you should think as it would be.
The Array Formula could be seen as: =SUM(F2:F22) where the array F2:F22 is the imaginary column (sum of the products), it would contain the row by row formulas =D2*E2.
This is why AFs are difficult to build and understand. They act in imaginary levels…
Well, you have build it {=SUM(D2:D22*E2:E22)}
Here are more examples: {=MATCH(TRUE,EXACT(Haystack!$B$2:$B$8,A2),0)} evaluates TRUE in the imaginary/temporal range of TRUE and FALSEs that would result after applying the EXACT function.
To finish this explanation, let’s explore another practical example, the formula: {=SUM(IF(C1:C4>4,C1:C4))} in cell A6, creates a temporal range with the values greater than 4 and then applies the SUM function.
You would have a formula like this one =SUM(A2,A4)
Important notice about Array Formulas:
- Press CTRL + SHIFT + ENTER each time you write or edit one
- Array formulas are also know as CSE formulas
- Arrays must be equal dimension
Popularity: 5% [?]
Post a Comment