Usage examples of operators and functions
This topic explains some examples of using operators and functions.
For details on the operators and functions available to use in formulas, refer to the following page:
List of operators and functions
Using operators and functions
| Operator/Function | Formula example | Example of calculation result |
|---|---|---|
| + | 3+2 | 5 |
| - | 3-2 | 1 |
| * | 3*2 | 6 |
| / | 3/2 | 1.5 |
| ^ | 3^2 | 9 |
| & | John&Jones | JohnJones |
| = | IF(A=100,B,C) | If you substitute 100 for A, 10 for B, and 5 for C: 10 |
| != | IF(A!=100,B,C) | If you substitute 100 for A, 10 for B, and 5 for C: 5 |
| <> | IF(A<>100,B,C) | If you substitute 100 for A, 10 for B, and 5 for C: 5 |
| < | IF(A<100,B,C) | If you substitute 100 for A, 10 for B, and 5 for C: 5 |
| <= | IF(A<=100,B,C) | If you substitute 100 for A, 10 for B, and 5 for C: 10 |
| > | IF(A>100,B,C) | If you substitute 100 for A, 10 for B, and 5 for C: 5 |
| >= | IF(A>=100,B,C) | If you substitute 100 for A, 10 for B, and 5 for C: 10 |
| SUM | SUM(1, 2, 3) | 6 |
| YEN | YEN(1100.5, 0) | ¥1,101 |
| DATE_FORMAT | DATE_FORMAT(1522972800, "YYYY MMM d", "system") | 2018 Apr 6 |
| IF | IF(A<100,B,C) | If the value of A is 90, the result is B. If the value of A is 110, the result is C. |
| AND | IF(AND(A>10,B>=10),"1","0") | If the values of A and B are 10: 0 |
| OR | IF(OR(A>10,B>=10),"1","0") | If the values of A and B are 10: 1 |
| NOT | IF(NOT(A>10),"1","0") | If the value of A is 10: 1 |
| CONTAINS | IF(CONTAINS(Workday, "Sunday"), "Working on Sunday", "Not Working on Sunday") | Displays "Working on Sunday" if Sunday is selected. Displays "Not Working on Sunday" if Sunday is not selected. |
| ROUND | ROUND(A * 1.1) | If the value of A is 8,875: 9,763 |
| ROUNDDOWN | ROUNDDOWN(A * 1.1) | If the value of A is 8,875: 9,762 |
| ROUNDUP | ROUNDUP(A * 1.1) | If the value of A is 8,875: 9,763 |
Calculating numeric value
Numeric calculation is performed using the Calculated or Text field.
"Field A" is the value of the field of which field code is "Field A". "Field B" is the value of the field of which field code is "Field B".
Sum values: Field A + Field B |
Calculate using "( )": (Field A - 1000) * 2 |
Calculate using * and +: Field A*5 + Field B*10 |
Calculating totals in the table
Assume that the following table is on a form.
| Item | Unit Price | Quantity | Subtotal |
|---|---|---|---|
| USB memory | 1,500 | 1 | 1,500 |
| Ball-point pen | 70 | 5 | 350 |
| LAN cable | 350 | 2 | 700 |
| Power strip | 150 | 1 | 150 |
| A5 notebook | 50 | 10 | 500 |
| (Total the numbers) |
If the field code of the "Subtotal" field is "Field_A", the subtotal can be obtained by the following formula: You can perform calculation even when Unit Price or Quantity is empty.
Formula: SUM(Field_A) |
For more details, refer to the following page:
Calculating fields in tables
Calculating date and time
The Calculated field can be used for the following calculations:
- Date and time difference calculation
- Addition and subtraction of date/time, and time
In formulas, enter time in seconds. For example, to specify 1 hour, type "3600" or "60*60*1". To specify 1 day, type "24*60*60*1".
Calculate by seconds: Field A + (60 * 60 * 1) |
Use Time for display format: Field A - Field B |
For details, refer to the following page:
Calculation of date, time, and date and time
Working with Text field
Use the Text field to concatenate strings or display YEN function.
Display the value of other field: Field A |
Concatenate strings: Field A & " " & Field B |
Example of YEN function: YEN(Field A * 1.1, 0) |
For details, refer to the following page:
& operator: Concatenating strings and numbers
Related information: Calculating empty fields
Empty fields are considered as "0" or a null string.
- Empty numeric type fields are considered as "0". However, when the "&" operator is used, such a field is treated as an empty string("").
- A string type field without input is treated as an empty string("").
- If you enter "Calculated field = 0" with IF function, the result returns TRUE when the Calculated field is empty, as well as when the Calculated field value is '0'.
- If you specify "Calculated field = """, the condition will be used to determine whether the field is empty or not. Returns FALSE when '0' is entered, and TRUE when the field is empty.
- Described below is the way to make the field empty instead of displaying '0'.
Specify "" as the result.- Example: IF(Quantity=0, "", Unit_Price*Quantity)
When the quantity is 0, the result will be empty. When the quantity is not 0, the result value of multiplication will be displayed.
- Example: IF(Quantity=0, "", Unit_Price*Quantity)
Related information: Values of "Number" and "Calculated" fields
Values of Number and Calculated fields might be rounded, depending on the number of digits. You can change the number of digits and method of rounding.
For details, refer to the following pages: