More About Array Formulas

In the previous post, we introduced array formulas.  These are formulas you enter into a range of cells using Shift+Ctrl+Enter.  That block, or array, works as a unit.  You can extend the array easily or, with some difficulty, reduce it.  We looked at some array formulas: TRANSPOSE, COLUMN, SUM, and LINEST.  If you didn’t read that post first, do it now.

Acknowledgement: Some of the examples below are based on ones in Microsoft’s online help.  To cater for users with older versions of Excel, we have removed items specific to Office 365 (366 in leap years).

Array Formulas using Multiplication and SUM

In this example,

Column E, Total Sales = Number Sold * Unit Price

We could simply create the formula in E4: = C4*D4 and then copy it down as far as row 11.  A SUM in E12 would complete the picture.

On a more sophisticated level, with Formulas > Create From Selection, we could name the range C4:C11  as Number_Sold, and range D4:D11 as Unit_Price .  Then insert the formula in E4: = Number_Sold * Unit_Price and copy it down. Although the two named ranges are multi-cell ranges, Excel is clever enough to calculate using only the value on the same row as each formula.

Getting even more complex, we can use an array formula in cells E4:E11.  It’s the same formula as above, but now we select the range E4:E11 first.  Type the formula = Number_Sold * Unit_Price, and enter it with Shift+Ctrl+Enter.  The only benefit this has, is that parts of the array formula cannot be changed on their own.  Hence, you have the assurance that all formulas in the array are identical.

The real strangeness comes when we use the SUM in E12 as an array formula. We can either enter it as

=SUM(C4:C11*D4:D11)

Or, if we named the ranges, as

=SUM(Number_Sold*Unit_Price)

Remember to use Shift+Ctrl+Enter to enter it.  If you use plain Enter, you will get a #VALUE! error (except in Excel 365).

That array formula SUM recalculates the individual products and then adds them up.  Usually, we would SUM the products calculated above.  With the array formula, we could even do without the Total Sales column!

Named Array Constants

We briefly mentioned range naming above.

You are probably familiar with named ranges.  They are single cells or ranges that you give a name, using the Formulas ribbon and the tools in the Defined Names group.

However, there is a hidden option to create an array of constants, give it a name, and then use it in array formulas.  I was excited when I first learned of this.

We can, for example, create a range name “Quarter1” consisting of the names {“January”,”February”,”March”} and then use that array in various ways.

Select the Formulas ribbon.  In the Defined Names group, click Define Name:

Note the braces typed around {“January”,”February”,”March”}, indicating that this is an array.

Click OK.

To use this, select three cells horizontally next to each other (this only works horizontally).  Type =Quarter1 and enter it as an array formula with Shift+Ctrl+Enter.  In the adjacent cells, you should see:

If you do need these vertically, you can use {=TRANSPOSE(range)}.  As usual with the TRANSPOSE formula, you first select three adjacent cells vertically, type the formula, and enter it with Shift+Ctrl+Enter.

Exercise: Try defining a named array constant “Rabbits” that refers to ={1,2,3,4,5,6}.  Why Rabbits?  Well, they multiply, right?  We’ll use that next.

Using Mathematical Operators in an Array Formula

You can use mathematical operators in your formulas: Multiply (*), Divide (/), Add (+), Subtract (-), etc.

In the example below, we’ve used add, subtract, divide, multiply, square, and square root.  In blue, we document the formulas using the FORMULATEXT function.

The array formulas are:

Add (D7:I7): {={1,2,3,4,5,6}+2}

Subtract (D10:I10): {={1,2,3,4,5,6} – 2}

Divide (D13:I13): {={1,2,3,4,5,6}/2}

Multiply*: {=Rabbits*2}

Square: {=Rabbits^2}

Square Root: {=SQRT(Rabbits)}

*In the previous section, on the Formulas ribbon, we defined a named array constant “Rabbits” that refers to ={1,2,3,4,5,6}.  See above.

For the month column headings, we have also used array formulas:

In D6:F6: {=Quarter1}

In G6:I6: {=Quarter2}

In D9:I9: {=TEXT(DATE(YEAR(TODAY()),{1,2,3,4,5,6},1),”mmmm”)}

In D12:I12: {=TEXT(DATE(YEAR(TODAY()),Rabbits,1),”mmmm”)}

And so on.

Working Around Errors with IF(ISERROR) in an Array Function

Suppose that we have a data range we want to SUM, but it contains errors we want to ignore.

The SUM function in Excel does not work when you sum a range that contains an error value, like #VALUE! or #N/A.  The SUM merely returns #VALUE! or #N/A.  This example shows you how to sum the values in a range named Data that contains errors:

Use arrays to deal with errors. For example, the following formula will sum the range named Data even if it includes errors, like #VALUE! or #NA:

=SUM(IF(ISERROR(Data), 0, Data)) —enter it as a single-cell array formula with Shift+Ctrl+Enter.

The formula creates a temporary array that contains the original values but treats error values as zero. To understand it, start from the innermost function and work outwards:

