Chapter 16: Introducing Array Formulas

Worksheet Functions That Return an Array

Several of the Excel worksheet functions use arrays; you must enter a formula that uses one of these functions into multiple cells as an array formula. These functions are FORECAST, FREQUENCY, GROWTH, LINEST, LOGEST, MINVERSE, MMULT, and TREND. Consult the Excel Help system for more information.

If you want to generate an array of consecutive integers, a formula like the one shown previously is good — but not perfect. To see the problem, insert a new row above the range that contains the array formula. Excel adjusts the row references so that the array formula now reads

{=ROW(2:13)}

The formula that originally generated integers from 1 to 12 now generates integers from 2 to 13.

For a better solution, use this formula:

{=ROW(INDIRECT(“1:12”))}

This formula uses the INDIRECT function, which takes a text string as its argument. Excel does not adjust the references contained in the argument for the INDIRECT function. Therefore, this array formula always returns integers from 1 to 12.

Cross-Reference

Chapter 17 contains several examples that use the technique for generating consecutive integers. n

Using Single-Cell Array Formulas

The examples in the preceding section all used a multicell array formula — a single array formula that’s entered into a range of cells. The real power of using arrays becomes apparent when you use single-cell array formulas. This section contains examples of array formulas that occupy a single cell.

Counting characters in a range

Suppose that you have a range of cells that contains text entries (see Figure 16.14). If you need to get a count of the total number of characters in that range, the “traditional” method involves creating a formula like the one that follows and copying it down the column:

=LEN(A1)

Then you use a SUM formula to calculate the sum of the values returned by these intermediate formulas.

371

Part II: Working with Formulas and Functions

The following array formula does the job without using any intermediate formulas:

{=SUM(LEN(A1:A14))}

FIGURE 16.14

The goal is to count the number of characters in a range of text.

The array formula uses the LEN function to create a new array (in memory) that consists of the number of characters in each cell of the range. In this case, the new array is

{10,9,8,5,6,5,5,10,11,14,6,8,8,7}

The array formula is then reduced to

=SUM({10,9,8,5,6,5,5,10,11,14,6,8,8,7})

The formula returns the sum of the array elements, 112.

Summing the three smallest values in a range

If you have values in a range named Data, you can determine the smallest value by using the SMALL function:

=SMALL(Data,1)

You can determine the second smallest and third smallest values by using these formulas:

=SMALL(Data,2)

=SMALL(Data,3)

To add the three smallest values, you could use a formula like this:

