Calculation across rows

[#1]

Is it possible to do calculations across rows. How for instance do I:

• devide a number i column A, row 1 with a number in column B, row 2?
• calculate the standard deviation of the numbers in column A, which fulfill a criteria based on the content on column B (not via the "X By Y", but directly in the table).
• Convert row to columns, that is create a column for each value in column A, with the value in column B.

[#2]

This are not specific answers but here are some potentially helpful ideas to get you started:

• Check out the Dif() and Lag() operators to use in a formula
• Create column C with a formula that copies of the value in column A if a condition in column B is met otherise leave as empty "."
• Use the Split() function

[#3 · Accepted solution]

Although the JMP data table is not a "free-form" spreadsheet like an Excel sheet (e.g a column can only contain data of a single data type and formulas are applied to the entire column) all of the above is possible.

• devide a number i column A, row 1 with a number in column B, row 2?

The column formula :A[1] / :B[2] applies that particular ratio to all rows.

The column formula :A / :B[Row() + 1] give each row the ratio between the current value of A with the next row value of B.

• calculate the standard deviation of the numbers in column A, which fulfill a criteria based on the content on column B (not via the "X By Y", but directly in the table).

The column formula Col Std Dev( :A, :B ) gives the standard deviation of values in A for each level of B. More complex criteria can be acccomplished but may involve an intermediate column that categorize B.

• Convert row to columns, that is create a column for each value in column A, with the value in column B.

Use the Transpose command in the Tables menu.

With JSL script you'll have many more options than the above examples based on column formulas.

[#4]

@MS:

Thank you for your reply. That helped me a great deal. However I have run into a new problem:

I need a function to return the row number of a row in which a cell in a give column meets a certain criteria. E.g.:

• Return the row number of the cell in Column 1 with the value "A". Assuming "A" appear only once in the data set.
• Return the row number of the previous (or next) row, relative to the row in which the formula result is displayed, in which the value in Column 1 is "A". "A" may appear several times in the data set.
• Return the row number of the cell in Column 1 with the value is closest to a given number.

[#5]

Use the get rows where capability:

dt = open("$sample_data\Big Class.jmp");

match_rows = dt << get rows where(:sex == "F");

print(match_rows);

[1, 2, 3, 4, 5, 9, 10, 11, 16, 17, 18, 19, 20, 28, 29, 35, 36, 38]

The match_rows variable is a matrix that you can reference by the index.  I.e. match_rows[1] is the first element, etc.

[#6]

@PMroz:

Thank you. I'm sorry, but I'm not experienced in the scripting side of JMP. I don't quite understand your answer. Is there any way to do perform the above mentioned actions directly in the Formula editor?