Choose Language Hide Translation Bar
Highlighted
powerpuff
Community Trekker

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.

0 Kudos
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

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

Highlighted
cwillden
Super User

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
Highlighted

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

powerpuff
Community Trekker

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

0 Kudos
Highlighted
cwillden
Super User

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