topic Re: Conditional Formatting Columns in a Table Box in Discussions

Conditional Formatting Columns in a Table Box

Anonymous — Tue, 14 Aug 2018 20:47:41 GMT

Dear community,

I have a data table with dozens of z-scores derived from distribution stats, therefore the scores may vary from -4 to 4 for example. I want to highlight those numbers: less than -2.1 and greater than +2, basically anything score indicating a value is more than 2 standard deviations. (see screen shot attached)

I have looked a number of posts and not found the right approach.

Thank you,

-Lucas

Re: Conditional Formatting Columns in a Table Box

gzmorgan0 — Sat, 11 Aug 2018 06:36:39 GMT

Lucas,

What are you trying to highlight: points in a graph, or highlighted cells in a table, or ?

If you want your graph symbols to have special markers and colors and your table table is unstacked, then it will be difficult, since JMP symbols and colors are by row and not by cell.

However, if the data is stacked, then it is simple.

Conditional formatting rules are for reports. See the text changes for p-values in the test of the mean for weight=55 for Big Class data.

If you want to color the cells of your table and you have numerous columns, it is easier with a script. Please expalin what you are trying to do.

Re: Conditional Formatting Columns in a Table Box

Anonymous — Sat, 11 Aug 2018 17:12:30 GMT

Thank you for the reply.

I am simply trying to highlight those cells with a particular value range. I have tried the gradient option but that colors each cell along a range.

My data exist of z-score that measure how many standard deviations a sample is from a group mean. So a z-score of 4 means that sample variable (elemental concentrations in obsidian parts per million in my work) is four standard deviations from a group mean. Because that sample is an outlier it should be part of another group. My limit is 2 standard deviations to be part of a group. The z-scores I generate by saving the "standardized" to my table after plotting the distributions have negative and positive numbers, thus I want to highlight those cells that are less than -2 and greater than +2. In other words, the only cells that remain unhighlighted are between - 2 and 2.

Sorry for the confusion in my post. The title of my post was overwritten by an auto fill option somehow. It should have read "applying conditional formatting in a cell."

Thank you,
-Lucas

Re: Conditional Formatting Columns in a Table Box

Anonymous — Sat, 11 Aug 2018 17:26:49 GMT

Thank you for the reply.

I am simply trying to highlight those cells with a particular value range. Like what the last image you posted. I have tried the gradient option but that colors each cell a long a range. I am new to JMP so scripting might be a small challenge for me, but I want to learn.

My data exist of z - score that measure h ow many standard deviations a sample is from a group mean . So a z-score o f 4 means that sample variable (elemental concentrations i n obsidian parts per mill ion i n my work) is four standard deviations from a group mean . Because that sample is an outlier it should be part of another group . My limit is 2 standard deviations to be part of a group. The z-scores I generate by saving the " standardized " to my table after plotting the distributions have negative and positive numbers, thus I want to highlight t hose cells that are less than -2 and greater than +2 . In other words, the only cells that remain unhighlighted are between - 2 and 2.

Sorry for the confusion in my post. The title of my post was overwritten by an auto fill option somehow. It should have read "applying conditional formatting in a cell."

Thank you,
- Lucas

Re: Conditional Formatting Columns in a Table Box

gzmorgan0 — Sat, 11 Aug 2018 18:35:16 GMT

Lucas,

See the attached table. You can do this via the GUI by following these steps for each zscore column:

Select the target column and make sure no other columns are selected
Main Menu > Rows > Row Selection > Select Where [same as Ctrl+Shift +W]
Create the criteria: this will be an Or condition [where any condition is met] and press OK. See picture

Conditional Row Selection - Or condition

Right click ( be careful) and select Color Cells and choose your color.

Otherwise, run a script like this.

Names Default to Here(1);

dt = current data table();

cdlg = Column Dialog(
    Text Box("Select all columns to be higlighted"),
	exY = ColList( "Y", Min Col( 1 ), Data Type( "Numeric" ) ),
);

If (cdlg["Button"] !=1, Throw(),
   cnames = cdlg["exY"]
 );

For(i =1, i<=nitems(cnames), i++,
  ccol = column(dt, cnames[i]);
  _idx = dt << get rows where(ccol[]>2 | ccol[] < -2);  //set your criteria	
  ccol << color cells(RGB Color([255,128,0]),_idx) 	    //use "yellow" a color name or RGB number
);

Note, I kept this simple, since I do not know your scripting skills: the color and the criteria is hardcoded and added a simple column selector.

Open the attached table and open the attached script and run the script by pressing the the Run Script icon or Ctrl+R or right click in teh script window and select Run Script.

Re: Conditional Formatting Columns in a Table Box

Anonymous — Tue, 14 Aug 2018 19:58:06 GMT

Thank you (sorry for my delay).

Where do I right click to select the color?

-Lucas

Re: Conditional Formatting Columns in a Table Box

Anonymous — Tue, 14 Aug 2018 20:02:14 GMT

Hi. Okay. I understand where I right click. Ignore the last post.

I would like to highlight just the cells in multiple columns that meet my conditions. I do not want to highlight the row. I have more than 5 z-scores.

Re: Conditional Formatting Columns in a Table Box

Anonymous — Tue, 14 Aug 2018 21:19:38 GMT

Hello again. Sorry for all the reply postings. I used the code (my first time) and it worked like a charm! Thank you. I will need to research this code so I understand what it is doing.

Thank you!