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 sigma, and >3 sigma, the sigma is the standard deviation of the very column.
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.
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 ););
sigma, and >3 sigma, the sigma is the standard deviation of the very column.
