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

- JMP User Community
- :
- Discussions
- :
- if "compare" column value is greater than 100%, then change the value to 100%, i...

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

Highlighted
##
if "compare" column value is greater than 100%, then change the value to 100%, if less than or equal to 100%, then keep the value no change

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

Created:
May 28, 2020 10:12 PM
| Last Modified: May 29, 2020 4:57 AM
(455 views)

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
New Column( "Compare", Numeric, "Continuous", Format( "Percent", 12, 0 ), Formula( :weight / :height ) );
//change the value on position of :weight[2] to 59. But seems not work. could you help edit the script on this place?
dt:weight[2] << Set Values( 58 );
// I hope realize if the column "compare" result >100%, then output 100%, if <100% then keep the value no change. then how to edit script below? If( :Compare>100%,100%, no change);
```

- Tags:
- p

2 REPLIES 2

Highlighted
##
Re: if "compare" column value>100%, then change the value to 100%, if <=100%, then keep the value no change

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

Created:
May 28, 2020 10:27 PM
| Last Modified: May 29, 2020 1:52 AM
(450 views)
| Posted in reply to message from Theresa 05-29-2020

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
New Column( "Compare",
Numeric,
"Continuous",
Format( "Percent", 12, 0 ),
Formula( :weight / :height )
);
// Changing the 2nd rows value for Weight to 59 will work with
// a simple assignment statement
dt:weight[2] = 59;
```

```
// One can not change a cell in a formula column,
// unless you change the complete formula
dt:compare << set formula(
:weight / :height;
if( :weight / :height > 1, 1)
);
```

Probably the method that I use most often, is to Delete the formula from a column that was created using a formula. Deleting the formula, converts the column into a normal static column, keeping all of the values. You can then just make the changes you need, using standard assignment statements.

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
New Column( "ratio",
Numeric,
"Continuous",
Format( "Percent", 12, 0 ),
Formula( :weight / :height )
);
dt << run formulas;
dt:ratio << delete formula;
For Each Row( If( :ratio > 1, :ratio = 1 ) )
;
```

Jim

Highlighted
##
Re: if "compare" column value>100%, then change the value to 100%, if <=100%, then keep the value no change

for the first method: if( :weight / :height > 1, 1); ------This is update all the values which exceed 100% successful. But change the number less than 100% to "." which should keep no change. don't know how to improve this script.

For the second method:For Each Row( If( :ratio > 1, :ratio = 1 ) ); works well. thank you so much.

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

for the first method: if( :weight / :height > 1, 1); ------This is update all the values which exceed 100% successful. But change the number less than 100% to "." which should keep no change. don't know how to improve this script.

For the second method:For Each Row( If( :ratio > 1, :ratio = 1 ) ); works well. thank you so much.