Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- Formula to toggle value (1 or 0) in new column based on change by row in existin...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Nov 29, 2018 10:42 AM
(4598 views)

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Nov 29, 2018 11:23 AM
| Last Modified: Nov 29, 2018 11:25 AM
(4590 views)
| Posted in reply to message from WendyLou315 11-29-2018

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!

Dan Obermiller

3 REPLIES 3

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Nov 29, 2018 11:23 AM
| Last Modified: Nov 29, 2018 11:25 AM
(4591 views)
| Posted in reply to message from WendyLou315 11-29-2018

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!

Dan Obermiller

Highlighted
##

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Article Labels

There are no labels assigned to this post.