How to get a formula column by row sub group

[#1]

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.

[#2]

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

[#3]

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.

[#4]

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

[#5]

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.

[#6]

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)))

[#7]

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?

[#8]

try

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

[#9]

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)

[#10 · Accepted solution]

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)))