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 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 If Field A is 1, and Field B is 2, the result is 3. |
| Calculate using "( )": (Field A - 1000) * 2 If Field A is 1,500, the result is 1,000. |
| Calculate using * and +: Field A*5 + Field B*10 If Field A is 20, and Field B is 10, the result is 200. |
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) The result is 3,200. |
For details, refer to Calculations 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) If Field A is "2014-01-01 09:00", the result is "2014-01-01 10:00". This is the calculation result of when "Date and time (Example: 2012-08-06 2:03)" is set for the display format of the field. |
| Use Time for display format: Field A - Field B If Field A is "2014-01-01 09:00" and Field B is "2014-01-01 08:00", the result is "1 hour 0 minute". This is the calculation result of when "Time (Example: 26 hours 3 minutes)" is set for the display format of the field. |
For details, refer to 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 If Field A is "John", the result is "John". |
| Concatenate strings: Field A & " " & Field B If Field A is "John" and Field B is "Jones", the result is "John Jones". |
| Example of YEN function: YEN(Field A * 1.1, 0) If Field A is "8,875", the result is "¥9,763". The value of "8,875 × 1.1 = 9762.5" is rounded to the nearest whole number. |
For details, refer to & 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, in the case of using the '&' operator, such fields are considered as "null string("")".
-
Empty string type fields are considered as "null 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 ="" after a "Number" field, it will determine whether or not the field is empty. The result will return TRUE if the "Number" field is empty, and FALSE if 0 is entered as the value.
-
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 Numbers and Calculated Fields
Values of the Number field and the Calculated field 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.
Setting the Round Off, Round Down, or Round Up
Setting the Significant Digits of Numbers and the Rounding Method