Part II: Working with Formulas and Functions

Working with Multicell Array Formulas

The preceding chapter introduced array formulas entered into multicell ranges. In this section, I present a few more array multicell formulas. Most of these formulas return some or all the values in a range, but rearranged in some way.

On the CD

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

Returning only positive values from a range

The following array formula works with a single-column vertical range (named Data). The array formula is entered into a range that’s the same size as Data and returns only the positive values in the Data range. (Zeroes and negative numbers are ignored.)

{=INDEX(Data,SMALL(IF(Data>0,ROW(INDIRECT(“1:”&ROWS(Data)))),

ROW(INDIRECT(“1:”&ROWS(Data)))))}

As you can see in Figure 17.9, this formula works, but not perfectly. The Data range is A4:A22, and the array formula is entered into C4:C23. However, the array formula displays #NUM! error values for cells that don’t contain a value.

This modified array formula, entered into range E4:E23, uses the IFERROR function to avoid the error value display:

{=IFERROR(INDEX(Data,SMALL(IF(Data>0,ROW

(INDIRECT(“1:”&ROWS(Data)))),ROW

(INDIRECT(“1:”&ROWS(Data))))),””)}

The IFERROR function was introduced in Excel 2007. For compatibility with older versions, use this formula:

{=IF(ISERR(SMALL(IF(Data>0,ROW(INDIRECT(“1:”&ROWS(Data)))),ROW

(INDIRECT(“1:”&ROWS(Data))))),””,INDEX(Data,SMALL(IF

(Data>0,ROW(INDIRECT(“1:”&ROWS(Data)))),ROW(INDIRECT

(“1:”&ROWS(Data))))))}

Returning nonblank cells from a range

The following formula is a variation on the formula in the preceding section. This array formula works with a single-column vertical range named Data. The array formula is entered into a range of the same size as Data and returns only the nonblank cell in the Data range.

394

Chapter 17: Performing Magic with Array Formulas

{=IFERROR(INDEX(Data,SMALL(IF(Data<>””,ROW(INDIRECT

(“1:”&ROWS(Data)))),ROW(INDIRECT(“1:”&ROWS(Data))))),””)}

For compatibility with versions prior to Excel 2007, use this formula:

{=IF(ISERR(SMALL(IF(Data<>””,ROW(INDIRECT(“1:”&ROWS(Data)))),

ROW(INDIRECT(“1:”&ROWS(Data))))),””,INDEX(Data,SMALL(IF

(Data<>””,ROW(INDIRECT(“1:”&ROWS(Data)))),ROW(INDIRECT

(“1:”&ROWS(Data))))))}

FIGURE 17.9

Using an array formula to return only the positive values in a range.

Reversing the order of cells in a range

In Figure 17.10, cells C4:C13 contain a multicell array formula that reverses the order of the values in the range A4:A13 (which is named Data).

The array formula is

{=IF(INDEX(Data,ROWS(Data)-ROW(INDIRECT (“1:”&ROWS(Data)))+1)=””,””,INDEX(Data,ROWS(Data)-ROW(INDIRECT(“1 :”&ROWS(Data)))+1))}

395

Part II: Working with Formulas and Functions

FIGURE 17.10

A multicell array formula displays the entries in A4:A13 in reverse order.

Sorting a range of values dynamically

Figure 17.11 shows a data entry range in column A (named Data). As the user enters values into that range, the values are displayed sorted from largest to smallest in column C. The array formula in column C is rather simple:

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

If you prefer to avoid the #NUM! error display, the formula gets a bit more complex:

{=IF(ISERR(LARGE(Data,ROW(INDIRECT(“1:”&ROWS(Data))))),

“”,LARGE(Data,ROW(INDIRECT(“1:”&ROWS(Data)))))}

Note that this formula works only with values. The companion CD-ROM has a similar array formula example that works only with text.

Returning a list of unique items in a range

If you have a single-column range named Data, the following array formula returns a list of the unique items in the range (the list with no duplicated items):

{=INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT

(“1:”&ROWS(Data))),MATCH(Data,Data,0),””),ROW(INDIRECT

(“1:”&ROWS(Data)))))}

This formula doesn’t work if the Data range contains any blank cells. The unfilled cells of the array formula display #NUM!.

396

Chapter 17: Performing Magic with Array Formulas

The following modified version eliminates the #NUM! display by using the Excel 2007 IFERROR function.

{=IFERROR(INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT

(“1:”&ROWS(data))),MATCH(Data,Data,0),””),ROW(INDIRECT

(“1:”&ROWS(Data))))),””)}

FIGURE 17.11

A multicell array formula displays the values in column A, sorted.

Figure 17.12 shows an example. Range A4:A22 s named Data, and the array formula is entered into range C4:C22. Range E4:E22 contains the array formula that uses the IFERROR function.

397

Part II: Working with Formulas and Functions

FIGURE 17.12

Using an array formula to return unique items from a list.

Displaying a calendar in a range

Figure 17.13 shows the results of one of my favorite multicell array formulas, a “live” calendar displayed in a range of cells. If you change the date at the top, the calendar recalculates to display the dates for the month and year.

On the CD

This workbook is available on the companion CD-ROM. The file is named array formula calendar.xlsx. In addition, you’ll find a workbook (yearly calendar.xlsx) that uses this technique to display a calendar for a complete year. n

After you create this calendar, you can easily copy it to other worksheets or workbooks.

To create this calendar in the range B2:H9, follow these steps:

1.Select B2:H2 and merge the cells by choosing Home Alignment Merge &

Center.

2.Enter a date into the merged range. The day of the month isn’t important.

3.Enter the abbreviated day names in the range B3:H3.

398

Chapter 17: Performing Magic with Array Formulas

4.Select B4:H9 and enter this array formula. Remember: To enter an array formula, press Ctrl+Shift+Enter (not just Enter).

{=IF(MONTH(DATE(YEAR(B2),MONTH(B2),1))<>MONTH(DATE(YEAR(B2), MONTH(B2),1)-(WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1)+ {0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),””, DATE(YEAR(B2),MONTH(B2),1)-(WEEKDAY(DATE(YEAR(B2),MONT H(B2),1))-1)+

{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)}

5.Format the range B4:H9 to use this custom number format: d. This step formats the dates to show only the day. Use the Custom category in the Number tab of the Format Cells dialog box to specify this custom number format.

6.Adjust the column widths and format the cells as you like.

7.Change the month and year in cell B2. The calendar updates automatically.

After creating this calendar, you can copy the range to any other worksheet or workbook.

FIGURE 17.13

Displaying a calendar by using a single array formula.

The array formula actually returns date values, but the cells are formatted to display only the day portion of the date. Also, notice that the array formula uses array constants.

Cross-Reference

See Chapter 16 for more information about array constants. n

399

Part III

Creating Charts

and Graphics