Calculations between rows based on conditions

[#1]

Hey, I'm new to JMP scripting, but somewhat used to python language. I'm trying to achieve the following with my dummy test table below:

1) Cycle through all rows (both before and after the row) and do calculations only on those rows where Cuvette == 11, remaining rows where Cuvette != 11 the "Output" can be set to NA or whatever. 

2) The calcuation itself is between two separate rows. In rows with Cuvette == 11, the 850 nm cell of the row has to be divided with the 250 nm cell from the row that meets two conditions:
                   a) Cuvette == 12
                   b) The Pass value has to match up between the rows, e.g. Pass value for cuvette 11 == Pass value for Cuvette 12    

Pass	850 nm	250 nm	Cuvette	Output
1	100	200	11	100/500 = 0,2
2	400	500	11	400/400 = 1
3	300	100	11	300 / 900 = 0.33
1	700	500	12	NA
2	200	400	12	NA
3	400	900	12	NA
1	400	800	13	NA
2	500	700	13	NA
3	300	700	13	NA

[#2]

To do this in JMP like fashion, you generally would split your data and then perform the calculation (JMP is Not a Spreadsheet), then join it back or continue with the split data.

But you can use also JSL or Formula to do this calculation, but first consider the splitting option.

[#3]

Here is one matrix option, but using JMP features is generally easier to understand

Names Default To Here(1);

dt = Current Data Table();

cuvette_start = 11;
cuvette_end = 12;

cuvettes = dt[0, "Cuvette"];

// Data is sorted -> we can just take values and make comparison
start_idx = Loc(cuvettes, cuvette_start);
end_idx = Loc(cuvettes, cuvette_end);

// differences
output = J(1, N Rows(dt), .);
output[start_idx] = dt[start_idx, "850 nm"]  dt[end_idx, "250 nm"];
dt << new column("R", Numeric, Continuous, Values(output));

If you can rely on the order heavily, you can use formula like this

If(:Cuvette == 11,
	:"850 nm"n / :"250 nm"n[Col Min(If(:Cuvette == 12, Row(), .)) + Row() - 1];
);

The type of final formula will heavily depend on your real use case (is data sorted "properly", are you looking for more than one value comparison and so on).

[#4]

Welcome to the Community.

Here is an example of how I would approach your problem

Names Default To Here( 1 );
dt = Current Data Table();

// Separate out the Cuvette==12 rows into a separate data table
// and then join the Pass==Pass rows to the Cuvette==11 rows
// Then calculate the Output column

// Select the Cuvette==12 rows
dt << Select Where( :Cuvette == 12 );

// Create a new subsetted data table with those rows
dt12 = dt << Subset( Selected Rows( 1 ), columns( :Pass, :"250 nm"n ) );

// Change 250 nm column name so when joined back to original table
// it will have a unique column name
dt12:"250 nm"n << Set Name( "Divisor" );

// For alignment reasons, add a new column called Cuvette and set
// it equal to 11
dt12 << New Column( "Cuvette", Set Each Value( 11 ) );

// Join the tables using the Update Platform
dt << Update(
	With( dt12 ),
	Match Columns( :Pass = :Pass, :Cuvette = :Cuvette )
);

// Create the new Output column
dt << New Column( "Output", Set Each Value( :"850 nm"n / :Divisor ) );

// Remove Divisor column since it is no longer needed
dt << delete columns( Divisor );

// Close the subsetted data table since it is no longer needed
Close( dt12, nosave );

// Remove the Selected RowStates from the data table
dt << clear rowstates;

 Below is a more direct approach, but a far more inefficient method, which yields the same results.  On a large data table this method will be quite slow

Names Default To Here( 1 );
dt = Current Data Table();

dt << New Column( "Output",
	Formula(
		foundRow = [];
		If( :Cuvette == 11,
			curPass = :Pass;
			foundRow = Current Data Table() <<
			get rows where( :Cuvette == 12 & :Pass == curPass );
			If( N Items( foundRow ) > 0,
				:"850 nm"n / :"250 nm"n[foundRow[1]]
			);
		);
	)
);