Arrays

An array is a group of items within a number cell. Arrays consist of columns and rows of data. The smallest array would have one column and one row; the largest would have many columns and many rows. In practice, the size of an array is limited by the memory in your computer.

Arrays can only be entered in number cells. Enter an array by enclosing the array contents in curly brackets ({ }). Data in the same row in an array is separated by commas; rows in the array are separated by semi-colons.

Examples:

{1,2,3} three columns, one row

{"a";"b";"c"} one column, three rows

{A1,B1;A2,B2} two columns, two rows

Arrays can contain constant numbers, dates, strings, names or cell references.

Arrays cannot contain ranges or other arrays or formulae.

You can mix different types of data in the same array.

Example:

{"fred",10,A1} three columns, one row—containing a text string, a number and a cell reference

If you enter an array into a number cell, PipeDream displays the first element in the array, i.e. the element in column one, row one. To see what else is in the array, you must use the INDEX function to select a different element or pass the array to another function to calculate a result.

Passing Arrays To Functions

You can pass arrays to functions and to custom functions. As far as arrays are concerned, there are two types of function: those which have array parameters and so expect arrays and those which do not. If a function has an array parameter, the array is processed explicitly within the function.

One function which does expect an array is INDEX. Its first parameter must be an array; the INDEX function picks out one or more elements from the array that you pass to it.

When you pass an array to a function or operation which does not expect one, PipeDream automatically calls the function for each element in the array. The result of the function is an array of values of the same size.

An example of such an operation is ‘+’. If you enter the following formula:

{1,2,3}+{4,5,6} the result is {5,7,9}

similarly,

SGN({-100,0,100}) returns {-1,0,1}.

If the function or operation has more than one parameter, and you pass arrays of different sizes to it, PipeDream automatically expands the smaller array or arrays to the size of the largest.

If the array to be expanded has the same number of rows but fewer columns than the largest array, PipeDream copies the last column in the array until the array is large enough. If the array to be expanded has the same number of columns but fewer rows than the largest array, PipeDream copies the last row until the array is large enough. If both the number of rows and the number of columns is different PipeDream fills the spaces in the array with blanks.

Example:

{1,2,3}*4 expands to {1,2,3}*{4,4,4} resulting in {4,8,12}.

{1,2;3,4}*-1 expands to {1,2;3,4}*{-1,-1;-1,-1} resulting in{-1,-2;-3,-4}.

{55,66}+{1,1;1,1} expands to {55,66;55,66}+{1,1;1,1} resulting in {56,67;56,67}.

Arrays and Custom Functions

You can also pass arrays to custom functions. This will have one of two effects depending on whether or not the custom function expects an array as one of its parameters. If it does not expect an array as a parameter, it will behave as an ordinary function does, i.e. it will return an array of results. If the custom function does expect arrays as one or more of its parameters, you must supply arrays only for those parameters. The custom function must contain the instructions to process the array itself. If you supply an array for another parameter, you will get an error message.

Arrays and Ranges

In most cases, arrays and ranges of cells are interchangeable as parameters to functions and operators. If you pass a range of cells instead of an array, PipeDream treats it as an array of the same dimensions as the range of cells, containing the contents of the range of cells.

Arrays and Ranges in Lists

If you give arrays and ranges of cells as the parameters to functions which expect lists, like SUM, PipeDream includes every element in each array and every cell in each range of cells in the list.

Example:

SUM(A1A2,{1,2}) adds the contents of cell A1, the contents of cell A2, 1 and 2.
If cells A1 and A2 contain arrays themselves, each element of these arrays will be added too, so if cell A1 contains {3,4} and cell A2 contains {5,6}, the result will be 1+2+3+4+5+6.

A powerful usage of arrays, ranges and functions is to have one function call calculating many results.

Example:

SUM(A1A100*B1B100) causes all the products A1*B1, A2*B2, … A100*B100 to be calculated and then adds them all together to produce a result.

Special Functions

Some functions, such as the complex number functions and the matrix functions, only operate on arrays and ranges.

For information on See
Using arrays in custom functions Custom Functions