topic Formula issue in Discussions

Formula issue

powerpuff — Thu, 29 Mar 2018 19:59:51 GMT

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,

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:

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.

Re: Formula issue

Dan_Obermiller — Thu, 29 Mar 2018 19:39:37 GMT

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:

and it gives these results:

Re: Formula issue

cwillden — Thu, 29 Mar 2018 19:45:51 GMT

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
)

Re: Formula issue

powerpuff — Thu, 29 Mar 2018 20:57:19 GMT

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