After Mike “ExcelIsFun” Girvin’s appearance on ExcelTV, it was clearly time to embrace array formulas. Up until that episode, array formulas just freaked me out. I would go onto forums and ask for help, and sometimes the solution would be an array formula. OMG!

The solutions worked but it wasn’t clear what was the alert, the sign, the entrance into the hole in the universe … what was the moment where we got stuck on this array formula highway?

WHAT IS AN ARRAY FORMULA?

Here’s how Mike Girvin broke it down for us on ExcelTV:

“An array formula at its essence makes an array calculation and returns multiple items.

“When you have those multiple items, you can do an aggregate calculation on them which means the array formula would go into a single cell. Or, you can have the array formula deliver multiple items to multiple cells.”

What does that mean?

1. Non-array formulas can crank through multiple cells (an array) and bring back a single answer (not an array).
2. However, in the example below, we use COUNTIFS on an array, and want to retrieve the actual items (i.e.,we want an array of the items that are counted), not just the count of the items. We will then “aggregate” that array by selecting the minimum value and returning only that value.

Thinking about it that way lead to …

The A-HA Moment!

Using something like COUNTIFS, an entry either fits the criteria or it doesn’t. It gets counted or it doesn’t. But in the example below, we will want the specific items, and that’s what Mike Girvin refers to as Data Extraction: bring us the items that fit our criteria so that we can see them or do work on just those items.

So, there’s an invisible step: the Data Extraction!

That’s when the lightbulb came on and I realized: we need an array formula, or a helluva lot of helper columns and IF statements.

ARRAY FORMULA EXAMPLE

Scenario: A List of Assigned Tasks

In the Task Table:

• 5 People
• 22 Tasks with Due Dates
• 1 of the 5 People is assigned to each Task
• Status of either Open or Done

What Do We Want? The Closest Due Date For Each Person’s Open Tasks

In the Summary Table we want to fill in the Closest Due Date column

In this example, we can scan the Tasks Table and see that that Deion’s next open task is due on 19SEP14, and Leesa’s next open task was due on 30JUN14. Those are 2 results that our array formula should return.

The Array Formula

NOTICE: the datasets are in tables and the formulas use table structured references

The curly brackets cannot be typed in!

Type in cell D4:

=MIN(IF((Tasks[Name]=[@Person])*(Tasks[Status]=Summary[[#Headers],[Open]]),Tasks[Due]))

Hit: CTRL+SHIFT+ENTER

{=MIN(IF((Tasks[Name]=[@Person])*(Tasks[Status]=Summary[[#Headers],[Open]]),Tasks[Due]))}

The curly brackets appear

Also note: Since the dataset is in a table, the formula will populate the entire column and create our results. I’ve also added a formula for # of Days ’til Due, and data bars.

# of Days ’til Due =[@[Closest Due Date]]-TODAY()

THE INVISIBLE STEP REVEALED

This is probably what makes array formulas so spooky. There is data extraction that’s happening behind the scenes. Let’s rip down the curtain by omitting the MIN part of the formula.

Leesa has 3 open tasks. Let’s get their due dates.

Highlight L4:L25, go to the formula bar and type:

=IF(((G4:G25)=”Leesa”)*((I4:I25)=”Open”),J4:J25)

finish with CTRL+SHIFT+ENTER

Notice that we highlighted a range of cells, typed the formula just once, made reference to an array, and we generated an array. When we then look for the MIN, the array is aggregated to bring back 30JUN14 and Leesa still hasn’t purchased the color-change paint.

Also notice, that this was just Leesa. If we did this with a non-array formula, we could use IF statements in column L, then columns M thru P for the other folks, and then hide those columns. Then use a MIN formula for each person to get their closest due date. That would work. It’s also messy.

CONCLUSION: ARRAY FORMULAS ARE WORTH THE MENTAL SHIFT

Yes, there are a lot of moving parts that we juggled in the array formulas but, we used a single formula and saved a lot of space. It’s that invisible step and subsequent aggregation that was freaking me out.

It would be great to hear from other people about their relationship to array formulas. Are you comfortable with them? Do they freak you out? What would you say about the difference in thinking for array and non-array formulas? Please comment.

For way more on array formulas

The Mastering Array Formulas playlist at the ExcelIsFun YouTube channel!

A paper version of CTRL+SHIFT+ENTER can be purchased at Amazon.com

The ebook version can be purchased by clicking the icon below:

lightbulb photo credit: nhuisman via photopin cc