It’s World Statistics Day! To honor the theme of the day, the JMP User Community is having conversations about the importance of trust in statistics and data. And we want to hear from you! Tell us the steps you take to ensure that your data is trustworthy.
Choose Language Hide Translation Bar
Highlighted
Level III

## conditional coloring of a column's cells based on standard deviation

I need to color the cells in a column based on whether the value of a certain cell is: within 1 sigma, > 1 sigma but <2 sigma, > 2 sigma but <3 sigma, and >3 sigma, the sigma is the standard deviation of the very column.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Staff

## Re: conditional coloring of a column's cells based on standard deviation

This script is not complete or that efficient, but it gives you an idea.

``````Names Default to Here( 1 );

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

mu = Col Mean( :weight );
sd = Col Std Dev( :weight );

lo = mu - 3*sd;
hi = mu + 3*sd;
rows = dt << Get Rows Where( lo <= :weight <= hi );
:weight << Color Cells( "Red", rows );

lo = mu - 2*sd;
hi = mu + 2*sd;
rows = dt << Get Rows Where( lo <= :weight <= hi );
:weight << Color Cells( "Yellow", rows );

lo = mu - sd;
hi = mu + sd;
rows = dt << Get Rows Where( lo <= :weight <= hi );
:weight << Color Cells( "Green", rows );``````

You could improve the Boolean tests for efficiency if you have a large number of rows.

Learn it once, use it forever!
3 REPLIES 3
Highlighted
Staff

## Re: conditional coloring of a column's cells based on standard deviation

This script is not complete or that efficient, but it gives you an idea.

``````Names Default to Here( 1 );

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

mu = Col Mean( :weight );
sd = Col Std Dev( :weight );

lo = mu - 3*sd;
hi = mu + 3*sd;
rows = dt << Get Rows Where( lo <= :weight <= hi );
:weight << Color Cells( "Red", rows );

lo = mu - 2*sd;
hi = mu + 2*sd;
rows = dt << Get Rows Where( lo <= :weight <= hi );
:weight << Color Cells( "Yellow", rows );

lo = mu - sd;
hi = mu + sd;
rows = dt << Get Rows Where( lo <= :weight <= hi );
:weight << Color Cells( "Green", rows );``````

You could improve the Boolean tests for efficiency if you have a large number of rows.

Learn it once, use it forever!
Highlighted
Level III

## Re: conditional coloring of a column's cells based on standard deviation

Thank you! It works.
Highlighted
Level III

## Re: conditional coloring of a column's cells based on standard deviation

Hi, Mark, I have one more related question from this post. In my dataframe, I have more than 10 columns, editing and pasting the code you provided is painful. I tried to create a for loop myself, but it does not work. Would you please provide some advice on this? Thank you!

col=dt<<get column names(string);
nc=nitems(col);

for(i=2,i<=nc,i++,

mu = Col Mean( :Name(col[i]) );
sd = Col Std Dev( :Name(col[i]) );

lo = mu - 3*sd;
hi = mu + 3*sd;
rows = dt << Get Rows Where( lo <= :Name(col[i]) <= hi );
:Name(col[i]) << Color Cells( "Red", rows );

rows = dt << Get Rows Where( :Name(col[i])< lo | :Name(col[i])> hi );
:Name(col[i]) << Color Cells( "Purple", rows );

lo = mu - 2*sd;
hi = mu + 2*sd;
rows = dt << Get Rows Where( lo <= :Name(col[i]) <= hi );
:Name(col[i]) << Color Cells( "Yellow", rows );

lo = mu - sd;
hi = mu + sd;
rows = dt << Get Rows Where( lo <= :Name(col[i]) <= hi );
:Name(col[i]) << Color Cells( "Green", rows );

);

Article Labels

There are no labels assigned to this post.