=SUM(SMALL(Data,1), SMALL(Data,2), SMALL(Data,3)

372

Chapter 16: Introducing Array Formulas

This formula works fine, but using an array formula is more efficient. The following array formula returns the sum of the three smallest values in a range named Data:

{=SUM(SMALL(Data,{1,2,3}))}

The formula uses an array constant as the second argument for the SMALL function. This generates a new array, which consists of the three smallest values in the range. This array is then passed to the SUM function, which returns the sum of the values in the new array.

Figure 16.15 shows an example in which the range A1:A10 is named Data. The SMALL function is evaluated three times, each time with a different second argument. The first time, the SMALL function has a second argument of 1, and it returns –5. The second time, the second argument for the SMALL function is 2, and it returns 0 (the second smallest value in the range). The third time, the SMALL function has a second argument of 3 and returns the third smallest value of 2.

FIGURE 16.15

An array formula returns the sum of the three smallest values in A1:A10.

Therefore, the array that’s passed to the SUM function is

{-5,0,2)

The formula returns the sum of the array (–3).

Counting text cells in a range

Suppose that you need to count the number of text cells in a range. The COUNTIF function seems like it might be useful for this task — but it’s not. COUNTIF is useful only if you need to count values in a range that meet some criterion (for example, values greater than 12).

To count the number of text cells in a range, you need an array formula. The following array formula uses the IF function to examine each cell in a range. It then creates a new array (of the same size and dimensions as the original range) that consists of 1s and 0s, depending on whether the

373

Part II: Working with Formulas and Functions

cell contains text. This new array is then passed to the SUM function, which returns the sum of the items in the array. The result is a count of the number of text cells in the range:

{=SUM(IF(ISTEXT(A1:D5),1,0))}

Cross-Reference

This general array formula type (that is, an IF function nested in a SUM function) is very useful for counting. See Chapter 13 for additional examples of IF and SUM functions. n

Figure 16.16 shows an example of the preceding formula in cell C7. The array created by the IF function is

{0,1,1,1;1,0,0,0;1,0,0,0;1,0,0,0;1,0,0,0}

Notice that this array contains four rows of three elements (the same dimensions as the range).

Here is a slightly more efficient variation on this formula:

{=SUM(ISTEXT(A1:D5)*1)}

This formula eliminates the need for the IF function and takes advantage of the fact that

TRUE * 1 = 1

and

FALSE * 1 = 0

FIGURE 16.16

An array formula returns the number of text cells in the range.

Eliminating intermediate formulas

One key benefit of using an array formula is that you can often eliminate intermediate formulas in your worksheet, which makes your worksheet more compact and eliminates the need to display irrelevant calculations. Figure 16.17 shows a worksheet that contains pre-test and post-test scores for students. Column D contains formulas that calculate the changes between the pre-test and the

374

Chapter 16: Introducing Array Formulas

post-test scores. Cell D17 contains a formula, shown here, that calculates the average of the values in column D:

=AVERAGE(D2:D15)

With an array formula, you can eliminate column D. The following array formula calculates the average of the changes but does not require the formulas in column D:

{=AVERAGE(C2:C15-B2:B15)}

How does it work? The formula uses two arrays, the values of which are stored in two ranges (B2:B15 and C2:C15). The formula creates a new array that consists of the differences between each corresponding element in the other arrays. This new array is stored in Excel’s memory, not in a range. The AVERAGE function then uses this new array as its argument and returns the result.

The new array consists of the following elements:

{11,15,-6,1,19,2,0,7,15,1,8,23,21,-11}

The formula, therefore, is equivalent to

=AVERAGE({11,15,-6,1,19,2,0,7,15,1,8,23,21,-11})

Excel evaluates the function and displays the results, 7.57.

You can use additional array formulas to calculate other measures for the data in this example. For example, the following array formula returns the largest change (that is, the greatest improvement). This formula returns 23, which represents Linda’s test scores.

{=MAX(C2:C15-B2:B15)}

FIGURE 16.17

Without an array formula, calculating the average change requires intermediate formulas in column D.

375

Part II: Working with Formulas and Functions

The following array formula returns the smallest value in the Change column. This formula returns –11, which represents Nancy’s test scores.

{=MIN(C2:C15-B2:B15)}

Using an array in lieu of a range reference

If your formula uses a function that requires a range reference, you may be able to replace that range reference with an array constant. This is useful in situations in which the values in the referenced range do not change.

Note

A notable exception to using an array constant in place of a range reference in a function is with the database functions that use a reference to a criteria range (for example, DSUM). Unfortunately, using an array constant instead of a reference to a criteria range does not work. n

Cross-Reference

For information about lookup formulas, see Chapter 14. n

Figure 16.18 shows a worksheet that uses a lookup table to display a word that corresponds to an integer. For example, looking up a value of 9 returns Nine from the lookup table in D1:E10. The formula in cell C1 is

=VLOOKUP(B1,D1:E10,2,FALSE)

FIGURE 16.18

You can replace the lookup table in D1:E10 with an array constant.

376

Chapter 16: Introducing Array Formulas

You can use a two-dimensional array in place of the lookup range. The following formula returns the same result as the previous formula, but it does not require the lookup range in D1:E1:

=VLOOKUP(B1,{1,”One”;2,”Two”;3,”Three”;4,”Four”;5,”Five”;

6,”Six”;7,”Seven”;8,”Eight”;9,”Nine”;10,”Ten”},2,FALSE)

This chapter introduced arrays. Chapter 17 explores the topic further and provides some additional examples.

377

CHAPTER

Performing Magic with Array Formulas

Working with Single-Cell

Array Formulas

As I describe in the preceding chapter, you enter single-cell array formulas into a single cell (not into a range of cells). These array formulas work with arrays contained in a range or that exist in memory. This section provides some additional examples of such array formulas.

On the CD

The examples in this section are available on the companion CD-ROM. The file is named single-cell array formulas.xlsx.

Summing a range that contains errors

You may have discovered that the SUM function doesn’t work if you attempt to sum a range that contains one or more error values (such as #DIV/0! or

379

Part II: Working with Formulas and Functions

#N/A). Figure 17.1 shows an example. The formula in cell C11 returns an error value because the range that it sums (C4:C10) contains errors.

The following array formula, in cell C13, overcomes this problem and returns the sum of the values, even if the range contains error values:

{=SUM(IFERROR(C4:C10,””))}

This formula works by creating a new array that contains the original values but without the errors. The IF function effectively filters out error values by replacing them with an empty string. The SUM function then works on this “filtered” array. This technique also works with other functions, such as AVERAGE, MIN, and MAX.

Note

The IFERROR function was introduced in Excel 2007. Following is a modified version of the formula that’s compatible with older versions of Excel:

{=SUM(IF(ISERROR(C4:C10),””,C4:C10))}

New Feature

The new AGGREGATE function, which works only in Excel 2010, provides another way to sum a range that contains one or more error values. Here’s an example:

=AGGREGATE(9,2,C4:C10)

The first argument, 9, is the code for SUM. The second argument, 2, is the code for “ignore error values.” n

FIGURE 17.1

An array formula can sum a range of values, even if the range contains errors.

Counting the number of error values in a range

The following array formula is similar to the previous example, but it returns a count of the number of error values in a range named Data:

{=SUM(IF(ISERROR(Data),1,0))}

380

Chapter 17: Performing Magic with Array Formulas

This formula creates an array that consists of 1s (if the corresponding cell contains an error) and 0s (if the corresponding cell does not contain an error value).

You can simplify the formula a bit by removing the third argument for the IF function. If this argument isn’t specified, the IF function returns FALSE if the condition is not satisfied (that is, the cell does not contain an error value). In this context, Excel treats FALSE as a 0 value. The array formula shown here performs exactly like the previous formula, but it doesn’t use the third argument for the IF function:

{=SUM(IF(ISERROR(Data),1))}

Actually, you can simplify the formula even more:

{=SUM(ISERROR(Data)*1)}

This version of the formula relies on the fact that

TRUE * 1 = 1

and

FALSE * 1 = 0

Summing the n largest values in a range

The following array formula returns the sum of the 10 largest values in a range named Data:

{=SUM(LARGE(Data,ROW(INDIRECT(“1:10”))))}

The LARGE function is evaluated 10 times, each time with a different second argument (1, 2, 3, and so on up to 10). The results of these calculations are stored in a new array, and that array is used as the argument for the SUM function.

To sum a different number of values, replace the 10 in the argument for the INDIRECT function with another value.

If the number of cells to sum is contained in cell C17, use the following array formula, which uses the concatenation operator (&) to create the range address for the INDIRECT function:

{=SUM(LARGE(Data,ROW(INDIRECT(“1:”&C17))))}

To sum the n smallest values in a range, use the SMALL function instead of the LARGE function.

Computing an average that excludes zeros

Figure 17.2 shows a simple worksheet that calculates average sales. The formula in cell B13 is

=AVERAGE(B4:B11)

381

Part II: Working with Formulas and Functions

FIGURE 17.2

The calculated average includes cells that contain a 0.

Two of the sales staff had the week off, however, so including their 0 sales in the calculated average doesn’t accurately describe the average sales per representative.

Note

The AVERAGE function ignores blank cells, but it does not ignore cells that contain 0.

The following array formula returns the average of the range but excludes the cells containing 0:

{=AVERAGE(IF(B5:B12<>0,B5:B12))}

This formula creates a new array that consists only of the nonzero values in the range. The AVERAGE function then uses this new array as its argument.

You also can get the same result with a regular (non-array) formula:

=SUM(B5:B12)/COUNTIF(B5:B12,”<>0”)

This formula uses the COUNTIF function to count the number of nonzero values in the range. This value is divided into the sum of the values.

Note

The only reason to use an array formula to calculate an average that excludes zero values is for compatibility with versions prior to Excel 2007. A simple approach is to use the AVERAGEIF function in a non-array formula:

=AVERAGEIF(B5:B12,”<>0”,B5:B12)

382

Chapter 17: Performing Magic with Array Formulas

Determining whether a particular value appears in a range

To determine whether a particular value appears in a range of cells, you can choose Home Editing Find & Select Find and do a search of the worksheet. But you also can make this determination by using an array formula.

Figure 17.3 shows a worksheet with a list of names in A5:E24 (named NameList). An array formula in cell D3 checks the name entered into cell C3 (named TheName). If the name exists in the list of names, the formula displays the text Found. Otherwise, it displays Not Found.

FIGURE 17.3

Using an array formula to determine whether a range contains a particular value.

The array formula in cell D3 is

{=IF(OR(TheName=NameList),”Found”,”Not Found”)}

This formula compares TheName to each cell in the NameList range. It builds a new array that consists of logical TRUE or FALSE values. The OR function returns TRUE if any one of the values in the new array is TRUE. The IF function uses this result to determine which message to display.

383

Part II: Working with Formulas and Functions

A simpler form of this formula follows. This formula displays TRUE if the name is found and returns FALSE otherwise.

{=OR(TheName=NameList)}

Yet another approach uses the COUNTIF function in a non-array formula:

=IF(COUNTIF(NameList,TheName)>0,”Found”,”Not Found”)

Counting the number of differences in two ranges

The following array formula compares the corresponding values in two ranges (named MyData and YourData) and returns the number of differences in the two ranges. If the contents of the two ranges are identical, the formula returns 0.

{=SUM(IF(MyData=YourData,0,1))}

Note

The two ranges must be the same size and of the same dimensions. n

This formula works by creating a new array of the same size as the ranges being compared. The IF function fills this new array with 0s and 1s: 1 if a difference is found, and 0 if the corresponding cells are the same. The SUM function then returns the sum of the values in the array.

The following array formula, which is simpler, is another way of calculating the same result:

{=SUM(1*(MyData<>YourData))}

This version of the formula relies on the fact that

TRUE * 1 = 1

and

FALSE * 1 = 0

Returning the location of the maximum value in a range

The following array formula returns the row number of the maximum value in a single-column range named Data:

{=MIN(IF(Data=MAX(Data),ROW(Data), “”))}

The IF function creates a new array that corresponds to the Data range. If the corresponding cell contains the maximum value in Data, the array contains the row number; otherwise, it contains an

384

Chapter 17: Performing Magic with Array Formulas

empty string. The MIN function uses this new array as its second argument, and it returns the smallest value, which corresponds to the row number of the maximum value in Data.

I use the MIN function to handle ties. If the Data range contains more than one cell that has the maximum value, the row of the first occurrence of the maximum cell is returned. If you change MIN to MAX, then the formula returns the last occurrence of the maximum cell.

The following array formula is similar to the previous one, but it returns the actual cell address of the maximum value in the Data range. It uses the ADDRESS function, which takes two arguments: a row number and a column number.

{=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data), “”)),COLUMN(Data))}

