How can we help?

Search for answers or browse our knowledge base

< All Topics
Print

calculation engine

Overview

  • A sheet is limited to 4095 columns and 1048575 rows.
  • Function names and booleans are in the user’s locale. Supported locales are :

English, Danish, Dutch, Finnish, French, German, Italian, Norwegian, Portuguese, Spanish, Swedish. In other cases, the calculation engine switches to English.

  • The engine uses the user’s locale, including the argument separator.
  • References are in A1, supporting relative and absolute references, row or column, but not 3D references (e.g., =SUM(Sheet1:Sheet3!A1)), which will be advantageously replaced by a multi-tab which offers elaborate synthesis functions.

Errors

The errors reported are the following:

Error Remarks Excel compatible
#CIRCLE! The cell is part of a cycle
#DIV/0! YES
#N/A YES
#NAME? Function, unknown name YES
#NUM! YES
#PARAM! Wrong number of parameters when calling a function
#SYNTAX! The syntax of the formula is not correct for the Calame engine
#REF! Invalid reference YES
#VALUE! YES
#NOTSUPPORTED Specific formula function or expression not supported in GT
#UNKNOWN! Unknown error, reports a bug in the engine

Date management

The calculation engine does not support the “Calendar since 1904” Excel option
Dates strictly inferior to 01/03/1900 are not compatible with Excel.
Particularly the functions taking a date as input (MONTH, DAY,…) do not work in the same way as Excel with empty values.
Thus, MONTH(Ø) = 12 in GT and 1 in Excel (because the serial number date 0 is 12/12/1899 in GT and 00/01/1900 in Excel).

Functions

Overview

  • The volatile functions are recalculated as in Excel, i.e., at each evaluation. The volatile functions are :

ALEA, ALEA.ENTRE.BORNES, TODAY, NOW, INDIRECT, SHIFT

  • Matrix formulas are not supported
  • Function names are not case-sensitive.
  • Functions returning text have no limit of 32767 characters except REPT.
  • Some Excel functions work differently depending on whether a parameter is a value or a reference. For example,

AND(TRUE; “AAAAA”) returns #VALUE! while AND(TRUE;A1) with A1=”AAAAA” returns TRUE. The grid engine does not differentiate between these two cases. In the example above, it always returns TRUE.

  • Excel functions that deal with integers return an inconsistent result if the integer exceeds 2^53 (double precision). For example for GCD(2^60 ;2^60-1) = GCD(1152921504606846976, 1152921504606846975) Excel returns 1,…E18 when the result is 1. Some GT functions will check that the operands do not exceed the limit and return #NUM! if they do (e.g. GCD and LCM). These cases are indicated in the function list.

Functions with string criteria (for NB.IF, SUM.IF, SUM.IF.ENS…)

If the criterion contains ‘*’, ‘?’, the character will be interpreted as a wildcard (in the same way as Excel).

The protection character of these wildcards is ~

In addition, string criteria are limited to 255 characters in Excel. The GT engine does not have this limitation.

List of the engine functions

GT specific functions

Name (EN) Name (FR) Syntax Remarks
GTDISPLAYTEXT GTDISPLAYTEXT GTDISPLAYTEXT (cell) Returns the text displayed by the cell (takes into account the numeric format)
GTHASH (deprecated as of version 2019) GTHASH GTHASH(range, [algo, DoPj, SkipEmpty]) Algo = CRC32 or MD5 (default)

DoPj : boolean

SkipEmpty: boolean

GTMOAXIS GTMOAXIS GTMOAXIS([offset]) Returns the name of the multi-tab group axis in a multi-tab group
GTMOAXISVAL GTMOAXISVAL GTMOAXISVAL([offset]) Returns the current (or previous/following) item in a multi-tab
GTMOGETSHEETNAME GTMOGETSHEETNAME GTMOGETSHEETNAME(item [, Axis, tab_index] ) returns the name of the grid/sheet for the item in a multi-tab, Axis and tab_index allow to specify the multi-tab group and/or the tab number in a multi-tab group with several sheets to be expanded
GTMS GTMS GTMS.Function_GTMS(argument) Used to aggregate the data of a multi-tab. See the detailed article Data synthesis of a multi-tab
GTPATNUM GTPATNUM GTPATNUM([reference]) Returns the “row” number in a pattern. See detailed article GTPATNUM
GTSGMO GTSGMO GTSGMO(‘Grid’!ref) Summarizes the values of a cell on the sheets of a multi-tab group. The name of the grid is mandatory.
GTVALIDMAILADDRESS GTVALIDMAILADDRESS GTVALIDMAILADDRESS() Returns the email address, if validated, in Answer (validation process via .checkmail attachment)
GTADMAILADDRESS GTADMAILADDRESS GTADMAILADDRESS() Returns the email address of the AD account used for authentication in Answer (authentication is enabled via the campaign launch action)
GTPJSIZE GTPJSIZE GTPJSIZE(ref_cell) Returns the size of the file saved in the attachment component located in “ref_cell

