BookmarkSubscribeRSS Feed
robertasme

New Contributor

Joined:

Jun 2, 2018

Conditional formatting in a data table

Hello,

 

I'm trying to apply conditional formula for columns in order to apply cell color but I can't achieve successful result.

 Any suggestions how to do it? I believe I am using wrong formula here.

 

Example:

 

Columns

UV            UV to reject

0.15              0.15

 

Formula: If( :UV == :UV to reject,
 Color Of( "Red" ),
 "White"
)

 

Idea is that JMP highlights the cell in color red if the value is equal to a value in another column

 

Thanks in advance

 

5 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

Re: Conditional formatting in a data table

The formula processor complained when I attempted to set the cell color from a column formula, however, below is a simple script that works.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << New Column( "Reject", character );

For Each Row(
	If( :weight > 120,
		:reject << color cells( "red", Row() ),
		:reject << color cells( "white", Row() )
	)
);

color.PNG

Jim
robertasme

New Contributor

Joined:

Jun 2, 2018

Re: Conditional formatting in a data table

It does seem to work, but in my case I have 3 columns of very similar e.i. weeight data. I want to highlight one out of three columns that contains value >20% from the mean 

txnelson

Super User

Joined:

Jun 22, 2012

Re: Conditional formatting in a data table

1. What version of JMP are you running?

2. What do you mean that it doesn't seem to work?  It runs without error in JMP 12, 13 and 14.

Jim
robertasme

New Contributor

Joined:

Jun 2, 2018

Re: Conditional formatting in a data table

I'm running JMP 14

 

The code you is working don't get me wrong. The thing is that I am looking for a solution that would allow me to distinguish which column out of 3 data columns is the furthest from the mean ( visually)

 

I am really sorry if I am being ambigues.

txnelson

Super User

Joined:

Jun 22, 2012

Re: Conditional formatting in a data table

I am still confused as to what "Mean" you are referring to?  But, the code below takes a guess at one of the possible means to compare with, and from it sets the cell with the max difference from the mean.

Names Default To Here( 1 );
// Open the sample data table
dt = Open( "$SAMPLE_DATA/blood pressure.jmp" );

// Loop across rows to find what cells have the max values
For Each Row(
	// Find the mean for the target columns in the row
	rowMean = Mean( :BP 8M, :BP 12M, :BP 6M, :BP 8W, :BP 12W, :BP 6W, :BP 8F, :BP 12F, :BP 6F );
	// Initialize the memory variables to hold the max value and the column the max value is in
	MaxVal = 0;
	Col = 0;
	// Loop across the columns in question and find the max value
	For( i = 1, i <= 9, i++,
		If( Abs( Column( i + 2 )[Row()] - rowMean ) > MaxVal,
			MaxVal = Abs( Column( i + 2 )[Row()] - rowMean );
			Col = i + 2;
		)
	);
	// Set the cell color for the cell that has the max value
	Column( col ) << color cells( "red", Row() );
);

cell colors.PNG

Jim