turn on suggestions

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

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- How Do I Transfer Excel COUNTIF and MATCH functions into JMP

Topic Options

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

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

Dec 14, 2016 10:49 AM
(4451 views)

I am having an issue in scripting the JSL code for two functions (see screen shot) which I currently have in Excel but want to transfer over into JMP. The data is found in the attached file **JMP_Test_Average2.xlsx** and the functions I wish to transfer to JMP are as follows:

*column VS*has a**COUNTIF**function; =COUNTIF(C3:N3,"-1")- I want to know the number of instances -1 appears in each row (e.g. in row 3 the number of instances of -1 are 2).
*Column m(AB)*is ignored in the function but it is impoirtant as it is part of the data sequence.

- I want to know the number of instances -1 appears in each row (e.g. in row 3 the number of instances of -1 are 2).
*column PD*has a**MATCH**function; =(MATCH("-1",C3:N3,0))+1- I want to know where the first instance of -1 appears in each row (e.g. in row 3 the first -1 appears in
*column m(LM)*or column 12 when considering*columns m(AB)*to*m(MN)*).*Column m(AB)*is ignored in the functions but it is impoirtant as it is part of the data sequence.

I would like to know how to script these functions in JSL so I can update my JMP table and not have to complete the calculations in Excel and then bring the results over into my JMP table.

Any help would be much appreciated.

Thanks.

Mike

1 ACCEPTED SOLUTION

Accepted Solutions

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

This should give you a basic Idea.

```
Names Default to here(1);
dt = new table("Test", add rows(10),
New Column("AB", formula(if(random uniform()<=.8, 1, -1))),
New Column("BC", formula(if(random uniform()<=.8, 1, -1))),
New Column("CD", formula(if(random uniform()<=.8, 1, -1))),
New Column("DE", formula(if(random uniform()<=.8, 1, -1))),
New Column("EF", formula(if(random uniform()<=.8, 1, -1))),
);
cols = dt << Get Column Reference(dt << Get Column Names);
dt << new Column("VS", formula(nrows(Loc(Evallist({:AB, :BC, :CD, :DE, :EF}), -1))));
dt << New Column("PD", formula(Contains(Evallist({:AB, :BC, :CD, :DE, :EF}), -1)));
```

10 REPLIES

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

This should give you a basic Idea.

```
Names Default to here(1);
dt = new table("Test", add rows(10),
New Column("AB", formula(if(random uniform()<=.8, 1, -1))),
New Column("BC", formula(if(random uniform()<=.8, 1, -1))),
New Column("CD", formula(if(random uniform()<=.8, 1, -1))),
New Column("DE", formula(if(random uniform()<=.8, 1, -1))),
New Column("EF", formula(if(random uniform()<=.8, 1, -1))),
);
cols = dt << Get Column Reference(dt << Get Column Names);
dt << new Column("VS", formula(nrows(Loc(Evallist({:AB, :BC, :CD, :DE, :EF}), -1))));
dt << New Column("PD", formula(Contains(Evallist({:AB, :BC, :CD, :DE, :EF}), -1)));
```

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

Dec 15, 2016 8:34 AM
(4404 views)
| Posted in reply to message from vince_faller 12/14/2016 02:24 PM

Thanks for your help, much appreciated.

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

Here I find the answer and solve my own problems.Thanks!

Contains( Eval List( :BC[Index(Row()-3,Row())] ), -1 )

Contains( Eval List( :BC[Index(Row()-3,Row())] ), -1 )

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

Dec 16, 2018 11:42 PM
(382 views)
| Posted in reply to message from vince_faller 12/14/2016 02:24 PM

howdy ! I'm dying to know:

If column "AB" has more than one "-1", how do figure out the number of rows that are within the range of the last "-1" in this range?

Thanks!

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

I tried to write code like this, but it didn't work.

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

This might be the formula you are looking for

```
x = 5 - Contains( Eval List( :ab[Index( Row(), Row() - 3 )] ), -1 );
If( x == 5, x = 0 );
```

Jim

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

Thank Jim!

x=5-Contains(Eval List(:ab[Index(Row(),Row()-3)]),-1);

If(x==5,0,x);

x=5-Contains(Eval List(:ab[Index(Row(),Row()-3)]),-1);

If(x==5,0,x);

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

```
//this gives you a vector of items that are -1
vector_of_neg1 = loc(Aslist(:AB[row()-3::row()]`), -1);
//then you just have to select the last one
n = nitems(vector_of_neg1);
if(n,
vector_of_neg1[n]
, //else 0
0
);
```

Or you can use the start argument to look from the back.

`Contains( :AB[Index( Row() - 3, Row() )] , -1, -1 )`

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

Here are the 2 formulae that I came up with to replicate your spreadsheet

For YS

```
Sum(
:Name( "m(AB)" ) == -1,
:Name( "m(BC)" ) == -1,
:Name( "m(CD)" ) == -1,
:Name( "m(EF)" ) == -1,
:Name( "m(FG)" ) == -1,
:Name( "m(GH)" ) == -1,
:Name( "m(HI)" ) == -1,
:Name( "m(IJ)" ) == -1,
:Name( "m(JK)" ) == -1,
:Name( "m(KL)" ) == -1,
:Name( "m(LM)" ) == -1,
:Name( "m(MN)" ) == -1
)
```

For PD

```
Contains(
Char( :Name( "m(AB)" ) ) || Char( :Name( "m(BC)" ) ) ||
Char( :Name( "m(CD)" ) ) || Char( :Name( "m(EF)" ) ) ||
Char( :Name( "m(FG)" ) ) || Char( :Name( "m(GH)" ) ) ||
Char( :Name( "m(HI)" ) ) || Char( :Name( "m(IJ)" ) ) ||
Char( :Name( "m(JK)" ) ) || Char( :Name( "m(KL)" ) ) ||
Char( :Name( "m(LM)" ) ) || Char( :Name( "m(MN)" ) ),
"-1"
)
```

Jim