The previous formulas work only with a single-column range. The following variation works with any sized range and returns the address of the smallest value in the range named Data:

{=ADDRESS(MIN(IF(Data=MAX(data),ROW(Data), “”)), MIN(IF(Data=MAX(Data),COLUMN(Data), “”)))}

Finding the row of a value’s nth occurrence in a range

The following array formula returns the row number within a single-column range named Data that contains the nth occurrence of the value in a cell named Value:

{=SMALL(IF(Data=Value,ROW(Data), “”),n)}

The IF function creates a new array that consists of the row number of values from the Data range that are equal to Value. Values from the Data range that aren’t equal to Value are replaced with an empty string. The SMALL function works on this new array and returns the nth smallest row number.

The formula returns #NUM! if the Value is not found or if n exceeds the number of the values in the range.

Returning the longest text in a range

The following array formula displays the text string in a range (named Data) that has the most characters. If multiple cells contain the longest text string, the first cell is returned.

{=INDEX(Data,MATCH(MAX(LEN(Data)),LEN(Data),FALSE),1)}

This formula works with two arrays, both of which contain the length of each item in the Data range. The MAX function determines the largest value, which corresponds to the longest text item. The MATCH function calculates the offset of the cell that contains the maximum length. The INDEX function returns the contents of the cell containing the most characters. This function works only if the Data range consists of a single column.

