Excel function formula
2024.4.17© 2024 CIMTOPS CORPORATION

Revision history
Date

VersionPage

Description

2015-01-16

Rev1.0.0--

Issued the first version.

2018-11-05

V6.2.18110

P4, 10Added the AND function and OR function.

P4

Addition of apps that do not support AND and OR functions

P4

Added the Windows version i-Reporter AND function and OR function.

2019-12-17

P8

Added search coverage for LOOKUP and VLOOKUP functions.

2020-12-25

P29

Added "Other restrictions" and added that the formula abbreviated format
is not supported.

2021-03-30

P22-24In "Specification limits of ConMas i-Reporter in EXCEL formula"
Add"TRUE" or "FALSE" as strings"“About non-input judgment""When a range of cells includes merged cells"

2021-03-30

P25

Changed "differences in processing for merged cells" to "differences inprocessing of COUNTBLANK function for merged cells" and reviewed thedescription.

2019-03-05

2019-05-14

WindowsversionV4.1.19040

2021-05-13

V8.0.21050

P7

Added SQRT function.

2022-02-21

V8.0.22020

P9、P12

Added EDATE function.
Added restrictions on the use of the EDATE function.

2024-04-17

V8.1.24040

P7、P13

Added POWER function.
Added restrictions on the use of the POWER function.

P35

Restrictions on the use of array constants have been added to"Other Restrictions.
© 2024 CIMTOPS CORPORATION

2Contents• Available EXCEL functions
logic function
Statistics function
math function
Search/matrix functions
string manipulation functions• Restrictions on the use of AND/OR functions• Date/Time calculation• Date/time calculation result display• Result data type• Old-style and new-style formula clusters• Changing formula clusters from old to new• Specification limit of ConMas i-Reporter in EXCEL formula• EXCEL function usage example

Setting method of VLOOKUP

function© 2024 CIMTOPS CORPORATION

3

Logic function
Logical functions are used to determine specified conditions.
1IF

Determines whether a value or expression satisfies a condition and returns the specified content
depending on the result.

2

IFERROR

Returns the specified value if the result of the formula is an error, otherwise returns the result of
the formula.

3

AND

Find the logical AND. Can only be used in the conditional expression of the IF function.
* iOS version app: Ver.6.2.18100 or higher Windows version app: Ver.4.1.19040 or higher

4

OR

Find the logical AND. Can only be used in the conditional expression of the IF function.
* iOS version app: Ver.6.2.18100 or higher Windows version app: Ver.4.1.19040 or higher

There are restrictions on the use of AND/OR, so please also refer to the "Restrictions on theuse of AND/OR functions" section for details.

© 2024 CIMTOPS CORPORATION

4

Statistics functions (1/2)
Statistical functions can find the average, maximum, and minimum values ofaggregated data.
5

AVEDEV

Returns the mean deviation.

6

AVERAGE

Returns the average value of the values specified by the arguments.

7

AVERAGEA

Returns the average value of the values specified by the arguments. Logical values are also
considered.

8

AVERAGEIF

Returns the average value of the cells that meet the condition.

9

AVERAGEIFS

Returns the average of numbers that meet multiple conditions.

10

COUNT

Returns the sum of numeric-only counts in the specified range of cells.

11

COUNTA

Returns the total number of numbers, strings, etc. in the specified range of cells.

12

COUNTBLANK

Returns the sum of unfilled data in the specified range of cells.

13

COUNTIF

Returns the number of data in the specified cell range that match the search criteria.

14

MAX

Returns the maximum value from the values specified by the arguments.

15

MAXA

Returns the maximum value from the values specified by the arguments. Logical values are also
considered.

16

MEDIAN

Returns the median value from the values specified by the arguments.

17

MIN

Returns the minimum value from the values specified by the arguments.

18

MINA

Returns the minimum value from the values specified by the arguments. Logical values are also
considered.

© 2024 CIMTOPS CORPORATION

5