1. The ISERROR function in an array function searches each cell of the named range, Data, for errors.  It returns TRUE for any error, FALSE if there is no error.
2. The IF function takes three arguments.  It evaluates the condition (first parameter).  If it evaluates to TRUE, it returns the second parameter.   Otherwise (FALSE), it returns the third parameter.
3. In this case, the IF returns zero (0) for error values because ISERROR evaluates to TRUE.  It returns the remaining values from the range (Data) because ISERROR evaluates to FALSE for those that don’t contain error values.
4. The SUM function then calculates the total for the filtered array.  The zeros for the error values do not affect the total.
5. A SUM does not mind summing text, including the empty string (“”).  It treats text as zero.  We could thus also use: =SUM(IF(ISERROR(Data), “”, Data))

Similarly, we can use these:

Count the number of errors in a range: {=SUM(IF(ISERROR(Data),1,0))}

Average the values in the range: {=AVERAGE(IF(ISERROR(Data),”“, Data))}
Here we deliberately use the empty string (“”) and not zero, because strings do not affect the average, whereas zeros would.

Find the minimum value in the range: {=MIN(IF(ISERROR(Data),”“, Data))}
Again, empty string (“”) is ignored, whereas zero would give a false minimum if all the numbers are positive.

Using Conditions in an Array Function

You can use arrays to calculate based on specified conditions, excluding values that don’t match the conditions.

This array formula sums just the positive numbers in a range named Sales, which is cells E4:E19 in the example below:

=SUM(IF(Sales>0, Sales))

The IF function creates an array of positive and false values. The SUM function effectively ignores the false values because FALSE = 0, which literally adds nothing to the sum.

You can also sum values that meet more than one condition. For example, this array formula calculates values greater than 0 AND less than 2500:

=SUM((Sales>0) * (Sales<2500) * (Sales))

How does this work? (Sales>0) and (Sales<2500) both evaluate to TRUE for values we want to include. TRUE * TRUE = 1.  Multiply by a Sales figure, and you get the Sales figure.  By contrast, if one or both of the conditions is false, TRUE * FALSE = 0.  Multiply by a Sales figure, and the result is zero, which leaves the SUM unchanged.

Be aware that this formula will return an error if the range contains any non-numeric cells.

You can also create array formulas that use a type of OR condition. For example, you can sum values that are less than 0 OR greater than 2500:

=SUM(IF((Sales<0) + (Sales>2500), Sales))

You can’t use the AND and OR functions in array formulas directly because those functions return a single result, either TRUE or FALSE.  –Array functions require arrays of results.  You can work around the problem by using the logic in the previous formula. In other words, you perform math operations, such as addition or multiplication on values that meet the OR or AND condition.

This array example shows you how to remove zeros from a range when you need to average the values in that range. The formula uses a data range named Sales:

=AVERAGE(IF(Sales<>0, Sales))

The IF function creates an array of values that do not equal 0 and then passes those values to the AVERAGE function.

This example shows the above array formulas, each checked using another method:

Differences Between Datasets, Locating MAX and MIN

The example below demonstrates how to:

1. Count the number of differences between two ranges:
   Given range D4:D18 named Qtr_1 and H4:H18 named Qtr_2, use the array formula
   {=SUM(IF(Qtr_1=Qtr_2,0,1))}
   Thu compares Qtr_1 with Qtr_2 cell-by-cell.  Where they differ, the IF function returns a “1”, which is added into the SUM.
2. Find the row number of the maximum value in a range:
   {=MIN(IF(Qtr_1=MAX(Qtr_1), ROW(Qtr_1),””))}
   Here, the MAX function returns the highest value in Qtr_1).  IF in the array formula compares this cell-by-cell with the cells in the range Qtr_1.  It returns the ROW of that cell when it is found.
3. Find the row number of the minimum value in a range:  The array formula
   {=MAX(IF(Qtr_1=MIN(Qtr_1), ROW(Qtr_1),””))}
   is identical to the previous one, except that it uses MIN instead of MAX.
4. Find the address of the maximum value in a range: The array formula
   {=ADDRESS(MIN(IF(Qtr_1=MAX(Qtr_1),ROW(Qtr_1),””)),COLUMN(Qtr_1))}
   builds on formula 2, wrapping it in an ADDRESS(row, column) function.  We already have the row from formula 2, it remains to give it the COLUMN too.
5. Find the address of the minimum value in a range: The array formula
   {=ADDRESS(MAX(IF(Qtr_1=MIN(Qtr_1), ROW(Qtr_1),””)), COLUMN(Qtr_1))}
   is identical to the previous one, except that it uses MIN instead of MAX.

Conclusion

As we have seen, array formulas are powerful, yet their use can be anything but straightforward.  They can avoid intermediate calculations, at times at the expense of being more obscure.  Some applications cannot be done any other way, while in other cases there are more obvious ways of handling the problem, like SUMIF or SUMIFS.

Surprisingly many Excel functions can be used in array formulas (SUM, for example), while there are glaring omissions (OR and AND).

If you haven’t already done so, try the above examples, obtainable free of charge from us if you hate retyping.  For even more mind-blowing uses (many only applicable to Excel 365), look at Microsoft’s online help. That is where we got the inspiration for the examples above.