6. Array Formulas

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

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.

Summary

  1. What are Array Formulas? (00:04)

    Array formulas let you perform multiple calculations on one or more items in an array of cells. The output can be either a single cell or an array. They can be used in situations where an inbuilt function doesn’t exist to meet your needs. Examples of this could include MAXIF and MINIF, which require you to use an array function.

  2. A Simple Example (00:42)

    To find total revenue, we need to sum the revenue for each order. The revenue for an order equals its price multiplied by its quantity. We therefore create a formula which is the sum of the price array multiplied by the quantity array.

    If we create the formula by pressing Enter, we get an error. Instead, we create an array formula by pressing Ctrl + Shift + Enter, which completes the calculation correctly. Creating an array formula creates a set of braces, i.e. { and } around the formula. These braces can be used to identify an array formula. You don’t type the braces yourself, instead they’re created automatically when you press Ctrl + Shift + Enter.

  3. The TRANSPOSE Function (01:59)

    TRANSPOSE converts columns of data to rows, and vice versa. It takes a single argument, which is an array of cells, and returns the transposed version of the array. The output from TRANSPOSE spans multiple cells. When a formula output spans multiple cells, we need to preselect these cells before we write the formula. After writing the formula, we need to press Ctrl + Shift + Enter to create the formula.

  4. Creating a MAXIF Function (03:00)

    We aim to find the largest order quantity for a particular product category. To create a MAXIF function, we create a MAX function, then create an IF statement inside it. The IF statement checks the product type array for the specific type we are interested in, and returns the order quantity array for the appropriate product type. As with all array formulas, we press Ctrl + Shift + Enter to create the formula.

Transcript

In the previous lesson, we learned how to name and anchor cells in Excel. In this lesson, we'll learn how to use array formulas. Array formulas, 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 in build functions over building your own array formulas. But sometimes an in build 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 over and look at array functions with the simple example of calculating the total revenue for Q1 sales data. We can accomplish this by simply adding up the order dollar 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. Let's start off by writing equals sum and we'll first select the product price array.

So control+shift+up arrow and then we'll multiply this number by the order quantity array.

If I then close the brackets or press enter, unfortunately I get a value error. If we now go back into the formula with F2, and instead press control+shift+enter, the formula now works and the revenue is calculated. The control+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 insures that the output from the array is only one cell. However many array functions output multiple cells. Let's look at another common array function 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 equals 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 control+shift+enter, I get the first value. To 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 select F2 to go back into the function, and now control+shift+enter.

And this transposes all five output values. So when you're outputting several cells from an array function, always make sure to preselect the destination cells before pressing control+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, we'll 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'll 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 the press control+shift+enter to complete the formula.

And this gives me the correct answer of 90.

If I anchor the arrays by reentering the formula, pressing F4, I can now copy this formula for laptop and desktop. Alt+E+S+Fand press okay.

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'd like you to calculate the maximum order dollar amount for the months, January, February, and March. The answers, as always, will be in the answer file.