6. Array Formulas
Array formulas allow you to perform calculations on multiple cells. Here I show you how to use them to create Max(IF) and Min(IF) functionality.
- Allow you to perform calculations on arrays, not just individual cells
- Can output to a single cell or to multiple cells (must be pre-selected)
- Shown by curly braces around a cell formula
- Common uses of array functions are MaxIF and MinIF formulas
Useful keyboard shortcuts and functions
CTRL + SHIFT + ENTER: Perform array function
ALT + E , S , F: Paste formulas
CTRL + SHIFT + →: Select all cells within data region
SHIFT + →: Select an additional cell
F2: Jump inside formula
F4: Anchor cells (when inside a formula)
=TRANSPOSE: Convert row to column and vice versa
Array functions, as the name suggests, allow you to perform multiple calculations on one or more items in an array of cells.
The output from an array function can be either a single cell or an array, depending on how you write the formula.
Many of the most common applications of array functions have been given their own separate functions in later versions of Excel.
Examples of this include COUNTIF, AVERAGEIF and SUMIF.
I always recommend sticking with these inbuilt functions over building your own array formulas, but sometimes an inbuilt function doesn't exist and you'll need to know how Array formulas work.
A case in point are the commands for MAXIF and MINIF.
Let's start off and look at array functions with the simple example of calculating the total revenue for our Q1 sales data. We can accomplish this by simply adding up the Order $ Amount column, but let's try for practice using an Array function.
We know that for each row the Product price is multiplied by the Order quantity, and the results are then added up to find the total revenue.
So let's start off by writing “=sum” and we'll first select the Product price array.
So Ctrl + Shift +UP-Arrow and then we'll multiply this number by the ‘Order quantity’ array.
If I then close the bracket and press Enter, unfortunately I get a value error.
If we now go back into the formula with F2 and instead press Ctrl + Shift+ Enter, the formula now works and the revenue is calculated.
The Ctrl + Shift + Enter command created a set of braces around the formula and this allows for Array functions to be performed.
Excel then multiplied the corresponding cells in both arrays and performed a SUM function to get the final answer.
In this example, the SUM function ensures that the output from the array is only one cell.
However, manual Array functions output to multiple cells.
Let's look at another common Array function example that does this called TRANSPOSE.
TRANSPOSE converts columns of data into rows and vice versa.
Let's see this in action by transposing the first five Order dates in January.
So I'll write “=transpose” and then select the relevant cells.
And when I close the bracket and press Enter, as expected, I get an error.
If I go back into the formula, press Ctrl + Shift + Enter, I get the first value.
Transpose all five values I need to preselect the output cells before I complete the Array function.
So let's select these cells, let's press F2 to go back into the function, and now Ctrl + Shift + Enter.
And this transposes all five output values.
So when you're outputting multiple cells from an Array function, always make sure to pre-select the destination cells before pressing Ctrl + Shift + Enter.
Now let's move on to MAXIF and MINIF.
To calculate the max order quantity of Tablets, we'll start off by writing a MAX function.
And inside the MAX function, well create an IF statement.
And the IF statement will check if our product type is equal to Tablet.
And if the value is true, we return the order quantity, so I'll select this array, and if the answer is false, I'll return an empty string.
I'll close the two brackets and then press Ctrl + Shift + Enter to complete the formula.
And this gives me the correct answer of 90.
If I anchor the arrays by re-entering the formula, pressing F4, I can now copy this formula for ‘Laptop’ and ‘Desktop’.
Alt + E S F and press OK.
And this gives me the MAX order quantity for each product type.
As you can see from these two examples, Array formulas can provide a lot of functionality if you know how to use them correctly.
As an exercise, I left you to calculate the maximum order dollar amount for the months January, February and March.
The answer, as always, will be in the after file.