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

- JMP User Community
- :
- Discussions
- :
- How to use the formula row by row to find the dynamic change of the total number...

- 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

Feb 10, 2020 1:02 AM
(539 views)

How to use the formula row by row to find the dynamic change of the total number of columns.

Such as setting:

The number a is the number of starting column positions that need to be summed

The number b is the total number of columns that need to be summed

```
dt = Open( "$SAMPLE_DATA/Diabetes.jmp" );
dt << New Column( "test", formula( As Column( 6 ) + As Column( 7 ) + As Column( 8 ) + As Column( 9 ) ) );
dt << run formulas;
Column( "test" ) << deleteFormula;
```

Such as:

A = 6;

B = 4;

Find the sum of columns 6, 7, 8, and 9 in the new "text" column (starting with column a, total column b).

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

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

This situation is weird. This script builds the formula:

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Diabetes.jmp" );
col = dt << New Column( "test" );
lo = 6;
hi = 9;
f = Expr( Add() );
For( c = lo, c <= hi, c++,
r = (dt << Get Column Reference( Eval List( { Column( dt, c ) << Get Name } ) ))[1];
Insert Into( f, r );
);
Eval(
Substitute(
Expr( col << Set Formula( fff ) ),
Expr( fff ),
Name Expr( f )
)
);
```

But it does not evaluate. Yet it works if I examine the preview in the formula editor.

Anyway, why use a formula when you don't want to?

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Diabetes.jmp" );
col = dt << New Column( "test" );
lo = 6;
hi = 9;
sum = J( N Row( dt ), 1, 0 );
For( c = lo, c <= hi, c++,
sum += Column( dt, c ) << Get As Matrix;
);
col << Set Values( sum );
```

Learn it once, use it forever!

3 REPLIES 3

Highlighted
##

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

Re: How to use the formula row by row to find the dynamic change of the total number of columns.

Created:
Feb 10, 2020 1:05 AM
| Last Modified: Feb 10, 2020 1:08 AM
(538 views)
| Posted in reply to message from lwx228 02-10-2020

I tried to write it this way, but it didn't work.

```
a=6;b=4;
dt = Current Data Table();
dt<<New Column("text",formula(
g=0;
for(i=6,i<=a+b-1,i++,
g=g+As Column(i+1)
)));dt<<run formulas;
Column("text")<<deleteFormula;Wait(0);
```

Looks like it's going to be in the brute force form of a line by line loop, right?

Highlighted

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

This situation is weird. This script builds the formula:

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Diabetes.jmp" );
col = dt << New Column( "test" );
lo = 6;
hi = 9;
f = Expr( Add() );
For( c = lo, c <= hi, c++,
r = (dt << Get Column Reference( Eval List( { Column( dt, c ) << Get Name } ) ))[1];
Insert Into( f, r );
);
Eval(
Substitute(
Expr( col << Set Formula( fff ) ),
Expr( fff ),
Name Expr( f )
)
);
```

But it does not evaluate. Yet it works if I examine the preview in the formula editor.

Anyway, why use a formula when you don't want to?

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Diabetes.jmp" );
col = dt << New Column( "test" );
lo = 6;
hi = 9;
sum = J( N Row( dt ), 1, 0 );
For( c = lo, c <= hi, c++,
sum += Column( dt, c ) << Get As Matrix;
);
col << Set Values( sum );
```

Learn it once, use it forever!

Highlighted
##

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

Re: How to use the formula row by row to find the dynamic change of the total number of columns.

Thank markbailey!

https://community.jmp.com/t5/Discussions/How-to-SUM-rows-with-variable-column-names/m-p/9817

Seeing this post, I also tried this method, but to determine the number of text format columns.

```
dt = Open( "$SAMPLE_DATA/Diabetes.jmp" );
a = 4;//has 2 text format columns.
b = 4;
m = (dt << get as matrix)[0, Index( a, a + b - 1 )];
s = (V Sum( m` ))`;
dt << New Column( "test", Numeric, Continuous, Values( s ) );
```

Article Labels

There are no labels assigned to this post.