Scripting Guide > Data Tables > Calculations
Publication date: 08/13/2020


This section discusses functions for pre-evaluated columnwise and rowwise statistics and shows how JSL expressions work behind the scenes in the JMP formula calculator.

Pre-Evaluated Statistics

The following functions are special, pre-evaluated functions: Col Maximum, Col Mean, Col Minimum, Col N Missing, Col Number, Col Quantile, CV (Coefficient of Variation), Col Standardize, Col Std Dev, Col Sum, Maximum, Mean, Minimum, NMissing, Number, Std Dev, and Sum.

Note: Statistics are also computed with Summarize (Store Summary Statistics in Global Variables). Although the named arguments to Summarize have the same names as these pre-evaluated statistic functions, they are not calling the pre-evaluated statistic functions. The resemblance is purely coincidental.

All the statistics are pre-evaluated. That is, JMP calculates them once over the rows or columns specified and thereafter uses the results as constants. Because they are computed once and then used over and over again, they are more efficient to use in calculations than the equivalent formula-calculated results.

When JMP encounters a pre-evaluated function in a script, it immediately evaluates the function and then uses the result as a constant thereafter. Therefore, pre-evaluated functions enable you to use columnwise results for rowwise calculations. For example, if you use Col Mean inside a column formula, it first evaluates the mean for the column specified and then uses that result as a constant in evaluating the rest of the formula on each row. A formula might standardize a column using its pre-evaluated mean and standard deviation:

( Height-Col Mean( Height ) ) / Col Std Dev( Height )

For the Big data, Col Mean(Height) is 62.55 and Col Std Dev(Height) is 4.24. So for each row, the formula above would subtract 62.55 from that row’s height value and then divide by 4.24.

Note: Pre-evaluated functions disregard the excluded row state, meaning that any excluded rows are included in calculations. For summary statistics that obey row exclusion, use the Distribution platform.

Columnwise Functions

The functions whose names begin with “Col” all work columnwise, or down the values in the specified column, and return a single number. For example, Col Mean(height) finds the mean of the values in all the rows of the column height and returns it as a scalar result. Some examples include the following:

Average Student Height = Col Mean( height );
Height Sigma = Col Std Dev( height );

With the Col functions, column properties such as Missing Value Codes assign data values that produce incorrect calculations. 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, refer to Col Stored Value() in the formula:

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

Rowwise Functions

The functions withoutCol” listed below work rowwise across the values in the variables specified and return a column result. For example, Mean(height, weight) finds the mean of the height and weight for the current row of the data table. The rowwise statistics are valid only when used in an appropriate data table row context. The following are some possibilities:

dt = Open( "$SAMPLE_DATA/Big" );

// scalar result for row 7 assigned to JSL global variable

Row() = 7;
::scalar = Mean( height, weight );

// formula column created in data table

dt << New Column( "Scaled Ht-Wt Ratio",
	Formula( Mean( height, weight ) / age )
vector = J( 1, 40 ); // create a 1x40 matrix to hold results
For Each Row( vector[Row()] = Mean( height, weight ) ); // fill the vector

Rowwise functions can also take vector (column matrix) or list arguments, as follows:

myMu = Mean( [1 2 3 4] );
mySigma = Std Dev( {1, 2, 3} );
Want more information? Have questions? Get answers in the JMP User Community (