calculate statement is used to perform arithmetic operations using literal and column values.
source operation source
where source is either of
and operation is one of the characters
+ - * / % for addition, subtraction, multiplication, division and modulo respectively.
There must be whitespace on each side of the _operation`_character
calculate column ResultCol as column Amount * value 1.2
calculate column Net as column total - column cogs
calculate column constant_7 as value 3.5 + value 3.5
The ResultCol parameter is the name of the column that will hold the results. This column may or may not exist (if necessary it will be created automatically).
Both of the two source parameters can specify a literal value, or the name of a column containing the value to use when performing the calculation.
- A literal value is specified using
Nwhere N is the literal number required
- A column name is specified using
colNamewhere ColName is the name of the column containing the values required
The ResultCol may be the same as a column specified by one of the source parameters in which case any existing values in it will be updated with the result of the calculation.
- Any blank or non-numeric values in a source column will be treated as 0
- An attempt to divide by zero will result in 0
- When performing a modulo operation, the two source values are rounded to the nearest integer first
- If the result column already exists then if option overwrite is set to
no, only blank cells in the result column will be updated.
- Add 1.5 to the values in the Rate column:
calculate column Rate as column Rate + value 1.5
- Multiply the values in the Rate column by those in the Quantity column
- Store the result in a new column called Charge
calculate column Charge as column Rate * column Quantity