topic I want to add formula to subtract 1st and 4th row, then 2nd and 5th row.. then 3rd and 6th row and so on..urgent please help! in Discussions

I want to add formula to subtract 1st and 4th row, then 2nd and 5th row.. then 3rd and 6th row and so on..urgent please help!

LearningJMP — Fri, 09 Jun 2023 17:08:08 GMT

Hi,

:RESULT is the column where I want to subtract row 1 and row 4, then row 2 and row 5 and so on until all the rows finishes.

:TEST is currently an empty column where I want to store the results of :RESULT column

I tried the following script in the formula but it doesn't work? Not sure why?

:RESULT << Data Type( Numeric );
:TEST << Data Type( Numeric );
For( i = 1, i <= N Rows(), i++,
	Abs( :RESULT[i] - :RESULT[i + 3] )
);

Re: I want to add formula to subtract 1st and 4th row, then 2nd and 5th row.. then 3rd and 6th row and so on..urgent please help!

LearningJMP — Fri, 19 Aug 2022 19:13:48 GMT

Figured it out:

Had to update line:

Abs( :RESULT[i] - :RESULT[i + 3] )

:TEST[i]=Abs( :RESULT[i] - :RESULT[i + 3] )

Re: I want to add formula to subtract 1st and 4th row, then 2nd and 5th row.. then 3rd and 6th row and so on..urgent please help!

txnelson — Fri, 19 Aug 2022 19:11:03 GMT

Your formula should not contain a For() loop, unless you are running this not as a JMP formula, but rather as JSL in open code. If you specify the code as being a formula for the column, JMP automatically loops through each row of the data, and applies the formula. Thus, the For() loop is not required Below is a little script that creates a new column and then adds a formula for the column.

Names Default To Here( 1 );
dt = Current Data Table();
dt << New Column( "The New Column",
	formula( If( :ATT1 == 0, 0, Abs( :RESULT - :RESULT[Row() + 3] ) ) )
);

If you have an empty column(for example, called "mytest") you can use a For Each Row() function to loop through the data table and calculate values

Names Default To Here( 1 );
dt = Current Data Table();
For Each Row(
	If( :ATT1 == 0,
		:mytest = 0,
		:mytest = Abs( :RESULT - :RESULT[Row() + 3] )
	)
);

Finally, as you have indicated, you can use a For() loop to loop through the data table. This code requires that one specifies the specific row to operate on

Names Default To Here( 1 );
dt = Current Data Table();
For( i=1, i<=N Rows(dt), i++,
	If( :ATT1[i] == 0,
		:mytest[i] = 0,
		:mytest[i] = Abs( :RESULT[i] - :RESULT[i + 3] )
	)
);

Re: I want to add formula to subtract 1st and 4th row, then 2nd and 5th row.. then 3rd and 6th row and so on..urgent please help!

Georg — Mon, 22 Aug 2022 06:20:50 GMT

In addition, to calculate with different rows, I recommend the function lag, that returns the value of a row relative to current row, and you do not need a loop.

Re: I want to add formula to subtract 1st and 4th row, then 2nd and 5th row.. then 3rd and 6th row and so on..urgent please help!

jthi — Mon, 22 Aug 2022 07:33:55 GMT

Besides using Lag to calculate there is also specific row function for this: Dif ( JSL Syntax Reference > JSL Functions > Row Functions - Dif(col, n) ). Sometimes it might work better than using Lag.