Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Calculation across rows

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 29, 2013 5:18 AM
(6065 views)

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

Jesper

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 29, 2013 8:10 AM
(9539 views)
| Posted in reply to message from JesperJohansen 04/29/2013 08:18 AM

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.

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculation across rows

Apr 29, 2013 8:06 AM
(5724 views)
| Posted in reply to message from JesperJohansen 04/29/2013 08:18 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 29, 2013 8:10 AM
(9540 views)
| Posted in reply to message from JesperJohansen 04/29/2013 08:18 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculation across rows

May 8, 2013 4:31 AM
(5724 views)
| Posted in reply to message from JesperJohansen 04/29/2013 08:18 AM

@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

Jesper

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculation across rows

May 8, 2013 6:19 AM
(5724 views)
| Posted in reply to message from JesperJohansen 05/08/2013 07:31 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Jesper