Spreadsheet Formulas - Row, Column Functions

Statistica provides a predefined broad selection of row and column functions that can be used in spreadsheet formulas like all other functions. Following is a list of all available row and column spreadsheet functions (parameters are given in parentheses).
 

CUSUM(vref)

Returns the cumulative sum of the variable vref

DATA(vref, row)

Returns the value from a specified spreadsheet cell. For example:

The following will fill current variable with whatever variable 3 has in row 2:

= DATA(V3, 2)

The following will create a Fibonacci series:

=iif(V0 <= 2, 1, DATA(VCUR, V0-1) + DATA(VCUR, V0-2))

The following produces a cumulative sum of variable V3:

=iif(V0 <= 1, V3, DATA(V3,V0) + DATA(VCUR, V0-1))

NOTE: If the variable-ref is to be computed or an absolute index is needed, use VREF as in DATA(VREF(1), V0).

DIF(vref, offset)

Returns the difference between the current value of variable vref and its value in the current row less offset. For example (when applied to Variable V3):

=DIF(V3, 3)

Is equivalent to

=V3 - LAG(V3,3)

LAG(vref, offset)

Returns value of variable vref in the current case less the offset. For example:

=LAG(V3, -3)

=LAG("MEASURE01", V2)

The following will perform a cumulative sum of V3:

=iif(V0 <= 1, V3, V3+LAG(VCUR, 1))

NCASES

Returns the number of cases in the current spreadsheet. For example:

The following will fill the current variable with the inverse order of what is in V3

=DATA(V3, NCASES-V0+1)

NVARS

Returns the number of variables in the current spreadsheet. For example:

The following will always reference the last variable in the file:

=VREF(NVARS)

V0

Number of currently processed case

VREF(column_number)

Convert the numeric argument to a variable reference with value of that variable in the current row. For example:

Example:

=VREF(2)

Is equivalent to

=V2

The power of is that it can evaluate at runtime. For instance:

=VREF(V3)

VCUR

Return the current variable number being processed. It is useful to make formulas more portable. For example:

=VCUR + 1

If applied to V3, then this will add one to V3, and would be equivalent to =V3+1.

See also: Distributions and their Functions, Spreadsheet Formulas - Overview, Spreadsheet Formulas - Syntax Summary, Spreadsheet Formulas - Examples, Spreadsheet Formulas - Syntax Operators, Spreadsheet Formulas - Distributions and their Functions.