Array Formulas in Excel for Engineering Calculations

Array formulas allow you to use multiple cell inputs rather than a single cell input. There are two types of array formulas. One type of array formula returns a single value from multiple inputs. A common example is the SUM function:

The other type of array formula is one that returns multiple outputs. A simple addition can be used as an array formula:

Creating the second type of array formula is a little different from what you’ve done so far. The biggest difference is that you’ll use Ctrl-Shift-Enter to finish the formula.

What’s the point of array formulas? We’ve already seen how to fill formulas into other cells using the fill handle. There are three benefits to using array formulas:

1. They can reduce errors in spreadsheets. Because you are forced to edit the entire array at once, you or someone else cannot mistakenly modify a formula in the middle of a range of cells.
2. They simplify spreadsheets by allowing you to combine calculations that would take multiple cells into a single cell.
3. They allow us to use named ranges on a selection of cells. This significantly improves formula readability in Excel. Named ranges will be discussed later in this chapter.

The worksheet contains an example where we can use an array formula. It’s set up to calculate airfoil lift as a function of varying velocity (v) and constant lift coefficient (C_L), area (A), and air density (ρ). The formula to calculate airfoil lift (L) is:

First, we’ll calculate the lift without using an array formula. Enter the formula in as

=C5*C6*C7*F7^2/2

The constant values in column C need to be absolute references, so click within each one and type F4. Leave F7 as a relative reference.

Double-click the fill handle in the lower right corner to fill the rest of the column with this formula.

One problem with performing the calculation this way is that it’s possible to mistakenly edit a single cell. For example, if you were to delete the denominator from a single cell’s formula, that cell’s value would be incorrect:

Excel flags the altered cell with a small green triangle to indicate that it doesn’t match the surrounding cells, but those flags can be hidden or even ignored altogether.

Using an array formula will minimize the potential for errors. There are a few differences in how array formulas are entered compared to standard formulas.

First, select all the cells that you want to return results to. Rather than enter the formula into the cell itself, click within the formula bar and enter it there.

Enter =C5*C6*C7* in the formula bar, then select the entire range of velocities. You should have:

=C5*C6*C7*F7:F16

Square the velocities by adding ^2, then divide by 2.

=C5*C6*C7*F7:F16^2/2

To finish the array formula, type Ctrl-Shift-Enter. This is an important step to remember when using array formulas.

In this case, it’s unnecessary to make the constants absolute references. The formula won’t be copied into any other cells, so the reference won’t change.

When you complete the array formula with Ctrl-Shift-Enter, Excel will automatically add curly brackets on either side of the formula to indicate it’s an array formula:

If you try to edit any single cell in the array, Excel will give an error message. To edit the formula, select all of the output cells in the array, make your edit, then again type Ctrl-Shift-Enter.