List of Operators and Functions
This section describes operators and functions that you can use when creating formulas.
Available operators and functions vary depending on whether you calculate numbers or texts.
Description of Operators
You can use the following operators.
Operator | Description |
---|---|
+ | Adds numbers. |
- | Subtracts numbers. This operator can also be used as an unary operator for converting field values to plus or minus. |
* | Multiplies numbers. |
/ | Divides numbers. |
^ | Raises a number to a power. From the minus power of 100 to the power of 100 can be calculated. When a decimal is specified for the exponential power, the decimal number is rounded down to the integer. Example: 3^2.5 is converted to 3^2 and the result is 9. |
& | Concatenates strings or numbers. If a numeric type or string type field is referred but is empty, that field will be considered as a null string and will be concatenated. |
= | Returns TRUE when the text or number matches, and FALSE when the text or number does not match. Returns FALSE when comparing different types. |
!= | Inverts the comparison result of "=". Returns TRUE when comparing different types. |
<> | Same result as "!=". |
< | Compares numeric values and returns TRUE if the value on the left is smaller than the value on the right. Returns FALSE if the value on the left is greater than the value on the right. |
<= | Compares numeric values and returns TRUE if the value on the left is smaller than or equals to the value on the right. Returns FALSE if the value on the left is greater or equals to the value on the right. |
> | Compares numeric values and returns TRUE if the number value on the left is greater than the value on the right. Returns FALSE if the value on the left is smaller than the value on the right. |
>= | Compares numeric values and returns TRUE if the value on the left is greater than or equals to the value on the right. Returns FALSE if the value on the left is smaller than or equals to the value on the right. |
Priory Order of Operators
Operators are applied in the following priority order:
- \+ , - (Plus and minus unary operators: +1, -2, etc.)
- ^ (Exponentiation)
- * , / (Multiplication and division)
- \+ , - (Addition and subtraction)
- & (String operator)
- =, !=, <>, <, >, <=, >= (comparison operators)
Description of Functions
You can enter functions using uppercase characters or lowercase characters.
You can use the following functions.
Function | Description |
---|---|
SUM |
Adds numbers. The following format is to be used: |
YEN |
Displays the calculated result in "Yen" using thousands separator. And the decimal part of the result value is rounded according to the specified number of decimal places. The YEN function is used in the following format: |
DATE_FORMAT | Change the date and time format and the time zone. For details, see the "DATE_FORMAT Function: Usage Examples" page. |
IF | Specifies conditions, and returns value depending on whether such conditions are met. |
AND | Returns TRUE when all the conditions specified in the formula are met. Otherwise, returns FALSE. You can specify up to 32 arguments. |
OR | Returns TRUE when any of the conditions specified in the formula is met. Otherwise, returns FALSE. You can specify up to 32 arguments. |
NOT | Inverts conditions. |
ROUND | Rounds off the value. |
ROUNDDOWN | Rounds down the value. |
ROUNDUP | Rounds up the value. |
CONTAINS | Determines if the specified field matches the condition (selection), or if the table has any row data that matches the condition (a search string). |