Subscribe Bookmark RSS Feed

How Do I Transfer Excel COUNTIF and MATCH functions into JMP

sellerm

Community Trekker

Joined:

Jan 13, 2016

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.
  • 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

 

Data as found in file JMP_Test_Average2.xlsxData as found in file JMP_Test_Average2.xlsx

 

1 ACCEPTED SOLUTION

Accepted Solutions
vince_faller

Super User

Joined:

Mar 17, 2015

Solution

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)));
4 REPLIES
vince_faller

Super User

Joined:

Mar 17, 2015

Solution

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)));
sellerm

Community Trekker

Joined:

Jan 13, 2016

Thanks for your help, much appreciated.

txnelson

Super User

Joined:

Jun 22, 2012

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
sellerm

Community Trekker

Joined:

Jan 13, 2016

Thanks for your help as well Jim, you and Vince solved my issue.