- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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,
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.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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:
and it gives these results:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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:
and it gives these results:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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..
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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
)