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.

2 comments:

Anonymous said...

Absolutely brilliant. I stumbled upon your blog doing a Google search for a means by which I might generate a successive integer string for a factorial function nested in an array formula, and rarely is it the case that a solution presents itself so readily on the internet. Thank you for your publication of this answer.

LyCresha said...

In you are told a model will be 1:16 scale. Use a formula to convert the lenghts of each model to feet and the another formula to convert to inches. And are given numbers like: boat 17, car 20, truck 80, train 330 how do you do this? Please help