Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Re: Formula issue

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 29, 2018 12:23 PM
(2854 views)

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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:

Dan Obermiller

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

3 REPLIES 3

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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:

Dan Obermiller

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Formula issue

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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