385

Part II: Working with Formulas and Functions

Determining whether a range contains valid values

You may have a list of items that you need to check against another list. For example, you may import a list of part numbers into a range named MyList, and you want to ensure that all the part numbers are valid. You can do so by comparing the items in the imported list to the items in a master list of part numbers (named Master).

The following array formula returns TRUE if every item in the range named MyList is found in the range named Master. Both ranges must consist of a single column, but they don’t need to contain the same number of rows.

{=ISNA(MATCH(TRUE,ISNA(MATCH(MyList,Master,0)),0))}

The array formula that follows returns the number of invalid items. In other words, it returns the number of items in MyList that do not appear in Master.

{=SUM(1*ISNA(MATCH(MyList,Master,0)))}

To return the first invalid item in MyList, use the following array formula:

{=INDEX(MyList,MATCH(TRUE,ISNA(MATCH(MyList,Master,0)),0))}

Summing the digits of an integer

I can’t think of any practical application for the example in this section, but it’s a good demonstration of the power of an array formula. The following array formula calculates the sum of the digits in a positive integer, which is stored in cell A1. For example, if cell A1 contains the value 409, the formula returns 13 (the sum of 4, 0, and 9).

{=SUM(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)*1)}

To understand how this formula works, start with the ROW function, as shown here:

