topic Re: Conditional formatting in a data table in Discussions

Conditional formatting in a data table

robertasme — Sat, 02 Jun 2018 11:25:19 GMT

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

Re: Conditional formatting in a data table

txnelson — Sat, 02 Jun 2018 14:56:35 GMT

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() )
	)
);

Re: Conditional formatting in a data table

robertasme — Sat, 02 Jun 2018 18:08:36 GMT

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

Re: Conditional formatting in a data table

txnelson — Sat, 02 Jun 2018 19:57:06 GMT

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.

Re: Conditional formatting in a data table

robertasme — Sun, 03 Jun 2018 20:00:54 GMT

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.

Re: Conditional formatting in a data table

txnelson — Sun, 03 Jun 2018 21:38:41 GMT

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() );
);