Excel Yourself: Array for a good solution

Excel Yourself

Used carefully, this little-known feature of Excel can do some heavy lifting.

Question
Is it possible to create a formula to calculate the maximum value in a range based on criteria in another range?
 
Answer
If you have Excel 2010 or later version, you can use the AGGREGATE function to perform this conditional calculation.

Conditional functions

Excel’s oldest conditional function is the SUMIF, which can sum a range based on a single condition.

The SUMIFS function was added in Excel 2007 and allows you to perform multiple criteria sum calculations.

There are several other conditional functions – COUNTIF, COUNTIFS, AVERAGEIF and AVERAGIFS. The last three were also added in Excel 2007.

There is no conditional maximum calculation function. If there were, it would be called MAXIF. The formula for a MAXIF solution for previous Excel versions is an array formula and is explained briefly at the end of this article. In earlier versions of Excel, conditional functions were all achieved by using array formulas.

NOTE: an array formula is different to an array, which is explained as part of the AGGREGATE solution.

Array formulas use arrays, but some standard functions can also work with arrays.

I covered the AGGREGATE function in the May issue of INTHEBLACK and highlighted its unique ability to ignore error cells. That same ability can be used to create a simulated MAXIF function.

This solution is quite flexible and can be converted into a MINIF (conditional minimum calculation) by changing one number.

Figure 1

Figure 1







Syntax

AGGREGATE(Function_Number,Options,Array,Value)

Function_Number – chooses the function to use in the calculation. In our example we will use 14 for the LARGE function.
The LARGE function can work with an array of values. Not all functions can work with arrays.

Options – chooses how to handle certain cells. We will use 6, which instructs the function to ignore error cells.

Array – is the range to base the calculation on. We will use a formula that creates an array based on a single condition.

Value – is optional, depending on the Function_Number chosen.

We will use 1 to work with the LARGE function as we want to find the largest value (2 would produce the second-largest value). Figure 1 has the example data.

We need to calculate the highest score from column B based on the region in column A. I’ve used a small table for demonstration purposes. The technique will work on large data sets.

The formula for cell E2 is: =AGGREGATE (14,6,($B$2:$B$13)/($A$2:$A$13=D2),1)

As mentioned, the 14 specifies the LARGE function.

The 6 instructs the AGGREGATE function to ignore all errors. The 1 at the end of the formula means we want the largest value.

By selecting part of a formula in the Formula Bar and pressing the F9 function key, Excel will display the results for that part of the formula.

Selecting ($B$2:$B$13)/($A$2:$A$13=D2) from within the formula and pressing F9 we see the following:

{0.75;#DIV/0!;#DIV/0!;#DIV/0!;0.7;#DIV/0!;#DIV/0!;#DIV/0!; 0.6;#DIV/0!;#DIV/0!;#DIV/0!}

See the final line in Figure 2.

Figure 2

Figure 2

The braces {} identify this as an array, a special type of Excel range.

Figure 2 shows the results of pressing F9 on three parts of the formula.

The resulting arrays each have 12 items. The North items are the first, fifth and ninth items in all three of the arrays shown in Figure 2.

The first array listed in Figure 2 is divided by the second array to create the third array. In the second array in Figure 2, each of the 12 cells in the range A2:A13 is compared to the value in cell D2, North.

This returns 12 separate results of TRUE or FALSE. TRUE means North is in column A. In Excel TRUE =1 and FALSE=0.

The 12 separate values in the range B2:B13 (first array line in Figure 2) are divided by the corresponding TRUE/FALSE result from the A2:A13 comparison (second array line in Figure 2).

Dividing by TRUE (1) leaves the value unchanged in the third array.

Dividing by FALSE (0) causes a #DIV/0! error in the third array.

The AGGREGATE function uses the third array and ignores the errors, leaving the LARGE function to use the three remaining values to determine the highest value for the North region.

The formula in cell E2 can be copied to E3, E4 and E5 to perform the calculation for the South, East and West regions.

We can even copy the formula to cell E8 and change the 14 to a 15.

This uses the SMALL function to replicate a conditional minimum or a MINIF.
The formula for E8 is:

=AGGREGATE (15,6,($B$2:$B$13)/($A$2:$A$13=d8),1)

This formula can be copied to the cells below.

Arrays are an advanced and powerful concept in Excel.

It may take a few reads to understand how the technique works and how it is being applied in a single cell.

Figure 3

Figure 3








All versions solution

To achieve the same results in all versions of Excel, you must use an array formula. Array formulas must be entered by holding down the Ctrl + Shift keys and pressing the Enter key.

Excel adds braces { } around the whole formula, which defines it as an array formula. Array formulas work differently to standard Excel formulas.

The array formula for cell E2 is: {=MAX(IF($A$2:$A$13=D2,$B$2:$B$13,""))}

This formula can also be copied down. 

Array formula warnings

If you edit an array formula, you must use the Ctrl + Shift + Enter combination to accept the changes, otherwise the formula converts to a standard Excel formula and may display an error message or an incorrect result.

You can’t add the braces {} yourself. I advise people to use array formulas as a last resort because of this special entry requirement.

Most Excel users are unaware of array formulas.

This warning does not apply to the AGGREGATE solution as it is a normal Excel formula.

Excel tip

Excel’s grouping feature allows you to hide and unhide rows or columns easily.

It also displays icons to show that rows or columns have been hidden. To apply grouping, select a range of rows or columns and press Shift + Alt + right arrow.

To remove grouping, press Shift + Alt + left arrow. The icons added are a minus sign to hide and a plus sign to unhide.

Clicking the small numbers to the left of the row numbers and above the column letters also allows you to hide and unhide rows and columns.

Neale Blackwood CPA is CPA Australia’s resident Excel expert. He runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to a4@iinet.net.au. www.a4accounting.com.au.

This article is from the December 2013 issue of INTHEBLACK magazine.


How-to articles, sample spreadsheets and more

Excel yourself

Combine annual and monthly data in Excel with these simple steps


Making data relationships work

Easy steps to make data Relationships work in Excel


Excel Yourself

Easy tips for matching salary to job class in Excel