Perform Multiple Calculations With Excel Array Formulas

An Excel array formula is a formula that carries out calculations on the values in one or more arrays rather than a single data value. In spreadsheet programs, an array is a range or series of related data values that are usually in adjacent cells in a worksheet.

What Are Array Formulas?

Array formulas are similar to regular ones. They:

• Begin with an equal sign ( = )
• Use the same syntax as regular formulas
• Use the same mathematical operators
• Follow the same order of operation

However, array formulas are surrounded by curly braces { }. And you can't just type them in; you must add them by pressing the Ctrl, Shift, and Enter keys after inputting the formula into a cell or cells. For this reason, an array formula is sometimes called a CSE formula in Excel.

There are two main types of array formulas:

• Single-cell array formulas that carry out multiple calculations in a single worksheet cell
• Multi-cell array formulas which are in more than one worksheet cell

How to Create an Array Formula

1. Enter the formula in a cell.

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.

5. If done correctly, curly braces will surround the formula.

Single Cell Array Formulas

A single cell array formula uses a function, such as SUM, AVERAGE, or COUNT, to combine the output of a multi-cell array formula into a single value in a single cell. Below is an example:

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

The formula above adds together the product of A1*B1 and A2*B2, and then it returns a single result in a single cell in the worksheet. Another way of presenting that formula is:

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

Multi-Cell Array Formulas

As their name suggests, multi-cell array formulas are in multiple worksheet cells, and they return an array as an answer. In other words, the same formula is in two or more cells, and it returns different answers in each cell.

Each copy, or instance, of the array formula, performs the same calculation in each cell it inhabits, but each one uses different data. Therefore, each one produces different results. An example of a multiple cell array formula is:

{=A1:A2*B1:B2}

If the above array formula is in cells C1 and C2 in a worksheet, then the results would be as follows:

• The data in A1 is multiplied by the data in B1, and the results appear in cell C1.
• The data in A2 is multiplied by the data in B2, and the results appear in cell C2.

Array Formulas and Excel Functions

You can use many of Excel's built-in functions, such as SUM, AVERAGE, and COUNT, in an array formula. There are also a few functions, such as TRANSPOSE, that must always be an array formula to work correctly. (The TRANSPOSE function copies data from a row into a column or vice versa.)

You can also extend the usefulness of many functions such as INDEX and MATCH or MAX and IF by using them together in an array formula.

Create a Simple Single Cell Array Formula

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

Ignore Error Values when Finding Data

This array formula uses the AVERAGE, IF, and ISNUMBER functions to find the average value for the existing data while ignoring error values such as #DIV/0! and #NAME?

Count Cells of Data

Use the SUM and IF functions in an array formula to count cells of data that meet one of several conditions; this technique differs from using Excel's COUNTIFS function, which requires that all set conditions be met before it counts the cell.

Find the Largest Positive or Negative Number

This example 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 it meets specific criteria. Here, the largest value represents the slowest time.

Find the Smallest Positive or Negative Number

Similar to the example above, you can combine the MIN and IF functions in an array formula to find the smallest or minimum value for a range of data when it meets specific criteria.

Find the Middle or Median Value

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, you can find the middle value for different groups of related data.

Make a Lookup Formula with Multiple Criteria

This array formula involves nesting the MATCH and INDEX functions to find specific information in a database.

Make a Left Lookup Formula

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

When entering an array as an argument for a function that usually contains just a single value or cell reference, you can type in the braces directly, rather than using the Ctrl+Shift+Enter keystroke combination, as in the above example.