334101_en-US

liliconcom

how do I multiply each row values backward in one column until a criteria is met

Hello, I have the following challenge that I have trouble scripting with.

 

My starting data table looks like below. I would like to multiply Xi backwards starting from col2 =0 as the criteria and put the new values in a new column.

col1 col2

X1  0

X2  y2

X3  y3

…

Xn  yn

XX1 0

XX2 yy2

….

XXn yyn

….

….

My end data table would look like this:

col1 col2 col3

X1  0  Xn*Xn-1*……X3*X2*X1

X2  y2 Xn*Xn-1*…..X3*X2

X3  y3 Xn*Xn-1*….*X3

…      

Xn  yn  Xn

XX1 0   XXn*XXn-1*…..XX2*XX1

XX2 yy2 XXn*XXn-1*…..XX2

….

XXn yyn XXn

….

….

Can someone please help out? Very much appreciated!

ScriptingRe: how do I multiply each row values backward in one column until a criteria is met

I don't understand your 3 sets of columns.  It appears as if you are changing column names in the middle of a data table, which will not work in JMP.  Is this table format coming from an Excel spreadsheet?

Here is a simple formula that will do what you want for a data table that looks like 

I have attached the data table.

The formula used is:

x = :col2[Row()];
If( Row() < N Rows( Current Data Table() ),
 For( i = Row() + 1, i <= N Rows( Current Data Table() ), i++,
  x = x * :col2[i]
 )
);
x;

This same idea can be used if there are multiple places in a data table that need to restart the multiplying.  The identification of when to restart and when to end would just have to be worked out

ARe: how do I multiply each row values backward in one column until a criteria is met

I removed my post since I missed the grouping variable. My approach would not work in this scenario.