BookmarkSubscribe
Choose Language Hide Translation Bar
robertasme
Community Trekker

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

 

0 Kudos
5 REPLIES 5
txnelson
Super User

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
0 Kudos
robertasme
Community Trekker

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 

0 Kudos
txnelson
Super User

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
0 Kudos
robertasme
Community Trekker

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.

0 Kudos
txnelson
Super User

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