Working with Multicell Array Formulas | Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

The previous 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 of or all the values in a range, but rearranged in some way.

On the CD

Each of the examples in this section is demonstrated in multi-cell array formulas.xlsx, a file on the companion CD-ROM.

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 it returns only the positive values in the Data range (0s 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 column C in Figure 15-10, this formula works albeit not perfectly. The Data range is A5:A24, and the array formula is entered into C5:C24. However, the array formula displays #NUM! error values for cells that don't contain a value.

image from book
Figure 15-10: Using an array formula to return only the positive values in a range.

This modified array formula, entered into range E5:E24, 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 is new to 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 previous 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 it returns only the nonblank cell in the Data range.

 {=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))))))}

Reversing the Order of the Cells in a Range

The following array formula works with a single-column vertical range (named Data). The array formula, which is entered into a range of the same size as Data, returns the values in Data, but in reverse order.

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

Figure 15-11 shows this formula in action. The range A5:A14 is named Data, and the array formula is entered into the range C5:C14.

image from book
Figure 15-11: A multicell array formula reverses the order of the values in the range.

Sorting a Range of Values Dynamically

Suppose your worksheet contains a single-column vertical range named Data. The following array formula, entered into a range with the same number of rows as Data, returns the values in Data, sorted from highest to lowest. This formula works only with numeric values, not with text.

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

Figure 15-12 shows an example. The input range is in column A; the multicell array formula is in column C.

image from book
Figure 15-12: A multicell array formula in column C sorts the values entered into column A.

This formula can be useful if you need to have your data entry sorted immediately. Start by defining the range name Data as your data entry range. Then enter the array formula into another range with the same number of rows as Data.

The array formula returns #NUM! for cells that don't have a value. The modified version, which follows, uses Excel 2007's IFERROR function to eliminate the error display. This formula is used in column E in Figure 15-12:

 {=IFERROR(LARGE(Data,ROW(INDIRECT("1:"&ROWS(Data)))),"")}

For compatibility with earlier versions, use this more complex formula:

 {=IF(ISERR(LARGE(Data,ROW(INDIRECT("1:"&ROWS(Data))))),"", LARGE(Data,ROW(INDIRECT("1:"&ROWS(Data)))))}

To sort the values in Data from lowest to highest, use the SMALL function in place of the LARGE function.

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 (non-duplicated) items in the range:

 {=INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT("1:"&ROWS(Data))), MATCH(Data,Data,0),""),ROW(INDIRECT("1:"&ROWS(Data)))))}

This formula does not work if the Data range contains any blank cells. Figure 15-13 shows an example. Range A5:A23 is named Data, and the array formula is entered into range C5:C23. Note that the unfilled cells of the array formula display #NUM!.

image from book
Figure 15-13: Using an array formula to return unique items from a list.

The modified version, which follows, 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))))),"")}

For compatibility with earlier versions, use this formula:

 =INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT ("1:"&ROWS(Data))),MATCH(Data,Data,0),""),ROW(INDIRECT ("1:"&ROWS(Data)))))

Displaying a Calendar in a Range

Figure 15-14 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.

image from book
Figure 15-14: Displaying a calendar using a single array formula.

On the CD

This workbook is available on the companion CD-ROM. The file is named array formula calendar.xlsx.

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

Select B2:H2 and merge the cells by choosing Home Alignment Merge & Center.
Enter a date into the merged range. The day of the month isn't important.
Enter the abbreviated day names in the range B3:H3.

Select B4:H9 and enter this array formula. Remember, to enter an array formula, use 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),MONTH(B2),1))-1)+ {0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)}

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.
Adjust the column widths and format the cells as you like.

Change the month and year in cell B2, and the calendar will update automatically. After creating this calendar, you can copy the range to any other worksheet or workbook.

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.

Following is another version of the formula. The IF function in the original formula checks each date to make sure it's in the current month. If not, the IF function returns an empty string.

You can simplify the array formula quite a bit by removing the IF function.

 =DATE(YEAR(B2),MONTH(B2),1)-(WEEKDAY(DATE(YEAR(B2),MONTH(B2), 1))-1)+{0;7;14;21;28;35}+{0,1,2,3,4,5,6}

Cross Ref

See Chapter 14 for more information about array constants.

This version of the formula displays the days from the preceding month and the next month, as shown in Figure 15-15.

image from book
Figure 15-15: A simpler version of the formula also displays dates from the preceding and subsequent month.