Color Cells Based on Row Means 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.

To use this snippet:

1. Select your table.
2. Select Automate > Custom Rule.
3. Paste in the code from below.
4. Click the 'play' icon and close.

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++) {
    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;

See also

• Table JavaScript and Plot JavaScript for an explanation of how to run this code.
• Table JavaScript and Plot JavaScript Reference for technical information.
• Table JavaScript and Plot JavaScript Examples Library for other examples.
• JavaScript for information about the JavaScript programming language.
• QScript for tools for automating projects using JavaScript.
• JavaScript Variables for detail on how to create new variables in the Variables and Questions tab using JavaScript.