• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create an array of numbers

Lori

Active Member
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

Excel Ninja
Staff member
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

Active Member
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.
 

Attachments

Ufoo

Member
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?
 
Top