Chapter 26: Array Formulas
What are Arrays in Excel?
Before delving into array formulas, it is essential to grasp the concept of
arrays in Excel. An array is a collection of values arranged in rows and
columns, creating a structured dataset. Excel manages arrays effortlessly,
allowing users to manipulate and analyse data efficiently. Let us consider a
simple example to illustrate the concept of arrays.
Suppose you have a dataset representing monthly sales for two products
(Product A and Product B) over the course of a year. The data might look like
this:
In this context:
The entire table represents a 2x12 array (2 rows and 12 columns).
Each row corresponds to a product (Product A and Product B).
Each column represents a specific month.
So, if you want to refer to the sales figure for Product A in February, you
would be accessing a specific element in the array.
Arrays in Excel provide a structured way to organise and work with data,
allowing users to perform calculations and analysis efficiently. The array
concept becomes even more powerful when working with array formulas,
where you can manipulate entire ranges of data in a single formula.
What is an Array Formula?
An array formula in Excel is a special type of formula that performs multiple
calculations on one or more items in an array. Unlike regular formulas that
operate on individual cells, array formulas can process entire ranges of data
with a single formula. This powerful feature enables users to perform
complex calculations, manipulations, and analyses efficiently. The benefits of
array formulas are:
Efficiency: Array formulas streamline calculations, reducing the
need for multiple formulas and improving performance.
Advanced Data Analysis: They are essential for performing
advanced data analysis, complex calculations, and managing
multiple conditions simultaneously.
Dynamic Results: Array formulas adapt to changes in input
data, providing dynamic and automatic updates.
What is an Array Constant?
An array constant in Excel is a fixed set of values arranged in a specific order
within curly braces { }. Unlike a range of cells, an array constant is a literal
representation of data that you can directly use in formulas. Array constants
play a crucial role in array formulas, allowing you to perform operations on
sets of values without referring to individual cells. An example of an array
constant is:
{5, 8, 12, 6, 10}.
If we look at the following array formula ‘=SUM({5, 8, 12, 6, 10})’, the array
constant {5, 8, 12, 6, 10} directly represents the set of numbers. The SUM
function then adds these values together, resulting in the sum of the array 5
+ 8 + 12 + 6 + 10 = 41.
Use Cases
Array formulas in Excel offer a versatile set of functionalities that cater to a
variety of use cases. Here are some scenarios where array formulas prove to
be particularly useful:
Multiple Criteria Lookup: Array formulas can be employed to
perform lookups based on multiple criteria, allowing users to
extract specific data points that meet complex conditions.
Advanced Aggregations: Array formulas enable advanced
aggregations beyond the capabilities of standard functions. This
includes calculating the median, mode, or percentile of a dataset.
Conditional Counting and Summing: Array formulas can be
applied to count or sum values based on specific conditions,
offering a more nuanced approach than traditional COUNTIF or
SUMIF functions.
Transposing Data: Array formulas facilitate the transposition of
data, allowing users to switch rows with columns and vice versa,
which can be particularly helpful in certain analytical scenarios.
Complex Mathematical Calculations: For mathematical
operations that involve multiple variables and require intricate
calculations, array formulas provide a powerful mechanism for
execution.
Handling Non-Contiguous Data: Users can use array
formulas to process non-contiguous data ranges, which is often
challenging with standard formulas.
Simulating Database Functions: Array formulas can simulate
some database functions, offering a dynamic approach to data
extraction and analysis.
Advanced Data Validation: Applying array formulas to data
validation scenarios allows for more sophisticated checks and
validations based on intricate rules.
Sequential Number Generation: Array formulas can assist in
generating sequential numbers based on specified patterns or
conditions, streamlining tasks such as numbering rows.
How to Enter Array Formulas
Unlike regular formulas, array formulas are entered using a special key
combination: Control + Shift + Enter (CSE). This signals to Excel that you are
working with an array formula, prompting it to calculate the formula for the
entire array.
Examples of Using the Array Formulas
Let us now look at some examples of how to use array formulas.
Example 1: Sum the Nth Smallest Values
Data
We will use the following dataset for this example.
In this example, we want to sum the 2nd and 3rd smallest values in the range.
Instructions
1. Data Preparation:
Enter the above dataset from A1 to A5.
2. Enter the Array Formula:
Select the cell where you want the result. Let's say B1.
Enter the formula =SUM(SMALL(A1:A5, {2,3})).
3. Use Control + Shift + Enter:
Press Control + Shift + Enter to complete the array formula.
How it Works
The SMALL function is employed to fetch the second and third
smallest values within the given range (A1:A5). The array constant
{2,3} indicates the intent to extract the second and third smallest
numbers, subsequently passed as arguments to the SUM function.
8 is the second smallest and 10 is the third smallest.
The SUM function adds the values in the resulting array.
SUM(8, 10) = 18.
Example 2: Sum Values Based on Criteria
Data
For this example, we will use the following dataset.
Here we want to sum the values in column A where the corresponding value
in column B is "Yes”.
Instructions
1. Data Preparation:
Enter the above dataset from A1 to B5.
2. Enter the Array Formula:
Select the cell where you want the result. Let's say C1.
Enter the formula =SUM((B1:B5="Yes")*A1:A5).
3. Use Control + Shift + Enter:
Press Control + Shift + Enter to complete the array formula.
How it Works
Excel evaluates the condition (B1:B5="Yes") to create an array of
TRUE and FALSE values:
{TRUE, FALSE, TRUE, FALSE, TRUE}.
It then multiplies the TRUE/FALSE array elementwise by the
corresponding values in column A:
{10, 0, 5, 0, 8}.
The SUM function adds the values in the resulting array:
SUM(10, 0, 5, 0, 8) = 23.
Example 3: Multi-Criteria Lookup
Data
For this example, we will use the following dataset.
Here, we want to retrieve the value in column C where both column A and
column B match specific criteria. In this scenario, you want to obtain the
price in column C where the fruit is “Apple”, and the colour is “Green”.
Instructions
1. Data Preparation:
Enter the above dataset from A1 to C5.
2. Enter the Formula:
Select the cell where you want the result. Let's say F5.
Enter the formula =INDEX(C2:C5, MATCH(1,
(A2:A5="Apple") * (B2:B5="Green"), 0)).
3. Use Control + Shift + Enter:
Press Control + Shift + Enter to complete the array formula.
How it Works
Excel evaluates the two conditions (A2:A5="Apple") and
(B2:B5="Green") separately:
{TRUE, FALSE, TRUE, FALSE} and {FALSE, FALSE, TRUE,
FALSE}.
These conditions are multiplied elementwise (TRUE * FALSE,
FALSE * FALSE, TRUE * TRUE, FALSE * FALSE):
{0, 0, 1, 0}.
The MATCH function searches for the first occurrence of 1
(indicating a match) in the resulting array:
MATCH(1, {0, 0, 1, 0}, 0) = 3.
The INDEX function retrieves the corresponding value from
column C at position 3:
INDEX(C2:C5, 3) = £1.80.
Using the F9 Key for Evaluation
An additional tool in your array formula arsenal is the F9 key. After entering
an array formula, select a portion of the formula and press F9 to evaluate that
portion. This allows you to troubleshoot and understand how each element
contributes to the overall result.
To demonstrate, we will use the array formula in the first example.
Highlight the SUM functions first argument, “number1”.
Press the F9 key and you can see that it returns {8,10}. This is the second and
third smallest values in the range. To return to the formula, just press the Esc
key.
Summary
Array formulas stand out as a dynamic and robust tool for managing complex
calculations and data manipulation. As you navigate the world of array
formulas, you have discovered the unique capabilities that set them apart
from standard formulas. Mastering array formulas opens doors to a deeper
understanding of spreadsheet functionalities. Whether you are working with
large datasets, building intricate models, or automating repetitive tasks, the
knowledge and application of array formulas propel you towards Excel
mastery.