Date & Time

Name (EN) Name (FR) Syntax Remarks
DATE DATE DATE(year, month, day)
DATEVALUE DATEVAL DATEVALUE(date_text) Only supports date_text in dd/mm/yy[yy] (local) formats
DAY JOUR DAY(serial_number)
DAY360 JOUR360
EDATE MONTH.DECALER EDATE(start_date,months) Range => #VALUE!
EOMONTH FIN.MOIS EOMONTH(date, month)
HOUR HEURE HOUR(serial_number)
ISOWEEKNUM HOUR.ISO ISOWEEKNUM(date) Excel >= 2013, GT >= 3.9
MONTH MOIS MONTH(serial_number)
NETWORKDAYS NETWORKDAYS(start_date, end_date, [holidays])
NETWORKDAYS.INTL NB.JOURS.OUVRES.INTL NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
NOW MAINTENANT NOW( )
SECOND SECONDE SECOND(serial_number)
TIME TEMPS TIME(hour, minute, second)
TIMEVALUE TEMPSVAL TIMEVALUE(time_text)
TODAY AUJOURDHUI TODAY()
WEEKDAY JOURSEM WEEKDAY(serial_number,return_type)
WEEKNUM NO.SEMAINE WEEKNUM(serial_num,return_type) Range => #VALUE!return_type >= 21 from GTv3.9
ISOWEEKNUM NO.SEMAINE.ISO ISOWEEKNUM(serial_num) from GTv3.9 equivalent to WEEKNUM with return_type to 21
YEAR ANNEE YEAR(serial_number)

Information

Name (EN) Name (FR) Syntax Remarks
ERROR.TYPE TYPE.ERREUR ERROR.TYPE(val_error) GT errors the values are >= 100:

#SYNTAX ! : 100

ERROR.TYPE For

#CIRCLE! : 101

#PARAM! : 102

#UNKNOWN! : 103

ISBLANK ESTVIDE ISBLANK(value)
ISERR ESTERR ISERR(value)
ISERROR ESTERREUR ISERROR(value)
ISEVEN EST.PAIR ISEVEN(number) Range => #VALUE!
ISLOGICAL ESTLOGIQUE ISLOGICAL(value)
ISNA ESTNA ISNA(value)
ISNONTEXT ESTNONTEXTE ISNONTEXT(value)
ISNUMBER ESTNUM ISNUMBER(value)
ISODD EST.IMPAIR ISODD(number) Range => #VALUE!
ISTEXT ESTTEXTE ISTEXT(value)
N N N(value) Range => TopLeft
NA NA NA()
TYPE TYPE TYPE(value)

Logic

Name (EN) Name (FR) Syntax Remarks
AND ET AND(logical1, [logical2], …)
FALSE Faux FALSE()
IF SI IF(logical_test, [value_if_true], IF [value_if_false])
IFERROR SIERREUR IFERROR(value,value_if_error) Excel 2007 and > function
NOT NON NOT(logical)
OR OU OR(logical1,logical2,…)
TRUE Vrai TRUE()

Search & Reference

Name (EN) Name (FR) Syntax Remarks
ADDRESS ADDRESSE ADDRESS(row,column,[abs_num],[a1],[sheet_name_txt]) Parameter A1 is ignored, the result is always produced in style A1
CHOOSE CHOISIR CHOOSE(index_num,value1,value2,…)
COLUMN COLONNE COLUMN(reference)
COLUMNS COLONNES COLUMNS(range)
HLOOKUP RECHERCHEH HLOOKUP(lookup_value,table_array, row_index_num,[range_lookup])
INDEX INDEX INDEX(array,row_num[,column_num]) Excel compatibility

* no syntax handling with field numbers

* By default, column_num is 1 as in Excel

* Returns #VALUE! if column_num = 0

INDIRECT INDIRECT INDIRECT(range_or_text[,a1]) Always in A1, the second parameter is ignored
ISREF ESTREF ESTREF(value)
LOOKUP RECHERCHE LOOKUP(lookup_value,lookup_vector,result_vector)

