7. More On Array Formulas

 
Subtitles Enabled

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

Free trial

Overview

In this lesson, we’ll see how to create array constants, and use them to calculate an array of sales with low revenue. We’ll also see how to use an array formula to count unique values in a dataset.

Summary

  1. Array Constants (00:19)

    Array constants are arrays with values that are fixed and pre-defined, instead of being defined by a formula. To create an array constant, we specify a series of terms within braces, for example {1,2,3,4}. We use commas and semicolons to indicate the shape of the array, for example:

    • {1,2,3,4} creates a row of four cells containing the numbers 1, 2, 3 and 4.
    • {1;2;3;4} creates a column of four cells containing the numbers 1, 2, 3 and 4.
    • {1,2:3,4} creates a 2x2 matrix with 1 and 2 on the top row and 3 and 4 on the bottom row.
  2. Finding the Smallest Orders (02:37)

    From an array of orders, we may want to find the orders with the smallest order quantity. We can do this using the SMALL function. SMALL takes two arguments. First is the array of data we want to search. Second is k, which indicates the position in the array of the value to return. For example, if k is 1, the function returns the smallest value, if k is 2 it returns the second smallest value, and so on.

    To find the 5 smallest values in the array, we use an array constant like {1;2;3;4;5} to return the 5 smallest values in the array.

  3. Counting Unique Customers (03:45)

    A more complex use of array formulas is to find the number of unique customers in the data set. To do this we use SUM and COUNTIF to create an array formula. COUNTIF creates an array that counts how often each customer appears in the data set. For example, the customer Sarah appears 5 times, so the number 5 will appear 5 times in this array.

    Dividing this array by 1 means that ⅕ will appear 5 times instead, so the sum of entries for Sarah will equal one. Each other customer will also have a series of entries adding to one. As a result, the sum of the whole array tells us the number of customers in the data set.

Transcript

Array formulas can be used in a wide variety of situations. Having introduced the concept of array formulas in the last lesson, in this lesson, we'll look at several examples of situations where you might find it useful to use an array formula. Let's start by looking at the concept of array constants, as we'll use these later on. Array constants are arrays who's values are defined directly by us instead of being defined by a formula or cell reference. Let's create a few array constants to understand the concept. We'll start by creating an array covering four columns and one row. So I'll select four cells in a horizontal row.

I'll then type one comma two comma three comma four. Close the bracket, and Ctrl + Shift + Enter to create an array formula.

It's important to note that when entering array constants you need to enclose the formula in braces yourself unlike with array formulas.

We can also create this array in a vertical shape. This time, I'll select four vertical cells, again type equals, brace, one, comma enter.

Unfortunately, this didn't work correctly. I don't have an output of one, two, three, and four. And the reason is, when creating an array constant vertically, I need to use semicolons between each number not commas. So let's jump back into the formula, and replace the comma with a semicolon and then recreate the array formula. And now you can see I have my desired output.

Finally, we can create an array across multiple rows and multiple columns. This time, let's highlight two by two matrix. And I'll write equals, open the brace, and I'll write one comma two colon three and then comma four.

Close the brace and Ctrl + Shift + Enter.

And as you can see, I have my numbers aligned correctly within a two by two cell block. Now that we can create array constants, let's use them in an array formula. We'll return to to our Q1 sales data set and create a formula that finds the five smallest order quantities, which is in column E.

I'll first select my cells, and we're going to use the small function to perform our calculation. And the small function returns the Kth smallest value in a dataset. And what this means is that in the formula, if I write a value for K, say three, it returns the third smallest value. Let's start by selecting the array, which will be in column E, and then we'll create our array constant. So open the brace, one semicolon two, semicolon three, four, and five. I'll then close the brace, close the bracket, and Ctrl + Shift + Enter to create the array formula.

And this now gives me the five smallest order quantities. Next, we'll use an array constant to find the count of unique customers.

To accomplish this task, we're going to use the sum formula and we're going to sum one divided by the countif function. And the countif function will simply count if the customer column is equal to the customer column. While this may be confusing in theory, I'll show you how it works as we write the equation. So let's start by writing equals sum, open a bracket, and type one divided by countif.

And the range will be the customer name, which is b2 to b24. And the criteria will be b2 to b24 as well.

I'll then close my brackets, and Ctrl + Shift + Enter to create an array formula. And this tells me that I have five unique customers. But how does this formula actually work? In this formula, the countif function creates an array counting the number of times each customer appears in the data set. So for example, customer Sarah, who appears five times, will return the number five. For each of the five entries, our formula will take one divided by the number five. Dividing one by this countif means that each of these fives will become a fifth. This means that Sarah now has five one fifths stored in the array for a total of one.

Each of the other customers who will also have a set of entries adding up to one will also appear in the final output. So the sum function will in fact return the number of unique customers in the dataset.

And from this calculation, we can see the count of unique customers is five.

As we've seen in this lesson, array formulas and array constants can be used in a wide variety of situations. If you understand the principles behind array formulas, then you might spot other situations where they can be useful and greatly improve your productivity in creating formulas in the process.

Excel Excel for Business Analytics Learning Plan
Formulas and Functions

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial