cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
powerpuff
Level IV

Formula issue

I have to manipulate data using various formulas and store the output in one final column. I am able to apply the formula successfully and store data in a new column but not in the same column. 

For example,

In this table,

example.PNG

I am using Formula(abs(Divide(Effect,Subtract( USL,Mean)))*100)) for cells where USL values are present and 

Formula(abs(Divide(Effect,Subtract( Mean,LSL)))*100)) where LSL values are present. But for cells where the column name, LSL, and USL are empty, I want to use the formula: Formula(Divide(abs(Effect)), Mean);

The script I am currently working on is:

 

 

dtq = Open("path\formula.jmp");
a = dtq << New Column("Final", Formula(abs(Divide(Effect,Subtract( USL,Mean)))*100));
//);
//:Final << Formula(abs(Divide(Effect,Subtract( USL,Mean)))*100);
b = dtq << New Column("Final1",Formula(abs(Divide(Effect,Subtract( Mean,LSL)))*100));
If( Is Empty( dtq << ColumnName ),
:Final << Formula(Divide(abs(Effect)), Mean);
);
show(b);

 

The output I get now is:

example.PNG

whereas I want the values of Final1 to be in the same column. Also, 

this part of the script:

If( Is Empty( dtq << ColumnName ),
:Final << Formula(Divide(abs(Effect)), Mean);
);

does not show any error but is not working as well. Thank you for any help.

2 ACCEPTED SOLUTIONS

Accepted Solutions

Re: Formula issue

This seems a little overly complex to me. All of the calculations can be put into a single formula column:

 

If(
Is Missing( :LSL ) & Is Missing( :USL ), Abs( :Effect / :Mean ),
Is Missing( :LSL ), Abs( :Effect / (:USL - :Mean) ) * 100,
Is Missing( :USL ), Abs( :Effect / (:Mean - :LSL) ) * 100
)

 

It does not need to be scripted. Interactively it looks like this:

Capture2.JPG

 

and it gives these results:

Capture1.JPG

Dan Obermiller

View solution in original post

cwillden
Super User (Alumni)

Re: Formula issue

Assuming a row will never have both :LSL and :USL as non-missing, this will handle all the cases you described:

If(
	!Is Missing( :LSL ), Abs( :Effect / (:Mean - :LSL) ) * 100,
	!Is Missing( :USL ), Abs( :Effect / (:USL - :Mean) ) * 100,
	Is Missing( :ColumnName ), Abs( :Effect ) / :Mean
)
-- Cameron Willden

View solution in original post

3 REPLIES 3

Re: Formula issue

This seems a little overly complex to me. All of the calculations can be put into a single formula column:

 

If(
Is Missing( :LSL ) & Is Missing( :USL ), Abs( :Effect / :Mean ),
Is Missing( :LSL ), Abs( :Effect / (:USL - :Mean) ) * 100,
Is Missing( :USL ), Abs( :Effect / (:Mean - :LSL) ) * 100
)

 

It does not need to be scripted. Interactively it looks like this:

Capture2.JPG

 

and it gives these results:

Capture1.JPG

Dan Obermiller
powerpuff
Level IV

Re: Formula issue

@Dan_Obermiller Thank you for the solution. I have far too many tables that I want the same formula to run on, so it would be better if a script would do it. Thanks a lot for the quick solution though..

cwillden
Super User (Alumni)

Re: Formula issue

Assuming a row will never have both :LSL and :USL as non-missing, this will handle all the cases you described:

If(
	!Is Missing( :LSL ), Abs( :Effect / (:Mean - :LSL) ) * 100,
	!Is Missing( :USL ), Abs( :Effect / (:USL - :Mean) ) * 100,
	Is Missing( :ColumnName ), Abs( :Effect ) / :Mean
)
-- Cameron Willden