{=ROW(INDIRECT(“1:”&LEN(A1)))}

This function returns an array of consecutive integers beginning with 1 and ending with the number of digits in the value in cell A1. For example, if cell A1 contains the value 409, the LEN function returns 3, and the array generated by the ROW functions is

{1,2,3}

Cross-Reference

For more information about using the INDIRECT function to return this array, see Chapter 16. n

This array is then used as the second argument for the MID function. The MID part of the formula, simplified a bit and expressed as values, is the following:

{=MID(409,{1,2,3},1)*1}

386

Chapter 17: Performing Magic with Array Formulas

This function generates an array with three elements:

{4,0,9}

By simplifying again and adding the SUM function, the formula looks like this:

{=SUM({4,0,9})}

This formula produces the result of 13.

Note

The values in the array created by the MID function are multiplied by 1 because the MID function returns a string. Multiplying by 1 forces a numeric value result. Alternatively, you can use the VALUE function to force a numeric string to become a numeric value. n

Notice that the formula doesn’t work with a negative value because the negative sign is not a numeric value. Also, the formula fails if the cell contains non-numeric values (such as 123A6). The following formula solves this problem by checking for errors in the array and replacing them with zero.

{=SUM(IFERROR(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)*1,0))}

Note

This formula uses the IFERROR function, which was introduced in Excel 2007.

Figure 17.4 shows a worksheet that uses both versions of this formula.

FIGURE 17.4

Two versions of an array formula calculate the sum of the digits in an integer.

387

Part II: Working with Formulas and Functions

Summing rounded values

Figure 17.5 shows a simple worksheet that demonstrates a common spreadsheet problem: rounding errors. As you can see, the grand total in cell E7 appears to display an incorrect amount. (That is, it’s off by a penny.) The values in column E use a number format that displays two decimal places. The actual values, however, consist of additional decimal places that do not display due to rounding (as a result of the number format). The net effect of these rounding errors is a seemingly incorrect total. The total, which is actually $168.320997, displays as $168.32.

FIGURE 17.5

Using an array formula to correct rounding errors.

The following array formula creates a new array that consists of values in column E, rounded to two decimal places:

{=SUM(ROUND(E4:E6,2))}

This formula returns $168.31.

You also can eliminate these types of rounding errors by using the ROUND function in the formula that calculates each row total in column E (which does not require an array formula).

Summing every nth value in a range

Suppose that you have a range of values and you want to compute the sum of every third value in the list — the first, the fourth, the seventh, and so on. One solution is to hard-code the cell addresses in a formula. A better solution, though, is to use an array formula.

Note

In Figure 17.6, the values are stored in a range named Data, and the value of n is in cell D2 (named n). n

388

Chapter 17: Performing Magic with Array Formulas

FIGURE 17.6

An array formula returns the sum of every nth value in the range.

The following array formula returns the sum of every nth value in the range:

{=SUM(IF(MOD(ROW(INDIRECT(“1:”&COUNT(Data)))-1,n)=0,Data,””))}

This formula returns 70, which is the sum of every third value in the range.

This formula generates an array of consecutive integers, and the MOD function uses this array as its first argument. The second argument for the MOD function is the value of n. The MOD function creates another array that consists of the remainders when each row number is divided by n. When the array item is 0 (that is, the row is evenly divisible by n), the corresponding item in the Data range will be included in the sum.

You find that this formula fails when n is 0: that is, when it sums no items. The modified array formula that follows uses an IF function to handle this case:

{=IF(n=0,0,SUM(IF(MOD(ROW(INDIRECT(“1:”&COUNT(data)))- 1,n)=0,data,””)))}

This formula works only when the Data range consists of a single column of values. It does not work for a multicolumn range or for a single row of values.

389

Part II: Working with Formulas and Functions

Using the Excel Formula Evaluator

If you would like to better understand how some of these complex array formulas work, consider using a handy tool: the Formula Evaluator. Select the cell that contains the formula and then choose Formulas Formula Auditing Evaluate Formula. The Evaluate Formula dialog box shown in the figure here.

Click the Evaluate button repeatedly to see the intermediate results as the formula is being calculated. It’s like watching a formula calculate in slow motion.

To make the formula work with a horizontal range, you need to transpose the array of integers generated by the ROW function. The TRANPOSE function is just the ticket. The modified array formula that follows works only with a horizontal Data range:

