topic Re: Formula to toggle value (1 or 0) in new column based on change by row in existing column in Discussions

Formula to toggle value (1 or 0) in new column based on change by row in existing column

WendyLou315 — Thu, 29 Nov 2018 18:42:31 GMT

I have a question on creating a formula to do something in JSL that I do very easily in Excel.

If I were to have Column A and I want to generate Column B, I would put a zero in B1 then add the formula:

=IF(A2<>A1,IF(B1=0,1,0),B1)

into B2 then copy down the column. Is there a formula to do that in JMP? I have tried Lag() but it doesn't give me the results I'm looking for.

Re: Formula to toggle value (1 or 0) in new column based on change by row in existing column

Mark_Bailey — Thu, 29 Nov 2018 19:05:23 GMT

Try this formula:

Re: Formula to toggle value (1 or 0) in new column based on change by row in existing column

Dan_Obermiller — Thu, 29 Nov 2018 19:25:17 GMT

The formula is exactly the same as what you would put into Excel. The differences are that you need specify the first row in the formula and you need to know exactly what the Excel autofill is doing (which is not always straight-forward).

Interactively, it would look like this:

The JSL code would look like this:

If(
	Row() == 1, 0,
	:A[Row()] != Lag( :A, 1 ), If( Lag( :B, 1 ) == 0, 1, 0 ),
	Lag( :B, 1 )
)

Or just use Mark's formula which looks simpler!

Re: Formula to toggle value (1 or 0) in new column based on change by row in existing column

WendyLou315 — Tue, 04 Dec 2018 19:45:09 GMT

@Dan_Obermiller, You are correct, @Mark_Bailey had a simpler option but both work perfectly! THANKS so much!!!