Expressions

A rich expression language is available for formulas and conditional formatting (go to About Conditional Formatting for more information). This language offers flexibility ranging from simple expressions involving the current cell to complex calculations on the results of a range of cells in the table.

The selected formatting is applied to the cell when the expression evaluates to TRUE.

When several expressions are applied, the expressions are evaluated in the order they appear in the list. If the first expression in the list evaluates to FALSE, the next expression will be evaluated. As soon as an expression evaluating to TRUE is reached, that expression’s formatting is applied to the cell and any following expressions will not be evaluated.

The following functions are supported:

Function	Description
ABS(val)	Returns the absolute value of val.
AVERAGE(val1, val2, ...)	Returns the average (arithmetic mean) of the arguments. Example: AVERAGE(COLVALUES()) will return the average of the values in all the cells of the current column.
CEIL(val)	The smallest integer greater than or equal to the specified number.
CELLVALUE() CELLV()	Returns the value of the current cell.  If the cell contains several values (count, horizontal and/or vertical percent) it returns the first value of the current cell.
CELLVALUE(column, row, [C | HP | VP]) CELLV (column, row, [C | HP | VP])	Returns the count/horizontal-/vertical percent value of the specified cell. C = Count HP = Horizontal Percents VP = Vertical Percents Examples: CELLVALUE(1,3) will return the value in the cell in the first column and third row of the table (or the first value if the cell contains several values (count, horizontal and/or vertical percent)) CELLVALUE(1,3,HP) will return the horizontal percentage in the cell in the first column and third row of the table, provided that that cell contains a horizontal percent. If it does not contain a horizontal percent, you will get an error message.
COLVALUES() COLV()	Returns the values in all the cells of the current column. If these cells contain several values (count, horizontal and/or vertical percent), the first value in each cell will be returned. Example: AVERAGE(COLVALUES(1,ROWS-1)) will return the average of the values in all the cells of the current column. If these cells contain several values (count, horizontal and/or vertical percent), the average of the first values in the cells will be returned.
COLVALUES(start, end, [C | HP | VP, column]) COLV (start, end, [C | HP | VP, column])	Returns the count/horizontal-/vertical percent values of the specified cells/column. This gives the ability to indicate a subset of values in a specific column. C = Count HP = Horizontal Percents VP = Vertical Percents Examples: MAX(COLVALUES(2,4)) will return the maximum of the values in the rows 2 to 4 in the current column. If these cells contain several values (count, horizontal and/or vertical percent), the maximum of the first values in the cells will be returned. AVERAGE(COLVALUES(1,5,VP) will return the average of the vertical percents in rows 1 to 5 in the current column. If these cells do not contain vertical percents, you will get an error message. MIN(COLVALUES(1,5,VP,3) will return the minimum of the vertical percents in rows 1 to 5 in column 3 in the table. If these cells do not contain vertical percents, you will get an error message.
COUNT(val1,val2, ...)	Returns the number of arguments. Example: COUNT(COLVALUES()) will return the number of rows in the column.
EMPTYVALUE() EMPTYV()	Returns the empty value. Examples: IF (CELLV(1,1)=EMPTYV(), 0,CELLV(1,1)) tests for an empty value. IF(CELLV()>10, CELLV(), EMPTYV()) sets an empty value. EMPTYV(), apply and close. Use Remove Empty Headers to hide the formula's empty cells.
EXP(power)	Returns E (the base of natural logarithms) raised to the specified power.
FLOOR(val)	The greatest integer less than or equal to the specified number.
IF(condition, expression1, expression2)	If condition evaluates to TRUE, expression1 is returned. If condition evaluates to FALSE, expression2 is returned. Example: IF(CELLVALUE()>=75, CELLVALUE(1,1), CELLVALUE(1,2)) will return the (first) value in the first row and column of the table if the (first) value in the current cell is greater than or equal to 75, otherwise it will return the (first) value in the first column and second row of the table.
LN(val)	Returns the natural logarithm of val.
LOG(val, base)	Returns the logarithm of val to the specified base.
LOG10(val)	Returns the logarithm of val to base 10.
MAX(val1, val2, ...)	Returns the maximum from a set of values. Example: MAX(COLVALUES()) will return the maximum of the values in the current column.
MIN(val1, val2, ...)	Returns the minimum from a set of values. Example: MIN(COLVALUES()) will return the minimum of the values in the current column.
NEG(val)	Returns the negation of val.
POWER(val, power)	Returns the number val to the specified power.
ROUND(val, [decimals])	Returns the number with the specified number of decimals nearest the specified value. If number of decimals is not specified, it will return the nearest integer.
ROWVALUES() ROWV ()	Returns the values in all the cells of the current row. If these cells contain several values (count, horizontal and/or vertical percent), the first value in each cell will be returned. Example: AVERAGE(ROWVALUES()) will return the average of the values in all the cells of the current row. If these cells contain several values (count, horizontal and/or vertical percent), the average of the first values in the cells will be returned.
ROWVALUES(start, end, [C | HP | VP, row]) ROWV (start, end, [C | HP | VP, row])	Returns the count/horizontal-/vertical percent values of the specified cells/row. This gives the ability to indicate a subset of values in a specific row. C = Count HP = Horizontal Percents VP = Vertical Percents Examples: MAX(ROWVALUES(2,4)) will return the maximum of the values in the columns 2 to 4 in the current row. If these cells contain several values (count, horizontal and/or vertical percent), the maximum of the first values in the cells will be returned. AVERAGE(ROWVALUES(1,5,VP) will return the average of the vertical percents in columns 1 to 5 in the current. If these cells do not contain vertical percents, you will get an error message. MIN(ROWVALUES(1,5,VP,3) will return the minimum of the vertical percents in columns 1 to 5 in row 3 in the table. If these cells do not contain vertical percents, you will get an error message.
SQRT(val)	Returns the square root of val.
SUM(val1, val2, ...)	Sum of the specified numbers. Example: SUM(ROWVALUES()) will return the sum of the values in the current row.

Note: Conditional formatting uses rounded values, so CELLVALUE()>0.5 will be true if Number of decimals is set to 1 and the value is 0,4999, because 0.5 will be displayed to the report viewer. This applies for all values returned from CELLVALUE, ROWVALUES and COLVALUES.

The following operators are supported:

Operator	Description
+	Addition
-	Subtraction
/	Division
<	Less than
%	Remainder (modulus) obtained by dividing one numeric expression into another
^	Power
>	Greater than
<=	Less than or equal
>=	Greater than or equal
= ==	Equality
<> !=	Inequality
AND &&	Logical AND
OR ||	Logical OR
NOT !	Logical NOT

The following constants are supported:

Constant	Description
COL	Current column number.
ROW	Current row number.
COLS	Total number of columns.
ROWS	Total number of rows.
TCOL	Total column for a horizontal distribution. -1 if no such column can be found.
TROW	Total row for a vertical distribution. -1 if no such row can be found.