Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

You can create an array constant, give it a name, and then use this named array in a formula. Technically, a named array is a named formula.

Figure 14-4 shows a named array being created by using the New Name dialog box, which is displayed when you choose Formulas Defined Names Define Name. The name of the array is DayNames, and it refers to the following array constant:

{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}

Figure 14-4: Creating a named array constant.

Notice that in the New Name dialog box, the array is defined by using a leading equal sign (=). Without this equal sign, the array is interpreted as a text string rather than an array. Also, you must type the curly brackets when defining a named array constant; Excel does not enter them for you.

After creating this named array, you can use it in a formula. Figure 14-5 shows a worksheet that contains a single array formula entered into the range A1:G1. The formula is

{=DayNames}

Figure 14-5: Using a named array in an array formula.

Because commas separate the array elements, the array has a horizontal orientation. Use semicolons to create a vertical array. Or, you can use Excel's TRANSPOSE function to insert a horizontal array into a vertical range of cells. (See "Transposing an Array," later in this chapter.) The following array formula, which is entered into a seven-cell vertical range, uses the TRANSPOSE function:

{=TRANSPOSE(DayNames)}

You also can access individual elements from the array by using Excel's INDEX function. The following formula, for example, returns Wed, the fourth item in the DayNames array:

=INDEX(DayNames,4)