Articles in this section

Color Cells Based on Values

  • Updated
Follow

The following snippet will shade cells on a table outside a certain range using Table JavaScript via a custom rule. There is example code below for the following scenarios:

Method - Using mean and standard deviation

Method - Using hardcoded range

Method - Using a specific column or NET

 

Technical details

With both versions of the rule, you are able to modify the code to adjust the statistic, colors, and threshold.

 

Method - Using mean and standard deviation

This rule will calculate the means and standard deviation per row and highlight cells according to whether the values are plus or minus 1.5 from the mean multiplied by the standard deviation.

//Edit the statistics and colors below
var statistic = "Column %";
var high_color = "Green";
var low_color = "Red";

form.setSummary("Coloring cells based on row means and standard deviation");

var values = table.get(statistic);
var cell_colors = table.cellColors;

for (var row = 0; row < table.numberRows; row++) {
    //ignore row if a net row
    if (table.netRows.indexOf(row) == -1) {
            var row_values = [];

            // Get all values from this row except for NET column
            for (var col = 0; col < table.numberColumns; col++) {
                if (table.netColumns.indexOf(col) == -1)
                    row_values.push(values[row][col]);
            }

            // Compute upper and lower boundaries from mean and standard deviation
            var row_mean = jStat.mean(row_values);
            var row_sd = jStat.stdev(row_values);
            var high = row_mean + 1.5 * row_sd;
            var low = row_mean - 1.5 * row_sd;

            // Loop over cells in row and color appropriately
            for (var col = 0; col < table.numberColumns; col++) {
                if (table.netColumns.indexOf(col) == -1) {
                    if (values[row][col] >= high)
                        cell_colors[row][col] = high_color;
                    else if (values[row][col] <= low)
                        cell_colors[row][col] = low_color;
                }  
            }
    }
}

table.cellColors = cell_colors;

 

Method - Using hardcoded range

This rule will highlight cells according to whether the Column % values are above or below high and low values specified.

//Edit the statistics, colors, and high and low threshold
var statistic = "Column %";
var high_color = "Green";
var low_color = "Red";
var high = 10; 
var low = 3;

form.setSummary("Coloring cells based on values");

var values = table.get(statistic);
var cell_colors = table.cellColors;

for (var row = 0; row < table.numberRows; row++) {
    //ignore row if a net row
    if (table.netRows.indexOf(row) == -1) {
            // Loop over cells in row and color appropriately
            for (var col = 0; col < table.numberColumns; col++) {
                if (table.netColumns.indexOf(col) == -1) {
                    if (values[row][col] >= high)
                        cell_colors[row][col] = high_color;
                    else if (values[row][col] <= low)
                        cell_colors[row][col] = low_color;
                }  
            }
    }
}

table.cellColors = cell_colors;

 

Method - Using a specific column or NET

This code allows you to highlight cells in each row which are greater or lower than a certain difference from a comparison column. The code uses the primary statistic shown in the table, and only works when the comparison is with one specific column. You can customize the colors, the level of difference required for highlighting (currently 5), and the target column (currently NET) by modifying the top 8 lines.

var high_color = "Green";
var low_color = "Red";

// How much higher or lower should cells be colored?
var difference_level = 5;

// Which column to compare with?
var total_column_label = "NET";
var total_column_index = table.columnIndex(total_column_label);

form.setSummary("Color cells larger or smaller than " + total_column_label + " by " + difference_level + " or more.");
if (total_column_index == null)
    form.ruleNotApplicable("there is no column called " + total_column_label + " in this table");

// Which statistic to use?
// Use the first one selected 
var statistic = table.statistics[0];
var values = table.get(statistic);
var colors = table.cellColors;

for (var row = 0; row < table.numberRows; row++) {
    var row_total = values[row][total_column_index];
    for (var col = 0; col < table.numberColumns; col++) {
        if (row_total - values[row][col] >= difference_level)
            colors[row][col] = high_color;
        else if (values[row][col] - row_total >= difference_level)
            colors[row][col] = low_color;
    }
}

table.cellColors = colors;

 

See also

Related articles

Powered by Zendesk