Computed cell formats

Overview

You can write and use an Analytica expression to compute cell-level formats for a table (usually a result table), thus enabling the appearance of the table to adapt to data and results in the model. Fill color, font style and color, text alignment, border style, number format, and cell bars can all be computed, with virtually unlimited possibilities for the sophistication of logic.

Some examples

Several examples of computed formats are show here is illustrate the potential application of computed formats.

A heat map can be used to emphasizes the numeric quantity, where green vs. red is used to reflect positive versus negative, and transparency/opaqueness used to emphasize magnitude of the numbers in each cell.

Emphasizing numeric magnitude using varying font size.

And yet another method, depicting quantity using cell bars.

ComputedCellFormat alternatingRowColor.png

Alternating row colors.

Fill color and outer border emphasize cells containing values with a certain property, in this case numbers that are divisible by 3.

The indentation for the elements of an index are computed based on information in the model (in this case, whether the element is a state, country or city). This can be easier that manually indenting the items from the dialog, and can automatically adapt to new locales that appear later.

Cell Format Expressions

To use an expression to computed the cell formats, enter the expression into the Cell Format Expression attribute. To make this attribute visible in the Object window and Attribute pane, go to the Attributes dialog (on the Object menu) and turn on Cell Format Expression. Then edit the expression for your table from its Object window or from the Attribute pane.

By default, your computed format will apply to the result table of the variable with the format expression, but not to the edit table. Your computed formats take precedence over any manually selected formats (set from the Cell format dialog).

Cell format expressions are just normal Analytica expressions, but with calls to special functions called cell format predicates: CellAlignment, CellBars, CellBorder, CellFill, CellFont, CellNumberFormat, and CellOnClick. The CellFormats function is used to combine multiple predicates. The CellSpan function is used to apply formats to header cells and totals. The CellComparisonFormat is used to apply formats to external comparison variable columns.

Simplest example

The simplest example of a computed format would be an expression that contains a single predicate:

CellFill('Green', 0.1)

The 0.1 is the alpha channel (on a scale from 0.0 to 1.0), and serves here to lighten the green fill color. With this Cell format expression, every body cell contains a dim green fill.

CellFormatExpression SimplestExample.png

Highlighting largest row and column

Next, we use the CellFill('Green', 0.1) inside some logic. In this case, we'll highlight the cells in the row and column with the largest sum. The CellFormatExpression is now

Var si := Sum(Self,I);
Var sj := Sum(Self,J);
If si=Max(si,J)  or  sj=Max(sj,I) Then CellFill('Green', 0.1)

Notice that the expression references Self.

Highlight tails

In this example, we put the top 5 values along J in bold, and the bottom 5 values along J in italics.

Var r := Rank(Self,J);
If r<=2 Then CellFont(italics:true)
Else If r+2>IndexLength(J) Then CellFont(bold:true)

Combining multiple predicates

This example uses CellFormats( ) to combine two predicates, so that two properties are changed together (the font and the border). The example hides cells with zeros in a sparse matrix by setting the font color to white and the border type for all inner sides to 'None'.

If Self=0 Then CellFormats(

CellFont(color:'White'), CellBorder('None',1,'Inner',I,J)