{=IF(n=0,0,SUM(IF(MOD(TRANSPOSE(ROW(INDIRECT (“1:”&COUNT(Data))))-1,n)=0,Data,””)))}

Removing non-numeric characters from a string

The following array formula extracts a number from a string that contains text. For example, consider the string ABC145Z. The formula returns the numeric part, 145.

{=MID(A1,MATCH(0,(ISERROR(MID(A1,ROW(INDIRECT (“1:”&LEN(A1))),1)*1)*1),0),LEN(A1)-SUM((ISERROR (MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)*1)*1)))}

This formula works only with a single embedded number. For example, it fails with a string like X45Z99 because the string contains two embedded numbers.

390

Chapter 17: Performing Magic with Array Formulas

Determining the closest value in a range

The formula in this section performs an operation that none of Excel’s lookup functions can do. The array formula that follows returns the value in a range named Data that is closest to another value (named Target):

{=INDEX(Data,MATCH(SMALL(ABS(Target-Data),1),ABS(Target-Data),0))}

If two values in the Data range are equidistant from the Target value, the formula returns the first one in the list. Figure 17.7 shows an example of this formula. In this case, the Target value is 45. The array formula in cell D4 returns 48 — the value closest to 45.

FIGURE 17.7

An array formula returns the closest match.

Returning the last value in a column

Suppose that you have a worksheet that you update frequently by adding new data to columns. You may need a way to reference the last value in column A (the value most recently entered). If column A contains no empty cells, the solution is relatively simple and doesn’t require an array formula:

=OFFSET(A1,COUNTA(A:A)-1,0)

391

Part II: Working with Formulas and Functions

This formula uses the COUNTA function to count the number of nonempty cells in column A. This value (minus 1) is used as the second argument for the OFFSET function. For example, if the last value is in row 100, COUNTA returns 100. The OFFSET function returns the value in the cell 99 rows down from cell A1 in the same column.

If column A has one or more empty cells interspersed, which is frequently the case, the preceding formula won’t work because the COUNTA function doesn’t count the empty cells.

The following array formula returns the contents of the last nonempty cell in the first 500 rows of column A:

{=INDEX(A1:A500,MAX(ROW(A1:A500)*(A1:A500<>””)))}

You can, of course, modify the formula to work with a column other than column A. To use a different column, change the four column references from A to whatever column you need. If the last nonempty cell occurs in a row beyond row 500, you need to change the two instances of 500 to a larger number. The fewer rows referenced in the formula, the faster the calculation speed.

Caution

You can’t use this formula, as written, in the same column with which it’s working. Attempting to do so generates a circular reference. You can, however, modify it. For example, to use the function in cell A1, change the references so that they begin with row 2 instead of row 1. n

Returning the last value in a row

The following array formula is similar to the previous formula, but it returns the last nonempty cell in a row (in this case, row 1):

{=INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>””)))}

To use this formula for a different row, change the 1:1 reference to correspond to the row.

Ranking data with an array formula

Often, computing the rank orders for the values in a range of data is helpful. If you have a worksheet containing the annual sales figures for 20 salespeople, for example, you may want to know how each person ranks, from highest to lowest.

If you’ve used the Excel RANK function, you may have noticed that the ranks produced by this function don’t handle ties the way that you may like. For example, if two values are tied for third place, RANK gives both of them a rank of 3. You may prefer a commonly used approach that assigns each an average (or midpoint) of the ranks — in other words, a rank of 3.5 for both values tied for third place.

392

Chapter 17: Performing Magic with Array Formulas

Figure 17.8 shows a worksheet that uses two methods to rank a column of values (named Sales). The first method (column C) uses the RANK function. Column D uses array formulas to compute the ranks.

The following is the array formula in cell D4:

{=SUM(1*(B4<=Sales))-(SUM(1*(B4=Sales))-1)/2}

This formula is copied to the cells below it.

Note

Each ranking is computed with a separate array formula, not with an array formula entered into multiple cells. n

Each array function works by computing the number of higher values and subtracting one half of the number of equal values minus 1.

New Feature

Excel 2010 includes a new worksheet function, RANK.AVG, that eliminates the need for an array formula. The formula that follows returns the same rankings as shown in Column D in Figure 17.8. This formula is in cell D4, and copied to the cells below.

=RANK.AVG(B4,Sales)

FIGURE 17.8

Ranking data with the Excel’s RANK function and with array formulas.

393