topic Re: Calculation across rows in Discussions

Calculation across rows

JesperJohansen — Mon, 29 Apr 2013 12:18:14 GMT

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.

Re: Calculation across rows

jeff3928 — Mon, 29 Apr 2013 15:06:06 GMT

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

Re: Calculation across rows

ms — Mon, 29 Apr 2013 15:10:11 GMT

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.

Re: Calculation across rows

JesperJohansen — Wed, 08 May 2013 11:31:11 GMT

@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.

Re: Calculation across rows

pmroz — Wed, 08 May 2013 13:19:11 GMT

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.

Re: Calculation across rows

JesperJohansen — Tue, 14 May 2013 09:02:13 GMT

@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?