Carry out Multiple Calculations with Excel Array Formulas

Excel Array Formula Definition and Tutorial List

Array Formula Overview

In spreadsheet programs such as Excel and Google Spreadsheets, an array is a range or series of related data values that are normally stored in adjacent cells in a worksheet.

An array formula is a formula that carries out calculations - such as addition, or multiplication - on the values in one or more arrays rather than a single data value.

Array formulas:

Array Formulas and Excel Functions

Many of  Excel's built-in functions - such as SUM, AVERAGE, or COUNT - can also be used in an array formula.

There are also a few functions - such as the TRANSPOSE function - that must always be entered as an array in order for it to work properly.

The usefulness of many functions such as INDEX and MATCH or MAX and IF can be extended by using them together in an array formula.

CSE Formulas

In Excel, array formulas are surrounded by curly braces "{ } ". These braces cannot just be typed in, but must be added to a formula by pressing the Ctrl, Shift, and Enter keys after typing the formula into a cell or cells.

For this reason an array formula is sometimes referred to as a CSE formula in Excel.

An exception to this rule is when curly braces are used to enter an array as an argument for a function that normally contains just a single value or cell reference.

For example, in the tutorial below that uses VLOOKUP and the CHOOSE function to  create a left lookup formula, an array is created for the CHOOSE function's Index_num argument by typing the braces around the entered array.

Steps to Creating an Array Formula

  1. Enter the formula;
  2. Hold down the Ctrl and Shift keys on the keyboard;
  3. Press and release the Enter key to create the array formula;
  4. Release the Ctrl and Shift keys.

If done correctly, the formula will be surrounded by curly braces and each cell holding the formula will contain a different result.

Editing an Array Formula

 Any time an array formula is edited the curly braces disappear from around the array formula.

To get them back, the array formula must be entered by pressing the Ctrl, Shift, and Enter keys again just as when the array formula was first created.

Types of Array Formulas

There are two main types of array formulas:

  • multi cell array formula - the same formula is located in more than one cell in a worksheet
  • single cell array formula one formula that carries out multiple calculations in a single worksheet cell

Multi Cell Array Formulas

Like their name suggests, these array formulas are located in multiple worksheet cells and they also return an array as an answer.

In other words, the same formula is located in two or more cells and returns different answers in each cell.

How it doe this is that each copy or instance of the array formula performs the same calculation in each cell it is located in, but each instance of the formula uses different data in its calculations and, therefore, each instance produces different results.

An example of a multiple cell array formula would be:

{=A1:A2 * B1:B2}

If the above example is located in cells C1 and C2 in a worksheet then the following results would be:

  • the data in A1 is multiplied by the data in B1 and the results stored in cell C1
  • the data in A2 is multiplied by the data in B2 and the results stored in cell C2

Single Cell Array Formulas

This second type of array formulas use a function - such as SUM, AVERAGE, or COUNT, to combine the output of a multi-cell array formula into single value in a single cell.

An example of a single cell array formula would be:

{=SUM (A1:A2 * B1:B2)}

This formula adds together the product of A1*B1 and A2*B2 and returns a single result in a single cell in the worksheet.

Another way of writing the above formula would be:

= (A1 * B1) + (A2 * B2)

List of Excel Array Formulas

Below are listed a number of tutorials containing Excel array formulas.

  • The first tutorial covers multi cell array formulas
  • The next eight tutorials are about single cell array formulas using a variety of Excel functions
  • The last tutorial is on the TRANSPOSE function
Carrying Out Calculation with a Multi Cell Array Formula in Excel
Carrying Out Calculation with a Multi Cell Array Formula. © Ted French

A multiple cell or multi cell array formula is an array formula that is located in more than one cell in a worksheet. The same calculations are carried out in multiple cells using different data for each formula. More »

Summing Multiple Arrays of Data with a Single Cell Array Formula in Excel
Summing Multiple Arrays of Data with a Single Cell Array Formula. © Ted French

Single cell array formulas normally first carry out a multi cell array calculation - such as multiplication - and then use a function such as AVERAGE or SUM to combine the output of the array into a single result. More »

Use the AVERAGE-IF Array Formula to Ignore Errors
Use the AVERAGE-IF Array Formula to Ignore Errors. © Ted French

This array formula can be used to find the average value for the existing data while  ignoring error values - such as #DIV/0!, or #NAME?

It uses the AVERAGE function along with the IF and ISNUMBER functions. More »

Counting Cells of Data with the SUM IF Array Formula in Excel
Counting Cells of Data with the SUM IF Array Formula. © Ted French

Use the SUM function and IF function in an array formula to count - rather than sum - cells of data that meet one of several conditions.

This differs from Excel's COUNTIFS function which requires all set conditions be met before the cell is counted. More »

excel-2013-max-if-array-formula.jpg
MIN IF Array Formula in Excel. © Ted French

This tutorial combines the MAX function and IF function  in an array formula that will find the largest or maximum value for a range of data when a specific criterion is met. More »

Finding the Smallest Values with the MIN IF Array Formula in Excel
Finding the Smallest Values with the MIN IF Array Formula. © Ted French

Similar to the article above, this one combines the MIN function and IF function in an array formula in order to find the smallest or minimum value for a range of data when a specific criterion is met. More »

Find the Middle or Median Values with the MEDIAN IF Array Formula in Excel
Find the Middle or Median Values with the MEDIAN IF Array Formula. © Ted French

The MEDIAN function in Excel finds the middle value for a list of data. By combining it with the IF function in an array formula, the middle value for different groups of related data can be found. More »

Finding Data Using a Multiple Criteria Lookup Formula in Excel
Finding Data Using a Multiple Criteria Lookup Formula. © Ted French

By using an array formula a lookup formula can be created that uses multiple criteria to find information in a database. This array formula involves nesting the MATCH and INDEX functions. More »

Finding Data with a Left Lookup Formula using VLOOKUP in Excel
Finding Data with a Left Lookup Formula. © Ted French

The VLOOKUP function normally only searches for data located in columns to the right, but by combining it with the CHOOSE function a left lookup formula can be created that will search columns of data to the left of the Lookup_value argument.
  More »

Flipping Data from Columns to Rows with the TRANSPOSE Function in Excel
Flipping Data from Columns to Rows with the TRANSPOSE Function. © Ted French

The TRANSPOSE function is used to copy data located in a row into a column or copy data located in a column into a row. This function is one of the few in Excel that must always be used as an array formula. More »