cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
JesperJohansen
Level IV

Calculation across rows

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.
BR
Jesper
1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Calculation across rows

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.

View solution in original post

5 REPLIES 5
jeff3928
Level III

Re: Calculation across rows

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
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Calculation across rows

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.

JesperJohansen
Level IV

Re: Calculation across rows

@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.
BR
Jesper
pmroz
Super User

Re: Calculation across rows

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.

JesperJohansen
Level IV

Re: Calculation across rows

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

BR
Jesper