Array formulas in Excel

In Excel you can perform some powerful and useful operations using array formulas. This Tutorial together with the examples in the workbook introduces basic array and application of more advanced formulas.

Introduction to Excel array formulas

An array formula is a formula that can perform multiple calculations on one or more of the items in a range. Array formulas in Excel workbook can be placed either:

• in a range of cells (multi-cell formula) or
• in a single cell (single-cell formula) to calculate single amount

How to enter an array formula?

In most cases array formulas use standard Excel formula syntax however a trick is needed to complete the coding.

• type the formula in the cell begin with ‘=’
• press the CTRL+SHIFT+ENTER keys at the same time rather then just ENTER
• CTRL+SHIFT+ENTER must be done the first time the formula is entered and whenever the formula is edited later
• Then Excel will display the formula enclosed in curly brackes { } – don’t type the { } manually.
• If you neglect to enter the formula with CTRL+SHIFT+ENTER, the formula may return a #VALUE error or return an incorrect result.

Screenshot 1: Data for Example

Basic array formula

Refer to the Screenshot 1. The first set of steps in this example uses a multi-cell formula to calculate a set of revenues. The second formula uses a single-cell formula to calculate total revenue.

Multi-cell formula

How to calculate revenue for each product using array formula?

• Select range H8:H22
• Type formula = F8:F22*G8:G22 in formula bar
• Press CTRL+SHIFT+ENTER
• Excel surrounds the formula with braces ({ }) and places an instance of the formula in each cell of the selected range
• You can also name defined F8:F22 as “Quantity” and G8:G22 as “Price”

{=F8:F22*G8:G22} or {=Quantity*Price} 

Screenshot 2: Multi-cell array formula

Single-cell formula

Screenshot 3 shows how to calculate total revenue in a single-cell array formula

• Select a single cell, e.g. H23
• Type formula = SUM (Quantity * Price)
• Press CTRL+SHIFT+ENTER, and the formula will appear as:

{=SUM(Quantity * Price)} 

Screenshot 3: Single-cell array formula

Copy-and-Paste using arrays

This component of array formulas called array constants, where you need to add sets of values that don't change (such as names) to your array formulas. TRANSPOSE function can be used to convert the range from column to row or vice versa. Refer to examples in the workbook.

Advanced Array Formula

The examples here show you some useful ways to apply advanced array formulas. Refer to Screenshot 4, the entered array formulas are highlighted in blue.

Exclude range that contains errors

The Excel functions do not work when you try to calculate a range that contains an error value such as #N/A. These formulas ignore the errors.

{=SUM(IF(ISERROR(RANGE),"",RANGE))}

{=MIN(IF(ISERROR(RANGE),"",RANGE))}

{=AVERAGE(IF(ISERROR(RANGE),"",RANGE))}

Exclude range that contains zeros

This example shows you how to remove zeros from a range when you need to calculate the minimum or average the values in that range.

{AVERAGE (IF(RANGE<> 0, RANGE))} 
{MIN (IF( RANGE<>0,RANGE )}

Sum values based on conditions

You often need to sum values based on conditions. For example, this array formula sums just the positive integers in the range:

{=SUM(IF(RANGE>0,RANGE))}

You can also sum values that meet more than one condition such as shown in Screenshot 5.

Count the number of differences

This might be useful when we are doing reconciliation. To use this formula, the cell ranges must be the same size and of the same dimension.

{=SUM(IF(RANGE1= RANGE2,0,1))

Screenshot 4: Advanced array formulas

Advantages and Disadvantages

• Consistency: You see the same formula on any of the cells in the range, which ensure greater accuracy.
• Safety: You cannot overwrite part of a multi-cell array formula.
• Speed: You can often use a single array formula instead of several intermediate formulas.
• Smaller file sizes

Disadvantages

• You may forget to use CTRL+SHIFT+ENTER
• The formulas are relatively undocumented - other users may not understand your formulas