Returning an Array of Sequential Numbers in Excel

ID: Q75834

SUMMARY

The following article discusses the use of the ROW and COLUMN functions to generate an array of sequential values.

MORE INFORMATION

Array formulas generally use arrays that represent a series of values. Usually each array can be represented by a range of values on the worksheet and is entered in the form of a reference. If the values in the array are numerous, however, entering the values on the worksheet can be cumbersome and manually entering the array can be tedious.

If the values in the array are sequential in nature, the ROW function is an alternative to manually entering all the values. The ROW function returns the row number of a specified cell reference. When the ROW function is used with a range of cells in an array formula, an array of row numbers is returned. For example, the formula {=ROW(A1:A10)} returns the array {1;2;3;4;5;6;7;8;9;10}. It does not matter what cells A1:A10 contain, and A1:A10 can be substituted with other ranges as long as the range of cells fall within the desired range of row numbers.

Consider the following example. Suppose cell A1 contains the word "EXISTENTIALISM" and you would like to write an array formula that places each character in this word in its own cell down column B. The formula would be entered by highlighting cells B1:B14, typing


   =MID($A$1,ROW(A1:A14),1)

and pressing CTRL+SHIFT+ENTER. Cells B1:B14 now contain "E", "X", "I", and so on, respectively. If the formula had been entered without the ROW function, it would have been entered as:


   =MID($A$1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14},1)

This method allows for more flexibility and ease of use, especially if the function will be entered several times using different ranges of values.

This method can also be used to return other series of numbers. Suppose you need an array of numbers that appear as:


   {10;20;30;40;50;60;70;80;90;100}

The ROW function would be entered as {=ROW(A1:A10)*10}.

The ROW function returns a vertical array of numbers. If a horizontal array of numbers is desired, simply use the COLUMN() function and a horizontal range of cells. For example, the formula


   {=COLUMN(K1:T1)}

returns the array {11,12,13,14,15,16,17,18,19,20}.

REFERENCES

"Microsoft Excel Function Reference," version 3.0, pages 34 and 201

"Microsoft Excel User's Guide," version 3.0, pages 276-289

Additional query words: noupd

Keywords :
Version :
Platform :
Issue type :