5 Examples of Using Array Formula in Excel

If you are a regular user of Excel, you most likely know about the array formula in Excel. Also, you could be aware of this, array formulas lessen our time and effort. In this article, we will explain how an array formula works in the memory of Excel. “In the memory of Excel” means how Excel processes an array formula. Here, we’ll discuss this topic with some Excel array formula examples. So, let’s start.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Introduction to Array in Excel

Before going into the details, let’s dwell a little on what an array in Excel is.
Generally, an array is a group of things. The elements may take the form of texts or numeric values, and they can place themselves in a single row, a single column, or numerous rows and columns. Let’s talk about the picture below.

excel array formula examples

From the image above, we can understand visually what an array is. Here, we have shown two types of arrays. One-dimensional and two-dimensional. We can differentiate one-dimensional arrays into two sub-types: horizontal and vertical.


Basics of Array Formula

Prior to discussing Excel array formula examples, we’ll know about array formulas first. An array formula differs from a standard formula in that it handles numerous items as opposed to a single value. In other words, a formula for an array in Excel examines each value in the array individually and executes several computations on a single or a group of items in accordance with the conditions stated in the formula. There are two types of array formulas.

  • First one that gives result in a single cell
  • Another one, that gives the output as an array also.

We can utilize array formulas in Excel for analysis of data, sums with criteria and different types of lookups, linear algorithms, calculation of matrices, and a lot more.


5 Examples to Use Array Formula in Excel

In this section, we’ll see some examples using the array formula in Excel. For ease of understanding, we’re utilizing a Mark List of Students of a certain institution. This dataset includes the Names of the students, and their corresponding marks in Maths and Science in columns B, C, and D respectively.

5 Examples to Use Array Formula in Excel

Now, we’ll use this dataset to show our first two examples. So let’s explore them one by one.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience. And if you face any inconvenience during usage, please let us know in the comment section.


Example 01: Calculating Total Marks of Students

In our first example, we’ll calculate the total marks for each student using an array formula in Excel. It’s simple and easy. Just follow along.

📌 Steps:

  • At the very beginning, create a new column Total Marks under Column E.

Calculating Total Marks of Students

  • Then, select cell E5 and enter the following formula.
=C5:C14+D5:D14
  • After that, press ENTER.

Note: Check the image carefully until you see a light blue line around the cells in Column E. This is because they are in an array. We didn’t use the Fill Handle or anything like that to fill these cells.

Here, we’re using Microsoft Excel 365 version. So, we can run the array formula by pressing ENTER. But, in the older version, you must press CTRL + SHIFT + ENTER to use the array formula.

  • So, go to cell E5 and enter the same formula.
  • After that, press CTRL + SHIFT + ENTER at a time.

Using Array Formula in Excel to Calculate Total Marks of Students

Note: After pressing these buttons, a pair of curly brackets will automatically be applied within two sides of the formula. You don’t have to write them manually.

Also, you can’t make any changes inside the cell of an array. Excel will not allow you to do this. If you ever try to do this kind of thing, the following phenomena will happen.

  • Here, go to cell E8 and change the cell element to 5.
  • After that, press ENTER.

Immediately, Excel will show a warning box on the display like the image below.

Excel Showing Warning Box while Trying to Change Any Item inside the Array


Example 02: Determining Highest Marks Obtained

Now, we want to discover the highest marks obtained by the students on any of the two subjects. So, let’s begin.

📌 Steps:

  • Firstly, we’ve made an output range in B16:C16.
  • Secondly, select cell C16 and write down the formula below.
=MAX(C5:D14)

Here, the MAX function returns the maximum value among the numbers in this range.

  • Lastly, press ENTER on the keyboard.

Determining Highest Marks Obtained Using Excel Array Formula


Example 03: Excel Array Formula with Multiple Criteria

