• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
EngineerExcel

EngineerExcel

FREE EBOOK
  • About
  • Course
  • Free Training
  • Resources
  • Login

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:

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

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 (CL), 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:

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

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.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

Primary Sidebar

Featured Posts

Importing Data into Excel from Text Files

Complex Numbers in Excel

3 Ways to Change the Data in an Excel Scatter Chart

Constrained Optimization in Excel – Maximize Open Channel Flow

SUMIF and SUMIFS Excel Functions for Engineering Calculations

About Me: Charlie Young, P.E.

I’m a licensed professional engineer with a degree in Mechanical Engineering and over a decade of practical experience building engineering applications in Excel. My goal is to help you learn how to turn Excel into a powerful engineering tool.

If you’re interested in learning more, click the button below to receive an update whenever I have a new tip to share. I’ll also send you a copy of my free eBook “10 Smarter Ways to Use Excel for Engineering”.


Subscribe Now

Footer

SOCIAL

Keep up with EngineerExcel:
  • Email
  • RSS
  • YouTube
EngineerExcel

Free Course

Navigation

  • Home
  • About
  • Free Course
  • Excel Tips
  • Resources
  • Coupon
  • Login

Support

  • Support
  • Terms
  • Privacy

Copyright © 2021 EngineerExcel.com · Log in