Create an array of numbers

[Lori]

A key to many Excel formulas is the ability to return an array of integers {1;2;...;n}. One of my hopes with each new release has been that MS would include a function that did this for you like =INTARRAY(n). It's easy to write such a function in VBA but then you may have to worry about distributing a workbook containing macros.

One basic method that you often see for a given Range is to use:
=ROW(Range)-MIN(ROW(Range))+1
While not totally ideal, this works fine for most purposes.

Q. But how about if you want to construct an array that is not based on a range?

AFAIK there are three basic options available:
=ROW(INDIRECT("1:"&intCount))
=ROW(OFFSET(A1,,,intCount))
=ROW(INDEX(A:A,1):INDEX(A:A,intCount))

These all do the job but there are unwanted side effects in that they add unnecessary references and/or volatility. This means that the formula:
a) may be sensitive to worksheet operations such as row deletion or insertion and recalculate
b) recalculates either every time the range that is referred to changes (eg: A:A in the last case) or every time any cell changes in the case of the first two. All three of these recalculate at start up causing the save changes prompt to appear by default even if no changes have been made to the workbook.

Q. So what other alternatives are there?

Until now i didn't think there were any but I just stumbled upon a way to return an array of numbers given only an array of text items or even just a number without needing to refer to a range. This method allows:

In Excel 2010 and prior, to return an array of numbers based on an array.
eg: using {"apple,"pear","banana","orange"} return {1;2;3;4}

In Excel 2013 just given a number, to return an array of numbers of that size.
eg: using 4 to return {1;2;3;4}

I had thought of entering this as a challenge but decided that it was more of theoretical interest. I would be interested to know any other ideas people have for this.

 

[Hui]

Lori

The use of Named Formula andn arrays is a great way to sppeed up Excel and makes amazing things like my Animated Pendulums possible

You list the 4 main methods of establishing an Array in Excel
These being:
=ROW(INDIRECT("1:"&intCount))
=ROW(OFFSET(A1,,,intCount))
=ROW(INDEX(A:A,1):INDEX(A:A,intCount))
={1;2;3;4}

The function using Indirect and Offset are Volatile
The Index methods would be the preferable route as it is Quasi Volatile
The Index method is quasi volatile in that it is recalculated on the spreadsheet open event

Obviously the hard coded arrays of ={1;2;3;4} are great in situations where the values will never change

You can also have arrays setup using VBA in which case you have full control of when it is updated.

My preference is using Offset, simply as it is easier to read/explain. It rarely results in any appreciable slowdown of things

 

[Lori]

Hui - many congrats on MVP status! Nice work on the animated pendulums workbook which is awesome. There was also a related discussion with Doug Jenkins about a Hypercube animation constructed using only named formulas. Daniel Ferry has many other amazing examples of this technique at Excel Hero.

For generating arrays of numbers, here is one more approach I wanted to share. As i already said it may be more academic than practical (but from experience of thinking that before, unexpected uses have often cropped up.)

1) For the text array example above, a formula which returns {1,2,3,4} is this:

=TREND(N({"apple","pear","banana","orange"})+3,,,0)

Note that this also works for vertical or square arrays.

2) In Excel 2013, we can use this to return {1,2;3,4}:

=TREND(MUNIT(2)*0+3,,,0)

For a vertical array {1;2;3;4} change MUNIT(2) to INDEX(MUNIT(4),,1).

Now how to adjust these for other numbers of elements? Clearly for 1 element the constant to add is +1, so assuming the relation is linear we can fill in the other terms: (1,1) (2,5/3) (3,7/3) (4,3) ...

The attached workbook has some ways to generalise this... but I'd suggest trying to figure out the logic first before opening. In particular if you have a little stats knowledge, it's a fun exercise to derive the linear relationship above.

 

[Colin Legg]

I tend to use a UDF to do this.

Nice, Lori, I haven't seen (2) before. That could be handy!

 

[Ufoo]

Hello excel ninjas, today I have learnt this trick for generating an array of numbers: MATCH(ROW($A$2:$A$9), ROW($A$2:$A$9). Any ideas?