Array Formulas for Beginners

Joseph Arrays, Basic, Excel, Formulas 9 Comments

Excel Array formulas are one of the handiest tools to have under your Excel tool belt. You can expand regular Excel formulas to provide a lot more power to regular functions. Take the function AVERAGEIF() as an example.

Before Excel 2007, you had to use array formulas to provide the same functionality. And while array formulas are extremely powerful, they can also be one of the most confusing features of Excel to learn.

Array Formulas - I have no idea what I'm doing

So, in this post, let’s learn how to use Excel array formulas as easy as possible.

There is so much you can learn with array formulas in Excel that I could probably write an essay on the subject. However, for the sake of brevity, this will be a simple introduction to arrays to get you familiar with the concept.

What’s an array?

Some people reading this might not be sure what an array is. Basically, an array in Excel is typically a simple set of data. This data can be text, numbers, or both. It’s very similar to a list. If I were to ask you what you needed to buy from the grocery store, you might give me this list:

  • Milk
  • Eggs
  • Paper Towels
  • Sandwich Bags

Put into an Excel array format, it would look like:

{"Milk";"Eggs";"Paper Towels";"Sandwich Bags"}

The curly braces {...} denotes the set of data (in other words, the array itself). Each item in the “list” is separated by a semi-colon. For text items, they are encapsulated with quotations, like in the example above. Arrays can be a mixture of different data types like text and numbers in one array. The idea to remember is that an array is a list that you can put any type of data into.

Array formulas in Excel are entered by creating the formula like normal (begin with the equal sign, etc.), but instead of pressing Enter, you press Ctrl+Shift+Enter (press and hold Ctrl, press and hold Shift, then press Enter and let go of all three). After we finish entering the formula with Ctrl+Shift+Enter, the formula will be surrounded by curly braces {=…} as in the following example:

{=AVERAGE(IF($A$2:$A$10>20,$A$2:$A$10))}

Please note that you can’t actually type the curly braces into an Excel formula and have it become an array formula. You need to type Ctrl+Shift+Enter when editing the formula.

I would like to mention at this point that this example of a single-dimension array. We’ll cover multi-dimension arrays in another post.

So, What are Array Formulas?

The array by itself isn’t particularly useful. When you put them in conjunction with Excel functions is when you harness the true power of array formulas. Let’s start with a simple example.

A list to be used in an array formulas

If I were to take the average of these numbers, I get 25.44 (repeating, of course). Let’s add the condition that I only want to average those numbers that are greater than 20. To build the formula, I need to first grab all of the numbers that are greater than 20 (create an array of them), and then average those numbers.

We’re going to use the IF() function to return the list of numbers greater than 20. Here’s what this part of the formula looks like:

=AVERAGE(IF($A$2:$A$10>20,$A$2:$A$10))

When this formula is entered as an array formula (using Ctrl+Shift+Enter), it will take each argument and convert it into an array and process each element in all arrays like so:

=AVERAGE(
    IF(
    {36; 3; 28; 35; 12; 33; 29; 19; 34}>20,
    {36; 3; 28; 35; 12; 33; 29; 19; 34},
    )
)

The first argument is the condition. Excel will look at each element in the first array and check if it is greater than 20. If so, it will evaluate to TRUE, otherwise it will evaluate to FALSE. Take a look at the first value of 36:

=AVERAGE(
    IF(
    { 36 > 20; 3; 28; 35; 12; 33; 29; 19; 34},
    { 36;     3; 28; 35; 12; 33; 29; 19; 34}
    )
)

The green section 36 > 20 will evaluate to TRUE. When it is done checking all the conditions, it will create a new array of TRUE/FALSE for each element it checked and replace the old array with the TRUE/FALSE array like so:

=AVERAGE(
    IF(
    { TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE},
    {   36;     3;   28;   35;    12;   33;   29;    19;   34}
    )
)

Now the formula will match up those items which are true and return the corresponding number and return only those numbers that match up with TRUE:

=AVERAGE({ 36; 28; 35; 33; 29; 34})

Now the AVERAGE() function will work with averaging those numbers returned, which results in:

32.5

I hope this post helps you better understand how Excel works out an array formula. I’ll be posting more articles in the near future with more examples using array formulas. If you have any questions, feel free to comment below!

Are you ready to master Excel Dashboards?

Learn Excel Dashboard Course Power BI Webinar

Still not convinced? Check out my review of the course!

  • Nice post, Joseph! Looking forward to seeing more on this topic. 🙂

    • Hi Ken! Thanks 🙂 Definitely will be posting more on this topic. Feel free to submit any ideas 😉

  • Sriram.R

    Dear Joseph,

    The topic is Nicely explained. Kindly post few more examples describing usages of array formulas.

    Thanks,

    Sriram.R.Iyer

  • Hi, I do believe this is an excellent blog. I stumbledupon
    it 😉 I will revisit once again since I
    book marked it. Money and freedom is the best way to change, may you be rich and
    continue to guide others.

  • Jamie S

    This is a good start for my understanding of arrays. However, what I want to apply it to is more complex. Can you guide me on how to apply this to my task?

    This application has to do with assigning points to competitors in a league.

    Lets say there are 100 competitors. Column A has numbers from 1 to 100 sequentially, representing the places from first to last, assuming no ties. Column B lists the point values assigned to each of these finishes.

    Column C lists the ACTUAL finishes. There will be many ties. Thus T16 may be 16, 16, 16 instead of 16, 17, 18, followed by 19. Individual places may or may not have any ties. They could have, say a dozen ties. First place is the only place that will never have a tie.

    Column D is the ACTUAL point values assigned to each position. In the above scenario, if 16th place gets 150 points and 17th gets 147 points and 18th place gets 145 points, the three finishers will get a rounded-to-whole number average of the three values (=442 / 3 = 147.33 = 147). Once those values are obtained, I eliminate the duplicates & list the values for the positions for that tournament next to the place finished in a vertical list. In another worksheet, I do a VLOOKUP of the position finished to capture the points just calculated.

    Columns A & B are static. Column C is just copied in & pasted. The issue is Column D. Currently, I have to hand-assign the points based on a value I calculate by hand. I am sure that there is a way (very likely through arrays) to count the number of each occurrence, add that many consecutive values from Column B where the first value in the sequence matches the finish of these multiple players, divide & round for the average points for each one, and plug that value into at lest one of the cells in Column D for that place.

    If you can guide me through that process, I’m home free. Thanks!

    • Hi Jamie,

      You’re right, you situation is definitely more complicated. Could you please send me an email with a sample worksheet of what you have and the expected output?

  • gianelli lonio

    Good afternoon, can you help me with my statistics? I have a hard time doing it because I have overlapping of works…

    Thanks.

    • Hi Gianelli, I’m not the best at statistics myself, but feel free to ask.

  • Ajay

    Excellent article! Cleared the basics effectively. I will be checking out all your interesting articles now.

    Great work.