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

- 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

Oct 9, 2019 11:55 AM
(1306 views)

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

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

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

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

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
##

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

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

Thank you! It works.

Highlighted
##

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

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.