Usage Examples of Operators and Functions

This topic explains some examples of using operators and functions.
For details on operators and functions available in formulas, refer to List of Operators and Functions.

Using Operators and Functions

Operator/Function Formula Example Example of Calculated 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 specify 100 to A, 10 to B, and 5 to C: 10
!= IF(A!=100,B,C) If you specify 100 to A, 10 to B, and 5 to C: 5
<> IF(A<>100,B,C) If you specify 100 to A, 10 to B, and 5 to C: 5
< IF(A<100,B,C) If you specify 100 to A, 10 to B, and 5 to C: 5
<= IF(A<=100,B,C) If you specify 100 to A, 10 to B, and 5 to C: 10
> IF(A>100,B,C) If you specify 100 to A, 10 to B, and 5 to C: 5
>= IF(A>=100,B,C) If you specify 100 to A, 10 to B, and 5 to 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" fields.
"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 the 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 the "Calculations in Tables" page.

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 calculated 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 calculated result of when "Time (Example: 26 hours 3 minutes)" is set for the display format of the field.

For details, refer to the "Calculation of Date, Time, and Date and Time" page.

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 the "& Operator: Concatenating Strings and Numbers" page.

Related Information: Calculating Empty Fields

Empty fields are considered as '0' or "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("")".

  • In the case of using IF function, if you enter "Calculated field = 0", the result becomes TRUE even when the calculated field is empty, not only when the calculated field value is '0'.

  • If you specify "Calculated field = """, decision will be made 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.

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, see the following pages.
Setting the Round Off, Round Down, or Round Up
Setting the Significant Digits of Numbers and the Rounding Method