Create array of numbers

Creating an array of numbers of certain length like {1;2;3;4;5} on a worksheet is something that comes up occasionally.  But getting a perfect formula to create an array fill that’s not affected by worksheet changes can be a little bit tricky. However, a technique based on ROW and INDIRECT functions precisely what you need. This formula excels where numeric arrays are needed.

Generic Formula

{=ROW (INDIRECT (start & “:” & end))}

……where the “start” is the 1st number and the “end” is the last number in the array.

Example

If you want to want to get create arrays of numbers, here is an example of how this formula. Can be helpful:

Figure 1. Example 1 of ROW/INDIRECT Functions

As seen in the ‘Example1’, the formula in D6 is:

=ROW (INDIRECT (B6 & “:” & C5))

…the formula creates array of numbers like {1;2;3;4;5;6;7}.

This is How the Formula Works

In this example, the formula picks the “start” and “end” numbers from B6 and C6 respectively. This is how the breaks down the values:

=ROW (INDIRECT (B6 & “:” & C6))

=ROW (INDIRECT (1 & “:” & 5))

=ROW (INDIRECT (“1:5”))

=ROW (1:5)

= {1; 2; 3; 4; 5}

The INDIRECT function guards against worksheet changes and thus any changes in the row either by deleting or inserting new rows not alter the range reference. Here is an example of would happen:

=ROW (1:5)

Given the above, it will change to this if a row is deleted:

=ROW (1:4)

That is because the INDIRECT functions work with a reference that is constructed with text which is altered by changes on the worksheet.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar