Publication date: 08/13/2020

Row Functions

Adding a row function to a formula lets you reference specific rows or cells within specific rows. You can also insert values based on an arithmetic sequence. See Row Functions in the Scripting Guide for more information about syntax.

Col Stored Value

Returns the column value that does not have column properties applied to it. If the row is not specified, the current row is used.

Suppose that the Missing Value Codes column property is assigned to the x1 column to treat “999” as a missing value. Another column includes a formula that calculates the mean. To use the value “999” instead of a missing value to calculate the mean, use Col Stored Value in the formula:

Mean( Col Stored Value( :x1 ), :x2, :x3 )

Count

Creates a list of values beginning with the from value and ending with the to value. The number of steps specifies the number of values in the list between and including the from and to values. Each value determined by the first three arguments of the count function occurs consecutively the number of times that you specify with the times argument. When the to value is reached, Count starts over at the from value.

Also, you can add the times argument with the insert button on the keyboard. This argument is one by default, but repeats the count process as many times as you specify, as illustrated by the Count4 column in the data table in Figure A.2. To add any argument to the Count function, highlight the argument preceding the one that you want to enter. Either type a comma or use the insert button on the Formula Editor keypad.

The columns in the data table below result from the following formulas:

Count (1, 9, 2) gives Count 1

Count (1, 9, 3) gives Count 2

Count (1, 9, 9) gives Count 3

Count (1, 9, 3, 3) gives Count 4

Figure A.2 Example of the Count Function 

The Count function is useful for generating a column of grid values. For example, the following formulas create a square grid of increment NRow(). NRow() is the Row function that gives the total number of rows in the data table. NRow() also creates axes that range from –5 to 5:

Count(–5, 5, Root( NRow() ) );
Count(–5, 5, Root( NRow() ), Root( NRow() ) );

Dif

Returns the difference between the value of the first argument in the current row and its value in the row defined by the current row less the second argument. The default Dif is one, which you can change to any number. Note that Dif(X, n) gives the same result as XRow()–XRow()-n, or as XRow()–Lag(X, n).

Dim

Returns a row vector with the dimensions of the current data table, a specified data table, or a matrix. The dimensions are the number of rows and the number of columns and are listed in that order.

Lag

Returns the value of the first argument in the row defined by the current row less the second argument. The default Lag is one, which you can change to any number. The value returned for any lag that identifies a row number less than one is missing. Note that Lag(X, n) gives the same result as the subscripted notation, XRow( )–n.

NRow

Returns the total number of rows in the active data table.

Row

Returns the current row number when an expression is evaluated for that row. You can use Row() in any expression, including column name subscripts. The default subscript of a column name is Row() unless otherwise specified.

Subscript

Enables you to use a column’s value from a row other than the current row. After choosing Subscript from the list, enter a numeric expression into the subscript argument. Subscripts that evaluate to nonexistent row numbers produce missing values. Column names with no subscript refers to the current row. To remove a subscript, select the subscript and delete it. Then delete the missing box.

The formula CountRow() – CountRow()–1, where Row() is the row number as described below, uses subscripts to calculate the difference between each pair of values from the column named Count. This result is the same as that given by the Dif() function. When Row() is 1, the computation produces a missing value.

The formula below calculates a column called Fib, which contains the terms of the Fibonacci series (each value is the sum of the two preceding values in the calculated column).

It shows the use of subscripts to do recursive calculations. A recursive formula includes the name of the calculated column, subscripted such that it references only previously evaluated rows (rows 1 through (i–1)). The calculation of the Fibonacci series shown includes a conditional expression and a comparison. See the sections Conditional Functions and Comparison Functions.

Sequence

Produces an arithmetic sequence of numbers across the rows in a data table, where the start value, ending limit, and increment are specified as arguments.

Want more information? Have questions? Get answers in the JMP User Community (community.jmp.com).
.