Array Formulas

Here are some tips related to the use of Array Formulas in Excel.

1. When using an Array Formula, you press Ctrl+Shift+Enter instead of just Enter after entering or editing the formula.

2. An Array Formula will show curly brackets around the formula in the Formula Bar like this: {=…}

3. Vector of 1's: The following formula will return an n x 1 vector of ones:
(1+0*ROW(OFFSET(INDIRECT("A1"),0,0,n,1)))

4. Sequential Number Vector (1, 2, 3, ...): To return an n x 1 vector of sequential numbers starting with 1, use the following formula:
ROW(OFFSET(INDIRECT("A1"),0,0,n,1))
or
ROW(INDIRECT("1:"&n))

Note: The INDIRECT function is used in these examples to avoid problems that would occur if cell A1 were deleted or if rows were inserted above cell A1 or columns were inserted to the left of A1.