Statistics functions (2/2)
19

MODE

Returns the most frequent value from the values specified in the argument.

20

PERCENTRANKReturns a value representing the rank as a percentage in the array or cell range specified by theargument.

21

STDEV

Returns an estimate of the population standard deviation based on the sample, taking theargument as a sample of a normal population.

22

STDEVA

Returns an estimate of the population standard deviation based on the sample, taking theargument as a sample of a normal population.
Logical values are also considered.

23

STDEVP

Returns the population standard deviation, taking the argument as the entire population.

24

STDEVPA

Returns the population standard deviation, taking the argument as the entire population. Logicalvalues are also considered.

© 2024 CIMTOPS CORPORATION

6

Math function
Mathematics and trigonometric functions can calculate four arithmetic operations suchas total values, rounding numbers and factorials.
25

ABS

Returns the absolute value of a number.

26

ROUND

Rounds a number to a specified number of digits.

27

ROUNDDOWN

Truncates a number to a specified number of digits.

28

ROUNDUP

Rounds a number up to a specified number of digits.

29

SUM

Sums the values of the specified number or range of cells.

30

SUMIF

Sums the values of cells that match the specified criteria.

31

SUMIFS

Sums up numbers that meet multiple specified criteria.

32

SQRT

A function that finds the positive square root of a number.
* Designer Ver.8.0.21050 or higher* iOS version app: Ver.8.0.21050 or higher Windows version app: Ver.5.2.21050 or higher* Check clusters cannot be included in calculations.

33

POWER

Returns the result of a number raised to a power.
* Designer Ver.8.1.24040 or higher* iOS Version app: Ver.8.1.24040 or higher Windows version app: Ver.5.2.24040 or higher© 2024 CIMTOPS CORPORATION

7

Search/matrix functions
Search and matrix functions are used to search for data in worksheets.
34

LOOKUP

Searches the specified range vertically and returns the value of the corresponding range cell at
the same position as the search value.
* Horizontal search is not supported.

35

VLOOKUP

Searches the specified range vertically, and returns the value in the specified column number if itmatches the search value.
* Horizontal search is not supported.

36

AREAS

Returns the number of regions in the range specified by reference.

© 2024 CIMTOPS CORPORATION

8

Date/time functions
Date/time functions are used to perform date/time calculations.
37

EDATE

Returns a date that is months after or months before the specified start date.
*Designer Ver.8.0.22020 or higher* iOS version app: Ver.8.0.22020 or higher Windows version app: Ver.5.2.22020 or higher© 2024 CIMTOPS CORPORATION

9String manipulation functions
It is used when converting a character string to a specified format or extracting part of acharacter string.
38

ASC

Converts a full-width string to a half-width string.

39

CONCATENATE

Joins multiple strings or numbers specified by arguments.

40

LEFT

Returns the specified number of characters from the left end of a string.

41

LEN

Returns the number of characters in the specified string.

42

MID

Returns a string starting at a specified starting position and starting at a specified number ofcharacters.

43

RIGHT

Returns a specified number of characters from the right end of a string.

In addition to the above, only the four arithmetic operators (+ - * /) can be used.

© 2024 CIMTOPS CORPORATION

10Restrictions on the use of AND/OR functions
AND/OR functions can only be used in conditional expressions of IF functions. It is also possible touse AND/OR functions within AND/OR functions. It is also possible to use another function within
the AND/OR function.
= IF (conditional expression, TRUE processing, FALSE processing)
The AND/OR function can be used in theconditional expression of the IF function.

■ Examples where AND/OR functions can be used

＝IF(OR(AND(SUM(S1C0,S1C1)>0,S1C0=S1C1), AND(S1C1=S1C2,S1C1>0)),1,0)
It is also possible to use AND/OR functions within AND/OR
functions.
It is also possible to use another function within the AND/OR
function.

＝IF(S1C0<0,”NG”,SUM(IF(AND(S1C0=S1C1,S1C0=S1C2),1,0),1))
There is no problem because the AND function is used in theconditional expression of the second IF function.