or

LOOKUP(lookup_value,array)

Excel compatibility:

* GT returns #N/A if lookup_vector or result_vector is not a vector (Excel only for result_vector)

* GT returns #N/A if lookup_vector and result_vector are not the same size

MATCH EQUIV MATCH(lookup_value,lookup_array,[match_type])
OFFSET DECALER OFFSET(range,rows,columns,[width_rows,[height_columns]]) Returns #VALUE! when the first parameter is not a reference (Excel forbids the entry of such a formula)
ROW LIGNE ROW(reference)
ROWS LIGNES ROWS(range)
VLOOKUP RECHERCHEV VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Maths

Name (EN) Name (FR) Syntax Remarks
ABS ABS ABS(number)
ACOS ACOS ACOS(number)
ACOSH ACOSH ACOSH(number)
ASINH ASINH ASINH(number)
ATAN ATAN ATAN(number)
ATAN2 ATAN2 ATAN2(x_num,y_num)
ATANH ATANH ATANH(number)
CEILING PLAFOND CEILING(number,significance)
COMBIN COMBIN COMBIN(number,number_chosen)
COS COS COS(number)
COSH COSH COSH(number)
DEGREES DEGRES DEGREES(angle)
EVEN PAIR EVEN(number)
EXP EXP EXP(number)
FACT FACT FACT(number)
FACTDOUBLE FACTDOUBLE FACTDOUBLE(number)
FLOOR PLANCHER FLOOR(number,significance)
GCD PGCD GCD(number1,number2, …) Returns #NUM! if any parameter exceeds 2^53
INT ENT INT(number)
LN LN LN(number)
LCM PPCM LCM(number1,number2, …) Returns #NUM! if any of the parameters or the result exceeds 2^53
LOG LOG LOG(number,base)
LOG10 LOG10 LOG10(number)
MOD MOD MOD(number,divisor)
MROUND ARRONDI.AU.MULTIPLE MROUND (number, multiple)
MULTINOMIAL MULTINOMIALE MULTINOMIAL(number1,number2, …) Returns #VALUE! if an argument is of string type (even if the string contains a number. In this case, Excel returns the wrong value because it uses the SUM function internally which does not do this conversion).
ODD IMPAIR ODD(number)
PI PI PI()
POWER PUISSANCE POWER(number,power)
PRODUCT PRODUIT PRODUCT(number1, [number2], …)
QUOTIENT QUOTIENT QUOTIENT(numerator,denominator) in Excel <= 2003, can return -0 (for example: QUOTIENT(-1,4))
RADIANS RADIANS RADIANS(angle)
RAND ALEA RAND()
RANDBETWEEN ALEA.ENTRE.BORNES RANDBETWEEN(min, max)
RANK RANG RANG(number,ref,order)
ROUND ARRONDI ROUND(number, num_digits)
ROUNDDOWN ARRONDI.INF ROUNDDOWN(number, num_digits)
ROUNDUP ARRONDI.SUP ROUNDUP(number, num_digits)
SIGN SIGNE SIGN(number)
SIN SIN SIN(number)
SINH SINH SINH(number)
SQRT RACINE SQRT(number)
SQRTPI RACINE.PI SQRTPI(number)
SUBTOTAL SOUS.TOTAL SUBTOTAL(function_num, ref1, ref2, …) if function_num is ³ 101 and £ 111 then generate a parameter error
SUM SOMME SUM(number1, [number2], [number3], [number4], …)
SUMIF SOMME.SI SUMIF(range, criteria, [sum_range]) range must have a number of elements <= to sum_range otherwise => #N/A! (if not, Excel completes range)
SUMIFS SOMME.SI.ENS SUMIFS(sum_range,[range1, criteria1], [range2, criteria2],…)
SUMPRODUCT SOMMEPROD SUMPRODUCT(array1,array2,array3, …) Unlike SUM, does not take strings into account. Calculations of the type SUMPRODUCT(Range1 operator Range2) are not allowed (example SUMPRODUCT(D1:D2+E1:E2/2)
SUMSQ SOMME.CARRES SUMSQ(number1,number2,…) Excel also counts booleans or text entered directly as arguments
SUMX2MY2 SOMME.X2MY2 SUMX2MY2(array_x,array_y) = sum (x^2 – y^2)
SUMX2PY2 SOMME.X2PY2 SUMX2PY2(array_x,array_y) = sum (x^2 + y^2)
SUMXMY2 SOMME.XMY2 SUMXMY2(array_x,array_y) = sum (x – y)^2
TAN TAN TAN(number)
TANH TANH TANH(number)
TRUNC TRONQUE TRUNC(number, num_digits)

Statistics

Name (EN) Name (FR) Syntax Remarks
AVEDEV ECART.MOYEN AVEDEV(number1,number2,…)
AVERAGE MOYENNE AVERAGE(number1,number2,…) Excel also counts booleans or text entered directly as arguments
AVERAGEA AVERAGEA AVERAGEA(number1,number2,…)
AVERAGEIf MOYENNE.SI AVERAGEIF(range,criteria,[av_range]) From version 3.9range must have a number of elements <= to av_range otherwise => #N/A! (if not, Excel completes range)
AVERAGEIFS MOYENE.SI.ENS AVERAGEIFS(av_range,rng1,crit1,rng2,crit2) From version 3.9
COUNT NB COUNT(value1,value2,…)
COUNTA NBVAL COUNTA(value1,value2,…)
COUNTBLANK NB.VIDE COUNTBLANK(range)
COUNTIF NB.SI COUNTIF(range,criteria)
COUNTIFS NB.SI.ENS COUNTIFS(range1, criteria1,range2, criteria2…) Function of Excel 2007 and above
COVAR COVARIANCE COVAR(array1,array2)
DEVSQ SOMME.CARRES.ECARTS DEVSQ(number1,number2,…)
MAX MAX MAX(number1,number2,…)
MAXA MAXA MAXA(number1,number2,…)
MEDIAN MEDIANE MEDIAN(number1,number2,…)
MIN MIN MIN(number1,number2,…)
MINA MINA MINA(number1,number2,…)
STDEV ECARTYPE STDEV(number1,number2,…)
STDEVP ECARTYPEP STDEVP(number1,number2,…)
VAR VAR VAR(number1,number2,…)
VARP VAR.P VARP(number1,number2,…)

Text

Name (EN) Name (FR) Syntax Remarks
CHAR CAR CHAR(number)
CLEAN EPURAGE CLEAN(text)
CODE CODE CODE(text)
CONCATENATE CONCATENER CONCATENATE (text1,text2,…)
EXACT EXACT EXACT(text1,text2)
FIND TROUVE FIND(find_text,within_text,start_num)
FIXED CTXT FIXED(number[,decimals,no_commas])
LEFT GAUCHE LEFT(text,num_chars)
LEN NBCAR LEN(text)
LOWER MINUSCULE LOWER(text)
MID STXT MID(text,start_num,num_chars)
PROPER NOMPROPRE PROPER(text)
REPLACE REMPLACER REPLACE(old_text,start_num,num_chars,new_text)
REPT REPT REPT(text,number_times)
RIGHT DROITE RIGHT(text,num_chars)
SEARCH CHERCHE SEARCH(find_text,within_text,start_num)
SUBSTITUTE SUBSTITUTE SUBSTITUTE(text,old_text,new_text,instance_num)
T T T(value)
TEXT TEXTE TEXT(value, format_text)
TRIM SUPPRESPACE TRIM(text)
UPPER MAJUSCULE UPPER(text)
VALUE CNUM VALUE(text)

Finance

Name (EN) Name (FR) Syntax Remarks
NPV VAN NPV(rate, value1, value2,…)
XNPV VAN.PAIEMENTS XNPV(rate, values, date)

 Named fields

When importing an Excel file into Design, the named fields are imported.

The following named fields are maintained (local or global)

  • reference (absolute)
  • value
  • formula without relative reference (except matrix formulas).

A name field can be used in graphs. The engine correctly handles the reference operator on the fields. In the table below, the field named “Quantity” is the data range C6:C8, the field named “CA” = D6:D8. In E6, the formula is “Quantity*CA”, which in this case is C6*D6.

The following fields are not imported:

  • hidden field
  • #REF!
  • invalid formula (for GT)

All global named fields are reimported at each Excel import, even those that are not in the import field. The list of named fields can be viewed (but not modified) via “Tools” –> “View Names”. Duplicates with the names generated in the Excel export are checked when saved in Design (only a warning). However, a collision is allowed if the name field and the component coincide.

Table function in Excel 2007 (and higher)

The Table function in Excel is not supported.

Was this article helpful?
0 out Of 5 Stars
5 Stars 0%
4 Stars 0%
3 Stars 0%
2 Stars 0%
1 Stars 0%
How can we improve this article?
Please submit the reason for your vote so that we can improve the article.
Previous Excel import: development considerations
Table of Contents