Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Level I

## How to set a formula on many columns in a elegant way

Hi Guys,

Can someone help me with the below question?

I would like to avoid long formula as below: (each element is a column name!)

Formula(

Std Dev(

:F23 m A,:F23 m C,:F23 m H,:F23 M A,:F23 M C,:F23 M H,:F23 N A,:F23 N C,:F23 N H,

:F30 m A,:F30 m C,:F30 m H,:F30 M A,:F30 M C,:F30 M H,:F30 N A,:F30 N C,:F30 N H,

:F36 m A,:F36 m C,:F36 m H,:F36 M A,:F36 M C,:F36 M H,:F36 N A,:F36 N C,:F36 N H,

:F37 m A,:F37 m C,:F37 m H,:F37 M A,:F37 M C,:F37 M H,:F37 N A,:F37 N C, :F37 N H,

:F49 m A,:F49 m C,:F49 m H,:F49 M A,:F49 M C,:F49 M H,:F49 N A,:F49 N C, :F49 N H,

:F53 m A,:F53 m C,:F53 m H,:F53 M A,:F53 M C,:F53 M H,:F53 N A,:F53 N C, :F53 N H,

:F87 m A,:F87 m C,:F87 m H,:F87 M A,:F87 M C,:F87 M H,:F87 N A,:F87 N C, :F87 N H)));

Cheers,

Dan

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: How to set a formula on many columns in a elegant way

Both above solutions will work, but be aware that for Ron's solution the formula may work only until the global variable colnames is redefined or cleared, e.g. when the JMP session is closed.

Here is some code comparing the two approaches:

dt = Open("\$SAMPLE_DATA/Big Class.jmp");

colnames = dt << get column names;

// Ron's approach: Variables in formula not evalueated

dt << New Column("Std", Formula(Std Dev(colnames[4 :: 5])));

// Ian's approach: Variables evaluated before setting formula

formula = Eval Expr(Std Dev(Expr(colnames[4 :: 5])));

dt << New Column("Std2", Formula(Name Expr(Formula)));

// Compare formulas in the log

Print(:Std << get formula, Std2 << get formula);

//Clear variable definitions and add rows. Only Std2 will recognize changes

Clear Globals();

Current Data Table() << concatenate(

Open("\$SAMPLE_DATA/Students2.jmp"),

append to first table

);

3 REPLIES 3
Highlighted
Staff

## Re: How to set a formula on many columns in a elegant way

This code 'avoids' in the sense that it uses the currently selected columns so you don't have to type. Is that what you are after, please?

Do 'File > New > New Script', cut and paste the JSL, then do 'Edit > Run Script'.

NamesDefaultToHere(1);

// Make a table

nc = 30;

dt = NewTable("Test");

for (c=1, c<=nc, c++,

dt << NewColumn("Col"||Char(c), Numeric, Continuous, Formula(RandomNormal()));

);

// Select half the columns

n = Round(nc/2);

c = RandomIndex(NCol(dt),n);

for (i=1, i<=n, i++,

Column(dt, c[i]) << setSelected(1);

);

// Run this code to make a new formula column for the standard deviatiion of the columns currently selected in a table

selCols = dt << getSelectedColumns;

addCol = Expr(dt << NewColumn("StdDev", Numeric, Continuous, Formula(StdDev(colsTBD))));

Highlighted
Super User

## Re: How to set a formula on many columns in a elegant way

hi dank77​,

Ian@JMP​'s solution is correct. Yet, if you are looking for something less rigorous you may try the following.

assuming the columns you need for the formula are all together in a range you can introduce the number of the first and last as follows:

dt = Open( "\$SAMPLE_DATA/Big Class.jmp" );

colnames = dt << get column names;

col = dt << new column ("Std");

col << Set Formula( std dev (colnames[4::5] ));

best,

ron

Highlighted
Super User

## Re: How to set a formula on many columns in a elegant way

Both above solutions will work, but be aware that for Ron's solution the formula may work only until the global variable colnames is redefined or cleared, e.g. when the JMP session is closed.

Here is some code comparing the two approaches:

dt = Open("\$SAMPLE_DATA/Big Class.jmp");

colnames = dt << get column names;

// Ron's approach: Variables in formula not evalueated

dt << New Column("Std", Formula(Std Dev(colnames[4 :: 5])));

// Ian's approach: Variables evaluated before setting formula

formula = Eval Expr(Std Dev(Expr(colnames[4 :: 5])));

dt << New Column("Std2", Formula(Name Expr(Formula)));

// Compare formulas in the log

Print(:Std << get formula, Std2 << get formula);

//Clear variable definitions and add rows. Only Std2 will recognize changes

Clear Globals();

Current Data Table() << concatenate(

Open("\$SAMPLE_DATA/Students2.jmp"),

append to first table

);

Article Labels

There are no labels assigned to this post.