Understanding basic array formulas

Understanding Basic Array Formulas Thumb

Understanding Basic Array Formulas

Have you heard of the mysterious “array formulas”?  Do you use them?  Or are you like most Excel users, you’ve placed them to the “too hard” pile, never to be seen again.  They always seem to crop up in forums as solutions to some tricky formula questions.

In this post, I want to introduce to you one type of array formula which is actually pretty easy to understand and can be applied to lots real life situations.  Once you’ve grasped the concept of this type of array formula, you’ll be able to apply it over and over again.  Your work colleagues will look at you like you’re an Excel genius, they will be mesmerized by the apparently impossible calculation you’ve just performed . . . but secretly you’ll know it wasn’t that difficult.

 

Array formula basics

An array formula is a calculation which works with an array or series of data.  Don’t worry if that concept blows your mind at the moment, by the end of this post it’ll hopefully make sense.

Often array formulas are entered by pressing Ctrl + Shift + Enter – hence why they are sometimes referred to as CSE formulas.  There are array functions which do not require Ctrl + Shift + Enter to be used.  So, just be aware that Ctrl + Shift + Enter does not apply to all array formulas.

The type of array formula we will be using in this example does require Ctrl + Shift + Enter.  Once the formula has been entered into the formula bar, do not just press Enter, but press Ctrl + Shift + Enter at the same time.  You will know when it has worked, because Excel will automatically show a ” { ” (opening curly bracket) at the start of the formula and a ” } ” (closing curly bracket) at the end of the formula.

You must press Ctrl + Shift + Enter, not just when creating the formula the first time, but any time you edit the formula.

In the examples below I will show the { } curly brackets, but remember, don’t type these, Excel will display these by itself.

 

Basic array formula example

For our example, we will be looking at the type of array formula which returns the result into a single cell.

We are going to recreate the SUMIF function as an array formula. Why?  Hopefully you already understand the SUMIF function, so the concepts will be easier to understand.  The goal is not to recreate a SUMIF using an array formula, but to learn the concepts so you can apply it to your own scenarios.

Look at the table of data below, it shows the quarterly sales for 3 departments of a clothing store.  If we wanted to know the annual total for any department we could use the SUMIF function.

Basic Array Formulas - SUMIF

The value in Cell F4 is calculating the total sales for the Men’s clothing department:

=SUMIF(A2:A13,F2,C2:C13)

Let’s break down the SUMIF function down into its two parts SUM and IF.  Logic requires that the IF function must be calculated first, then the SUM function can be used to calculate the total.  This is what we will do.

The first step to convert SUMIF into an array formula is to create an IF function for a single row.

=IF(A2=F2,C2,0)

The second step is to expand the range to include all the cells required for the calculation (this is what makes this an array formula, as it calculates based on an array of cells).

=IF(A2:A13=F2,C2:C13,0)

The third step is to include the SUM function around the IF function.

=SUM(IF(A2:A13=F2,C2:C13,0))

The final step is to press Ctrl + Shift + Enter to enter the formula (notice that Excel displays the { } by itself).

Basic Array Formulas - Array SUM IF

The formula in Cell F6 is:

{=SUM(IF(A2:A13=F2,C2:C13,0))}

You have just created an array formula.  That wasn’t so hard, was it.

 

Understanding the calculation

Let’s briefly understand why this formula works.

{=SUM(IF(A2:A13=F2,C2:C13,0))}

The section of the formula in blue above will calculate as TRUE or FALSE for each cell.  Does A2 = F2, then does A3 = F2, then does A4 = F2, and so on.  As F2 is a single cell this is used as a comparison for every cell in A2 – A13.

There are 12 cells in the range, so there are 12 TRUE/FALSE results.

{=SUM(IF({FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE},
C2:C13,0))}

The orange section in the formula above is will calculate as show below.

{=SUM(IF({FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE},
{1000;800;1200;1100;750;1050;1150;850;950;1250;900;1000},0))}

Each of these formula results is calculated as a separate IF function.

{=SUM(IF({FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE},
{1000;800;1200;1100;750;1050;1150;850;950;1250;900;1000},0))}

The first IF function has calculated as FALSE, therefore the result will be zero (it would have been 1000, if it were TRUE)

{=SUM(IF({FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE},
{1000;800;1200;1100;750;1050;1150;850;950;1250;900;1000},0))}

The second IF function has calculated as TRUE, therefore the result will be 800 (it would have been zero if it were FALSE)

This continues for each result of the IF function.  This calculates down to provide the following result.

{=SUM({0;800;0;0;750;0;0;850;0;0;900;0}))}

This is now a straight forward SUM function, which will add the numbers together.  The result is 3,300 (which is exactly the same as SUMIF).

 

Applying the array formula concept

This concept works for a variety of situations.  Now that you know the concept, you could create your own complex formulas, which would be impossible with a normal function.

You could create a SUMIFBUTFIXEDVALUEIFNOT (Sum if, but fixed value if not) function:

{=SUM(IF(A2:A13=F2,C2:C13,500))}

Or Maybe the AVERAGEROWNUMBER (average row number) function:

=AVERAGE(ROW(A2:A13))

You can also create the benefit of helper columns without needing to actually create a helper column.  In the example below Cells E14 & F14 are combined into a single search criteria then compared to A2 & B2 combined, then A3 & B3 combined, then A4 & B4 combined, and so on.

=MATCH(E14&F14,A2:A13&B2:B13,0)

 

Warnings

Array formulas may sound great, but there are some issues to be aware of:

  • They can be very slow to calculate, and they are often much slower than using a helper column
  • Most people won’t understand your work (though this may be a good thing)
  • If Ctrl + Shift + Enter is not used, it is likely to display either #VALUE or worse an incorrect result

 

Other examples of array formulas

Here are some posts from the blog which include other examples of array formulas.

 

What next?

Get Excel news, tips & tricks straight to you inbox.  Helping you to save time and achieve more with Excel.

You will also receive the Tab Hopper Add-in for FREE.

Tab Hopper Thumb







Save

Save

Save

Save

Save

Save

Save

Save

Leave a Reply

Your email address will not be published. Required fields are marked *