In this example, we’ll show you an array formula that will return a two-dimensional array with multiple criteria. Here, we’ve got the Name, Section, Subject, and Mark in columns B, C, D, and E consecutively. Also, we got Section A and Maths as the Subject in cells in the B16:E17 range.

Excel Array Formula with Multiple Criteria

Now, we’ll filter out the above array in the B5:E14 range with the criteria in the B16:E17 range. We can clearly see there are two criteria. One is Section, another is Subject. So, obviously, there are multiple criteria for filtering. So, without further delay, let’s dive in!

📌 Steps:

  • At first, copy the headings in the B4:E4 range and paste them into the B19:E19 range.
  • Then, go to cell B20 and paste the following formula into that cell.
=FILTER(B5:E14,(C5:C14=B17)*(D5:D14=D17),"")

Here, B5:E14 represents the range of the array. C5:C14 serves as the Section column. B17 performs as the section to be filtered. D5:D14 means the Subject column. Lastly, D17 acts as the subject to be filtered.
The FILTER function has three arguments. Here, B5:E14 is the array argument. And (C5:C14=B17)*(D5:D14=D17) is the include argument. Basically, this works like a Boolean array; it carries the condition or criteria for filtering. In this case, we’ve two criteria combined by a (*) sign.

  • After that, tap ENTER.

Filtering Range with Excel Array Formula


Example 04: Computing Total Cost based on Quantity

This example is actually fun to learn. You can apply this theory to other problems as well. Here, in Column B, we have the Quantity of Unit. And, in Column C, there is their corresponding Unit Price. In essence, we want to know how much this will cost if we order a certain quantity of units.

Computing Total Cost based on Quantity

From the picture above, we can see that the unit price is decreasing gradually with the increase in order amount. So, it will cost us less if we order in bulk. Let’s see it in action.

📌 Steps:

  • Initially, go to cell C12 and put the following formula into the cell.
=C11*IF(C11>=101,C9, IF(C11>=51, C8, IF(C11>=21, C7, IF( C11>=11, C6, IF(C11>=1, C5, "")))))
Formula Breakdown
  • IF(C11>=1, C5, ” “) →The IF function checks whether a condition is met, and returns one value if TRUE, and another one if FALSE.  Here, C11>=1 is the logical_test argument which compares the value of the C11 cell with 1. If this value is greater than or equal to 1 then the function returns the value of cell C5 (value_if_true argument) otherwise it returns blank(value_if_false argument).
    • Output → 20
  • IF( C11>=11, C6, IF(C11>=1, C5, “”)) → this becomes IF( C11>=11, C6, 20).
    • Output → 19
  • IF(C11>=21, C7, IF( C11>=11, C6, IF(C11>=1, C5, “”))) → this becomes IF(C11>=21, C7, 19).
    • Output → 16
  • IF(C11>=51, C8, IF(C11>=21, C7, IF( C11>=11, C6, IF(C11>=1, C5, “”)))) → this becomes IF(C11>=51, C8, 16).
    • Output → 15
  • IF(C11>=101,C9, IF(C11>=51, C8, IF(C11>=21, C7, IF( C11>=11, C6, IF(C11>=1, C5, “”))))) → this becomes IF(C11>=101,C9, 15).
    • Output → 15
  • C11*IF(C11>=101,C9, IF(C11>=51, C8, IF(C11>=21, C7, IF( C11>=11, C6, IF(C11>=1, C5, “”))))) → this becomes C11*15.
    • Output → 80*15 → 1200
  • Following this, press the ENTER key.

Computing Total Cost


Example 05: Finding out Average of Positive Numbers

In this worksheet, you see there are some numbers in Column B of the worksheet. Some numbers are positive, and some numbers are negative. We are going to find out the average of the positive numbers in this range.

Finding out Average of Positive Numbers

You can do it in two ways. The first way is: separate the positive numbers manually, create a new range, and at the end use the AVERAGE function to find out the average of the positive numbers. Another way is: we can use an array formula. It will save us time and effort. So, without further delay, let’s see how we can do it.

