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
- :
- How to get a formula column by row sub group

News

We’re asking you to select a content label when starting a new topic in the Discussions area. Read more to find out why.

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

Highlighted

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

Feb 10, 2019 7:41 AM
(4531 views)

Hi there,

I d like to create a formula column (sum or mean etc.) with sub groups. The sub groups should be selected by rows, for example all 5 following rows drom a data table or in general all N rows. Any ideas to solve this with row()?

Attache a file to add the formula column.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

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

Not sure why you see this error message. You could try updating to the latest version to see if it helps. But, you could hard code the value of *n* to avoid the variable in the formula if the number is constant.

Or try with *Local()* instead of *Parameter()* (which actually is what I intended in my first post).

`Local({n = 5}, Col Sum(:Data, Ceiling(Row() / n)))`

12 REPLIES 12

Highlighted
##

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

Re: How to get a formula column by row sub group

The Col Moving Average() function is what you need to use.

For your averaging the formula would be:

`Col Moving Average( :Data, 1, 0, 5 )`

To calculate the sum it would be

`Col Moving Average( :Data, 1, 0, 5 ) * 6`

Documentation on the function is found in the Scripting Index

Help==>Scripting Index

look for Statistical Functions

Jim

Highlighted
##

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

Re: How to get a formula column by row sub group

Hi Jim,

Thanks for your fast response. Actually I'm looking for column formulas for row sub groups (for example row1 1-5, row1 6-10 ..., in general row 1-n, row n+1 - 2n, row 2n+1 - 3n...), where I can apply the column formulas from JMP.

Thanks for your fast response. Actually I'm looking for column formulas for row sub groups (for example row1 1-5, row1 6-10 ..., in general row 1-n, row n+1 - 2n, row 2n+1 - 3n...), where I can apply the column formulas from JMP.

Highlighted
##

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

Re: How to get a formula column by row sub group

That is a bit confusing......could you provide a sample table?

Jim

Highlighted
##

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

Re: How to get a formula column by row sub group

Attached a sample file with a new column "Sum_of_each_5_Data_rows". This shows the sum of sub groups build from every 5 rows (N=5) of the data column.

How could this be done by using any given row numbers for the subgroup to create column formulas like Col Sum, Col Mean, Col Min etc.

Highlighted
##

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

Re: How to get a formula column by row sub group

Try using a Row() transformation as optional ByGroup argument of the "Col Stat()" functions.

For example:

`Parameter({n = 5}, Col Sum(:Data, Ceiling(Row() / n)))`

Highlighted
##

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

Re: How to get a formula column by row sub group

Hi ms,

Thanks for your support. Your formula works great. Despite the formula delviers the approriate reuslts, the formal editor is marking n red in the formul part:

`( Row() / n ) ) )`

and delievers an error code. The error code is "Error: Name Unresolved: n". Are there any reasons to be concerned?

Highlighted
##

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

Re: How to get a formula column by row sub group

try

`n = 5; Col Sum(:Data, Ceiling(Row() / n))`

Jim

Highlighted
##

Thanks Jim. Your formual works also. However, similar to the solution from ms I'm getting the error code from the formula editor for n "Error: Name Unresolved: n", despite the formual is working (JMP 14.0)

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

Re: How to get a formula column by row sub group

Highlighted

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

Not sure why you see this error message. You could try updating to the latest version to see if it helps. But, you could hard code the value of *n* to avoid the variable in the formula if the number is constant.

Or try with *Local()* instead of *Parameter()* (which actually is what I intended in my first post).

`Local({n = 5}, Col Sum(:Data, Ceiling(Row() / n)))`