Investment Studio > Expressions > References

In many contexts, expressions can contain references to values which are stored or computed elsewhere. This requires the location of each referenced value to have a name, unique in its context.

References, spreadsheet style

The simple spreadsheet in the Macros view is a good place to experiment with references, since it automatically assigns a unique name to each cell. The name is constructed by appending the cell's row number to the letter(s) heading the cell's column. For instance, the cell in column B and row number 1 is called B1, so its value can be accessed like this:

Note the absence of quotation marks. References are not strings; they are identifiers just like TRUE, FALSE and function names. As such, they are not case sensitive, so the expressions

=B1

and

=b1

are equivalent.

Many functions can take cell ranges as arguments. Ranges are specified by sandwitching the range operator between two single cell references:

Here, the SUM function is used to add up the values of all cells in the range spanned by corner cells B1 and C2.

Indirect references are also possible:

Here, the value of cell B1 is a string ("c1") which can be interpreted as a cell name. This is done by the expression in cell A1: the INDIRECT function looks at the value referenced by its argument ("c1", referenced by the argument B1), interprets it as a second reference (C1) and returns the value referenced by it (1). In other words, the argument to INDIRECT is a reference to an "address" cell (B1) which contains the name of the cell (C1) which in turn contains the value to be returned (1).

Note that the address cell does not contain an actual reference; it contains a string. It is the INDIRECT function which interprets it as a reference.

A reference to an array-valued cell yields the value of the first element in the array, i.e. the value displayed in the cell. For instance, if cell B1 contains the constant array {1, 10, 100},

the expression

=B1

will return the value 1:

To access the entire array, you can use the ARRAY function:

The subexpression array(b1) returns the array value of cell B1. Its elements can then be accessed using the INDEX function, just as if it were an array constant specified within the expression:

References, the general case

As far as the use of references is concerned, the only difference between the spreadsheet in the Macros view and other contexts is the way names are assigned to cells. The spreadsheet is the only place where cells are automatically named. In other contexts, you must declare such names (generally known as symbols) explicitly, although special, context-specific symbols may also be automatically defined.

Symbols can contain any alphanumeric character (letters A through Z, numbers 0 through 9) and are not case sensitive (i.e. "_SYMBOL" and "_symbol" are considered identical). The use of a leading "_" (underscore) is recommended to differentiate symbols from built-in identifiers (e.g. function names) but is not mandatory.

The details of how symbols are declared and used in different objects are covered in each object's documentation. See e.g. the section on symbols in Grid object record definitions.