Choose Language Hide Translation Bar
Highlighted

## Re: Color Cells based on formula result

You can do the calculations with colmin, colmax and colmean:

``````frr_min = col min(:FRR);
frr_max = col max(:FRR);
frr_avg = col mean(:FRR);

// Color FRR with a gradient
column(hlt_dt, "FRR") << Set Property( "Color Gradient", {"Light Spectral", Range(expr(frr_min), expr(frr_max), expr(frr_avg))});
column(hlt_dt, "FRR") << color cell by value(1);``````
Highlighted

## Re: Color Cells based on formula result

Sorry. This solution doesn't allow me to color values out of a range of interest with one color (say, red), and within the range of interest with a different color (say, white). This col min, col mean, col max solution seems to be a way to simply put some defaults in to do a gradient. What was attractive about @vince_faller's solution is that it creates a custom Gradient that forces exactly two colors (I think) onto the column. What's not attractive to me yet is that I don't understand how to precisely control when I switch from my "good" color to my "bad" color.

Highlighted

## Re: Color Cells based on formula result

So basically I'm just faking a color gradient that gradients over a tiny window.  So for only two colors you only need 4 points. 0, some number very close to 0, some number very close to 1, and 1.  If you wanted more than 2 colors you'd have to calculate values from 0 to 1 at each point in your range you wanted.  for instance if you wanted 110, it you'd have to add two values at 10/35 and 10/35+.0000001

``````Names Default to Here(1);
dt = Open("\$SAMPLE_DATA\Big Class.jmp");
Column(dt, "weight") << Set Property(
{{"Something", 16387,
{{255, 0, 0}, {0, 255, 0}, {0, 255,	0}, {255, 0, 0}}, //list of colors
{0, .00000001, .9999999, 1}}, //corresponding locations from 0 to 1
Range( {100, 135, 117.5} ) //minimum, maximum and center.  0, 1, and .5 is another way to look at it

}
);
Column(dt, "weight") << Color Cell By Value(1);``````

Vince Faller - Predictum
Highlighted

## Re: Color Cells based on formula result

This is clumsy, and there should be a more direct/obvious way to do it in JMP, but this solution can work. Thank you.

Highlighted

## Re: Color Cells based on formula result

Absolutely agree.  You should definitely put in a feature request for that at support@jmp.com.

Vince Faller - Predictum
Highlighted

## Re: Color Cells based on formula result

Maybe not obvious, but you may find this approach less "clumsy":

``````Names Default To Here(1);
dt = Open("\$SAMPLE_DATA\Big Class.jmp");
dt << New Column("highlight cells",
Formula(If(100 < :weight < 135, :weight << color cells("Red", Row()); ., .)),
Hide(1)
);``````
Highlighted

## Re: Color Cells based on formula result

This approach works for me unless you change the weights. I thought I could simply add a request to set the else parameter in the If() statement to Color Cells("White",Row()) but that doesn't seem to work.

Also it requires a new column for each parameter that has spec limits, although I suppose we could write a creative formula that iterates through all of the columns, checks for a Spec Limits column property, and if it finds one goes and colors the appropriate columns? At least the extra column(s) are hidden.

Highlighted

## Re: Color Cells based on formula result

Here's the same approach with a white-coloring Else clause. The hidden column is here numeric by default so both the Then and the Else must return something numeric, e.g. a missing value.

``````dt = Open("\$SAMPLE_DATA\Big Class.jmp");
dt << New Column("highlight cells",
Formula(If(100 < :weight < 135, :weight << color cells("Red", Row()); .,
:weight << color cells("White", Row()); .)),
Hide(1)
);``````

Article Labels

There are no labels assigned to this post.