📌 Steps:

  • Primarily, go to cell C5 and enter the following formula.
=AVERAGE(IF(B5:B14>0,B5:B14,FALSE))
Formula Breakdown

Observe closely the arguments of the IF function. You know how the IF function works. If the first argument of the function is TRUE, then the second argument is returned by the IF function. If the first argument is FALSE, then the third argument is returned by the IF function. Arguments are separated by commas.
In our case, the third argument is FALSE, so if the first argument is FALSE, then the IF function will return the FALSE statement.
OKAY! The main focus is now on the first argument and the second argument. You see the first argument is a range, and the second argument is also a range. And the whole formula is an array formula.

  • IF(B5:B14>0,B5:B14,FALSE) → here Excel will create an array internally with the positive numbers and False statements.
    • Output → {15, FALSE, 14, FALSE, FALSE, 45, 78, 65, 54, FALSE}
  • AVERAGE(IF(B5:B14>0,B5:B14,FALSE)) → this becomes AVERAGE({15, FALSE, 14, FALSE, FALSE, 45, 78, 65, 54, FALSE}).
    • Output → 45.167

The AVERAGE function finds out the average of the values in the array, except the FALSE values, the False values are not numbers, so the AVERAGE function neglects the FALSE values.

  • As usual, press ENTER.

Determining Average using an Array Formula

So this is how an array formula works. You see the formula starts with the opening curly bracket and closes with the ending curly bracket, so this is undoubtedly an array formula. You know this curly bracket is applied on an array formula automatically by Excel when you press CTRL, SHIFT, and ENTER keys simultaneously on your keyboard.


What to Do If Excel Array Formula Is Not Working

This article is based on the Excel 365 version. Therefore, if you are using an older version of Excel than the Excel 2019 version, then you must press CTRL + SHIFT + ENTER to use the array formula. If you just press ENTER, then your array formula will not work except for some functions such as the AGGREGATE and SUMPRODUCT functions.

However, you may explore 4 solutions if array formula is not showing results in your Excel file.


Things to Remember

  • You cannot construct an array with an entire column or multiple columns. Because this is very greedy oligarchs. It can cause malfunctioning of the systems.
  • You can use the F9 key to debug any part of an array formula. It will make it easy to evaluate them.
  • You need not place the curly bracket yourself. Excel will do it automatically.
  • Remember that you cannot make any change to a cell in an array. You have two choices. Either edit the formula or delete the formula and recreate it in your preferred way.

Practice Section

Though it is easy, it needs practice. That’s why we have attached practice sheets for you so that you can internalize the methods that you find easy and useful. Please do it by yourself. If you find any difficulty while doing practice, please inform us.

Practice Section


Conclusion

So this is how an array formula works. In this article, I have just introduced you to array formulas. Its scope is much wider. In this article, we have just shown some of the excel array formula examples. Don’t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

12 Comments
  1. Big-time Thank You!
    I am finally starting to “get it”! So many texts and web helper sites seem to be speaking either to infants or to PHDs. Your tone and style is simple without being inane. Interesting without being unnecessarily complicated. Great job!

  2. This is in awesome website and you are much more awesome Kawser!!!. I m loving excel now and will refer this to my friends too!!!. I will request you to plz share a practice worksheet containing advance excel problems for self practice. You should share the solutions on periodic basis. Thanks

  3. Really straight forward and easy to understand… I am off to search for more of these array posts from you. Excited to see which is next!

    • Hello Jay, thanks for your nice words. Please visit our blog for more array posts! We have written more posts like this time by time.

  4. Thank you very much for this tutorial, very useful in understanding what an array is and how it works in Excel.

  5. Excellent

  6. Hi

    Got your “Excel Array Formula Basic 2 – Breakdown of Array Formula” great.
    Question what is the name or address of Basin1

    This would help me with Array Formula.
    Bill

Leave a reply

ExcelDemy
Logo