■ Examples where AND/OR functions cannot be used

＝IF(SUM(AND(S1C0>0,S1C1>0),S1C2,S1C3)>3,”OK”,”NG”)
It will be NG because the AND/OR function is used in the
SUM function.
© 2024 CIMTOPS CORPORATION

11

Restrictions on the use of the EDATE function
The EDATE function calculates a date by adding (or subtracting if the value is negative) the
specified number of months from the start date by specifying the start date with year, month, andday as the first argument and the month as the second argument.
Since this is a function whose result is a date, please set the “Result data type" to “Date/Time" andspecify the date format for the formula cluster for which you wish to display the EDATE function
result.

=EDATE(start date, month)■ About specifying the first argument
Specify the serial number or cluster as the start date in the first argument.
If a decimal value is specified, it will be calculated by truncating the decimal point.
It is not possible to specify the date as a character string. If you want to specify the start date witha fixed value, please specify the serial number.
(Cluster type that can be specified as the first argument)“Date", "Calendar Date", "Calculation Formula (Set the calculation result data type to “Date/Time"and specify the date format.)", "Date of issue" (iOS only)", “Last update date (iOS only)"■ About specifying the second argument

Specify a number or cluster as the month to calculate for the second argument.
If a decimal value is specified, it will be calculated by truncating the decimal point.
A character string cannot be specified.
(Cluster type that can be specified as the second argument)"Numerical number keyboard", "Choice of Numerical number", "Calculation formula", "Toggle
select (calculate using "value")", "Toggle summary", "Single choice (“Take item of choices as
number" setting)", "Barcode (only available when “Value is numerical number" setting)"© 2024 CIMTOPS CORPORATION

12POWER関数の使用制限
The POWER function calculates powers of a number by specifying a number (the base of the power)
as the first argument and an exponent as the second argument.
Both arguments must be numerical values.
=POWER(number, exponent)■Specifying the first argument

Specifies a number (the base of a power).
■Specifying the Second Argument

Specify the exponent.
(Cluster types that can be specified as arguments)
・ Choice of Numeric selection
・ Numerical value・ Calculation formula・ Toggle select・ Toggle summary
・ Single choice (“Take item of choices as number" setting)・ Barcode (only available when “Value is numerical number" setting)"※If a serial value is used in the POWER function, it may not work as intended due to overflow of digits.
© 2024 CIMTOPS CORPORATION

13

Date/Time calculation
Date and time are calculated using serial numbers.

■What is a serial number"January 1, 1900" is set to "1", and the total number of days and time since that day is expressed as anumerical value.
The number "1" is calculated as one day, and the time is represented by the decimal value of "1"
converted to 24 hours.

"January 1, 1900" → Serial number is "1""January 5, 1900" → Serial number is "5""January 1, 2015" → Serial number is "42005“"0:00:00" → Serial number is "0.0""12:00:00" → Serial number is "0.5""21:00:00" → Serial number is "0.875"© 2024 CIMTOPS CORPORATION

14

Date/time calculation result display
Since the date and time are calculated using serial numbers, the calculation results
will also be serial numbers and displayed as numerical values.
Therefore, the “Result data type" setting has been added to the input parametersettings of the formula cluster. By setting “Result data type" to “Date,Time",
calculation results can be displayed in the format of the specified date or time.
（Example）
Designer setting

Tablet device© 2024 CIMTOPS CORPORATION

15

Result date type
There are three types of calculation result data type settings: "Other", “Date,Time",and “character string". Each display has the following specifications.
■Other
The same calculation result display as before is performed. Specify when there is no specific display
specification such as “Date,Time" or "character string". Automatically judge the calculation result anddisplay it as a character string or numerical value.
Therefore, strings that can be converted to numbers are displayed as numbers even if they are actuallystrings. Also, logical values are displayed as numerical values with True=1 and False=0, respectively.

■Date,TimeCalculates the numerical value resulting from the calculation as a serial number and displays it as adate/time in the specified format.
An error will occur if the calculation result is a value that cannot be converted to date/time, such as acharacter string.

■character string
Forces calculation results to be displayed as strings. If the calculation result is a logical value, it will be
displayed as a string of "true" or "false".
If the calculation result is a numerical value and it is a decimal value that continues infinitely like
1.666666, it will be rounded according to the number of digits after the decimal point in the formula
cluster set in Designer and then converted to a character string for display.

© 2024 CIMTOPS CORPORATION16

In the EXCEL formula
Specification limits of ConMas i-Reporter
There may be some differences in calculation formulas or calculation result
display specifications between EXCEL and ConMas i-Reporter.
Below is a summary of ConMas i-Reporter specification limitations.

© 2024 CIMTOPS CORPORATION

Cells included in formulas must be clustered
In ConMas i-Reporter, formulas that include cells that are not clustered on EXCEL
in formula clusters will be displayed as "#REF" when imported into Designer, andan error will occur when saving the Forms. Therefore, the cells included in theformula must be clustered in EXCEL.

Cells used in formulas must be
clustered.

© 2024 CIMTOPS CORPORATION

18

Pattern that can capture cell information that is not clustered
Cells included in a formula must be clustered, but as an exception, cell values can beimported into ConMas i-Reporter without clustering only for some EXCEL function
arguments.
（Object EXCEL function）
LOOKUP、VLOOKUP、AVERAGEIF、AVERAGEIFS、COUNTIF、SUMIF、
SUMIFS
Among the arguments of the above EXCEL function, it is not necessary to set thecluster for the cells that are the arguments for specifying the range. (It is also
possible to dare to set a cluster.)Cells in this range can be imported into
ConMas i-Reporter without cluster
setting.
There is no problem even if thespecified range is on another sheet ooutside the print setting range.
=VLOOKUP(K4,Q4:S10,3,FALSE)

Among the arguments inthe function, the part
where the range is
specified© 2024 CIMTOPS CORPORATION

19

Cluster types that cannot be included in formulas
Some cluster types cannot be included in calculation formulas. If the following
cluster types are included in the formula, the formula will be displayed as "#REF"
when importing EXCEL to Designer even for cells with cluster settings.

（ Cluster types that cannot be used informulas ）• Handwriting note
• Free whiteboard• Free draw
• Image• Issuer• Inspector• Approver• Action"#REF" is displayed when theformula is determined to beinvalid.

© 2024 CIMTOPS CORPORATION

20

strings are not treated as numbers
In ConMas i-Reporter, when a cluster type that handles strings, such as keyboard text
clusters, is included in a calculation formula, it is not treated as a numerical value. So even if
you enter a number with a keyboard text cluster, etc., it will be treated as a string, not anumber, and ignored in numerical calculations. This is valid for formulas that handle character
strings, such as LEFT and RIGHT.
For Single choice clusters and QR code clusters, the value is treated as a number instead ofa character string, so it can be included in numerical calculations only when the “result as anumber" setting is selected. However, if the value is a character string that cannot be
converted to a numeric value, it will be ignored from numeric calculations.

© 2024 CIMTOPS CORPORATION

21

Handling formula errors
Error details such as "#DIV/0!", "#REF!", "#NAME?", "#N/A", "#NULL!", "#VALUE!"
However, in i-Reporter, they are all displayed as " Error of Expression!".

© 2024 CIMTOPS CORPORATION

22

Handling toggle select clusters
A Toggle select cluster is a cluster that can set a numerical value for each label, and is a
special cluster that has both a string and a numerical value as internal values. Therefore,
when handling a Toggle select cluster in a formula, it may be handled as a numeric value or
as a character string depending on the EXCEL function used. Also, when dealing with
Toggle select clusters in the no input state with a numerical value, it is judged as "0".

(function that treats the toggle selection cluster as a string)
IF、ASC、CONCATENATE、LEFT、LEN、MID、RIGHT(All functions other than the above EXCEL functions treat the Toggle select cluster
as a numerical value.)
Use a label if you want totreat it as a string.

Use value if you
want to treat it as anumber.

© 2024 CIMTOPS CORPORATION

23

Single check cluster always True or False
ConMas i-Reporter treats unfilled status as False for Single check clusters.
Therefore, Single check clusters included in formulas always have a value of Trueor False.
For example, Single check clusters are always counted with the COUNTA function.

Single checkcluster© 2024 CIMTOPS CORPORATION

24

"TRUE" or "FALSE" as strings
Unlike EXCEL, i-Reporter does not convert data types automatically. If "TRUE" or "FALSE" isspecified as a character string, it will be determined as different from TRUE or FALSE as a bool
type. On the other hand, EXCEL may automatically convert to bool type depending on thecontents of the calculation formula even if "TRUE" or "FALSE" is specified as a character
string.
Because of this difference, calculation results involving "TRUE" or "FALSE" may differ between
i-Reporter and EXCEL.
Calculation result on i-Reporter

Calculation result on EXCEL=IF(B2="FALSE","OK","NG")=IF(B3="","FALSE","TRUE")
In other words, the calculation
result is "FALSE" as a character
string.

=COUNTIF(B2,"FALSE")

In i-Reporter, the result of IF function is consistent
with EXCEL, but the result of COUNTIF function
is different. This is because in EXCEL, thecharacter string "FALSE" specified by COUNTIF
is automatically converted to a bool value FALSE,
so "FALSE" as a character string in thecalculation result of B2 is not counted.

*Currently, in the Windows version application, even if the calculation result data type is specified as "character string",
TRUE/FALSE is treated as bool type. Therefore, depending on the calculation formula, there may be differences in calculation
results between the iOS version app and the Windows version app.
© 2024 CIMTOPS CORPORATION

25

About non-input judgment
In i-Reporter, string comparison can only be performed between string types, but as
an exception,As shown below, by using the IF function and comparing with anempty string in a conditional expression, it is possible to determine whether or not
the target cluster is not entered, regardless of the string type.

(Example) IF(S1C1=“”,“not entered”,“entered”) *S1C1 is the target cluster
However, since i-Reporter cannot distinguish between "empty characters" and "notentered", both are judged as not entered. In addition, clusters that can be judged as
not entered by this method are limited to cluster types that can be used as
calculation formula targets other than Single check clusters.
In addition, it is not possible to judge non-input by empty character comparison
except for the IF function.

*In EXCEL, it is possible to judge whether a character string or numerical value has not been entered bycomparing it with bool type FALSE, but i-Reporter does not automatically convert the data type, so it cannot
judge whether it has not been entered by comparing it with FALSE.
© 2024 CIMTOPS CORPORATION

26

When a range of cells includes merged cells
AVERAGEIF、AVERAGEIFS、COUNTIF、SUMIF、SUMIFS、LOOKUP、VLOOKUPFor functions, if the cell range specification includes merged cells, the number of cells in all
merged cells must be the same. If this does not match, normal calculation results cannot be
obtained.
(OK Ex.) The number of cells in the merged cell for “Sex" and "Age" is 8.

Cell range
specification

Cell range
specification(NG Ex.) There are 8 cells in the merged cell for “Sex" and 12 cells in the merged cell for "Age", which do not match.

Cell range
specification

Cell range
specification© 2024 CIMTOPS CORPORATION

27

Difference in processing of COUNTBLANK function for merged cells
Regarding the COUNTBLANK function, EXCEL does not treat merged cells as a single celland performs calculations by treating each cell in the range individually. On the other hand,
i-Reporter treats merged cells as one cluster for calculation. As a result, the calculation
result of the COUNTBLANK function including merged cells will appear different between
EXCEL and i-Reporter.
In EXCEL, the cells in the specified range are individually counted regardless of the merged state.

=COUNTBLANK(N2:AG6)

ConMas i-Reporter counts merged cells as one cluster.

© 2024 CIMTOPS CORPORATION

28

EXCEL function usage example
Setting method of VLOOKUP function
Examples of the use of EXCEL functions will be explained in detail using the VLOOKUP
function, which is a typical function.

© 2024 CIMTOPS CORPORATION

①About the VLOOKUP function■What is VLOOKUP function
This function searches for a specific value in the first column (the leftmost
column) of the specified range and returns another value in the same row.
You can use it to search a table created using cells in EXCEL with a specificvalue and return the value in the table according to the result.

（ Example of using the VLOOKUP function ）specified
range

Rows foundas a resultof search
VLOOKUP function.
Here, we are returning the"price" of the specified value(tangerine).

Specifies the value
to search for in theleftmost column ofthe specified range.

© 2024 CIMTOPS CORPORATION

30

②Write VLOOKUP function on EXCELTo import the VLOOKUP function into ConMas i-Reporter, first describe the
VLOOKUP function in an EXCEL file.

■Format of the VLOOKUP function=VLOOKUP(search value, range, column number, search method)
Search value = Specifies the value to search for in the leftmost column of the specified
range.
Range = Specifies the range to search. (Specify the range to be a table)
Column number = Specify the column number (the column in the range) of the value youwant to display as search results.
Search method = TRUE for approximate search, FALSE for exact match search.
(This can be omitted. If omitted, it will be treated as TRUE.)© 2024 CIMTOPS CORPORATION

31

③ About specifying the range
ConMas i-Reporter has the following specification restrictions regarding the
EXCEL import function.
・Cell data that are not clustered are not included.
・Cell data outside the print range setting is not included.
However, the exception is limited to the arguments of some functions such as the VLOOKUP function.
For the cells that correspond to the range specified in the second argument of the VLOOKUP function(the range of the table), these values can be imported even if they are not clustered, outside the print
range setting, or on a different sheet.

Table values canbe imported even if
the table is outside
the print range
settings.

Even if the body of the
VLOOKUP function andthe table are on different
sheets, the values in thetable can be imported.

(Caution) If the cells in the table range are set to be hidden, or if the
sheet with the table is set to be hidden, cell data will not be imported.
© 2024 CIMTOPS CORPORATION

32

④ Set the cell containing the VLOOKUP function to the formula cluster
Select the cell in which the VLOOKUP function is written, execute "Select kind ofcluster" from the right-click menu, and set the “Calculation formula" in “select kind ofcluster".
If you specify a cell instead of a fixed value for the search value, set the cluster typeas necessary for the corresponding cell as well.
2. Add-in starts

1. Select the cell containing the
VLOOKUP function and execute"i-Reporter type selection" fromthe right-click menu.

3. Select "Formula" from thecluster type selection andclick the "Set" button.

If you specify a cell as the searchvalue, set the cluster type asnecessary for the corresponding cell
as well.
© 2024 CIMTOPS CORPORATION

33

⑤Import EXCEL file into Designer
Save the EXCEL file with the settings and import it to Designer.
2. Click the "Create from EXCEL file"button and select the EXCEL filecreated earlier to import.
1. Click "Create form definition" on theupper left of Designer.

3. After successfully importing to
Designer, save the definition.

With the above procedure, you can handle the Forms using the VLOOKUP function
on the tablet.
© 2024 CIMTOPS CORPORATION

34

Other restrictions■ Descriptions that omit the conditional part of the calculation formula are
not supported.
（Ex.）=IF(A1,”OK“,”NG“)=IF(A1<>"",”OK","NG")

Abbreviated form is not supported.
Please fix like this.

■Formulas using array constants are not supported.
（Ex.）=VLOOKUP(B2,{“B01”,”Part01”;”B02”,”Part02”;”L01”,”Label01”},2,FALSE)© 2024 CIMTOPS CORPORATION

35