This article lists the functions you can use in Jedox Spreadsheets. Note that PALO functions are described in the article Jedox Data Functions Overview.
The functions are grouped as follows:
1. Jedox Spreadsheet Functions (without PALO functions)
2. Special Spreadsheet Functions
4. Functions that do not exist in Excel or that differ from those in Excel
Note: Jedox Spreadsheets do not support R1C1 reference style. Only the A1 style is supported.
1. Jedox Spreadsheet Functions (without PALO functions)
Most Jedox Spreadsheet functions work the same way as their corresponding Excel counterparts; see Microsoft's documentation for details. There are some exceptions, which are explained in the section below.
The table below lists all the implemented Jedox Spreadsheet functions. For details on Jedox PALO.DATA functions, see Jedox Data Functions Overview.
| ABS | ACOS | ACOSH | ACOT |
| ACOTH | ADDRESS | AND | ARABIC |
| AREAS | ASARRAY | ASIN | ASINH |
| ATAN | ATAN2 | ATANH | AVEDEV |
| AVERAGE | AVERAGEA | AVERAGEIFS | BASE |
| BESSELI | BESSELJ | BESSELK | BESSELY |
| BETA.DIST | BETA.INV | BETADIST | BINOM.DIST |
| BINOM.INV | CEILING | CELL | CHAR |
| CHISQ.DIST.RT | CHISQ.INV.RT | CHISQ.TEST | CHOOSE |
| CLEAN | CODE | COLUMN | COLUMNS |
| COMBIN | COMPLEX | CONCATENATE | CONCATENATE.range |
| CONFIDENCE.NORM | CONFIG.GET | CORREL | COS |
| COSH | COT | COTH | COUNT |
| COUNTA | COUNTBLANK | COUNTIF | COUNTIFS |
| COVARIANCE.P | CSC | CSCH | CURRENTUSER |
| DATE | DATEVALUE | DAVERAGE | DAY |
| DB | DCOUNT | DCOUNTA | DDB |
| DECIMAL | DEGREES | DELTA | DEVSQ |
| DGET | DMAX | DMIN | DPRODUCT |
| DSTDEV | DSTDEVP | DSUM | DVAR |
| DVARP | EDATE | ENCODEURL | EOMONTH |
| ERF | ERF.PRECISE | ERFC | ERFC.PRECISE |
| ERROR.MSG | ERROR.TYPE | EVEN | EXACT |
| EXP | EXPON.DIST | F.DIST.RT | F.INV.RT |
| F.TEST | FACT | FACTDOUBLE | FALSE |
| FILTERXML | FIND | FISHER | FISHERINV |
| FIXED | FLOOR | FORECAST | FORMULATEXT |
| FREQUENCY | FV | GAMMA.DIST | GAMMA.INV |
| GAMMALN | GCD | GEOMEAN | GESTEP |
| GROWTH | HARMEAN | HIDECOLUMN | HIDEROW |
| HLOOKUP | HOUR | HYPERLINK | HYPGEOM.DIST |
| HYPGEOMDIST | IF | IFERROR | IFNA |
| IFS | IMABS | IMAGINARY | IMARCCOS |
| IMARCCOSH | IMARCCOT | IMARCCOTH | IMARCCSC |
| IMARCCSCH | IMARCSEC | IMARCSECH | IMARCSIN |
| IMARCSINH | IMARCTAN | IMARCTANH | IMARGUMENT |
| IMCONJUGATE | IMCOS | IMCOSH | IMCOT |
| IMCOTH | IMCSC | IMCSCH | IMDIV |
| IMEXP | IMINV | IMLN | IMLOG10 |
| IMLOG2 | IMNEG | IMNORM | IMPOLAR |
| IMPOWER | IMPRODUCT | IMREAL | IMSEC |
| IMSECH | IMSIN | IMSINH | IMSQRT |
| IMSUB | IMSUM | IMTAN | IMTANH |
| INDEX | INDEX | INDIRECT | INFO |
| INT | INTERCEPT | IRR | ISBLANK |
| ISERR | ISERROR | ISEVEN | ISFORMULA |
| ISLOGICAL | ISNA | ISNONTEXT | ISNUMBER |
| ISODD | ISPMT | ISREF | ISTEXT |
| KURT | LARGE | LCM | LEFT |
| LEN | LINEST | LN | LOCALIZE |
| LOG | LOG10 | LOGEST | LOGNORM.DIST |
| LOGNORM.INV | LOGNORMDIST | LOOKUP | LOOKUP |
| LOWER | MATCH | MAX | MAXA |
| MAXIFS | MDETERM | MEDIAN | MID |
| MIN | MINA | MINIFS | MINUTE |
| MINVERSE | MIRR | MMULT | MOD |
| MODE.SNGL | MONTH | MROUND | MULTINOMIAL |
| MUNIT | N | NA | NEGBINOM.DIST |
| NEGBINOMDIST | NETWORKDAYS | NOEXPAND | NORM.DIST |
| NORM.INV | NORM.S.DIST | NORM.S.INV | NORMDIST |
| NOT | NOW | NPER | NPV |
| ODBCCOUNT | ODBCDATA | ODBCERROR | ODBCEXEC |
| ODBCINIT | ODD | OFFSET | OR |
| PEARSON | PERCENTILE.INC | PERCENTRANK.INC | PERMUT |
| PI | PMT | POISSON.DIST | POWER |
| PROB | PRODUCT | PRODUCTIFS | PROPER |
| PV | QUARTILE.INC | QUOTIENT | RADIANS |
| RAND | RANDBETWEEN | RANK.EQ | RATE |
| REGRESSION | REPLACE | REPT | RIGHT |
| ROMAN | ROUND | ROUNDDOWN | ROUNDUP |
| ROW | ROWS | RSQ | SEARCH |
| SEC | SECH | SECOND | SERIESSUM |
| SHEETNAME | SHOWPICT | SIGN | SIN |
| SINH | SKEW | SLN | SLOPE |
| SMALL | SQRT | SQRTPI | STANDARDIZE |
| STDEV.P | STDEV.S | STDEVA | STDEVIFS |
| STDEVPA | STDEVPIFS | STEYX | SUBSTITUTE |
| SUBTOTAL | SUM | SUMIF | SUMIFS |
| SUMPRODUCT | SUMSQ | SUMX2MY2 | SUMX2PY2 |
| SUMXMY2 | SYD | T | T.DIST.2T |
| T.DIST.RT | T.INV.2T | T.TEST | TAN |
| TANH | TDIST | TEXT | TIME |
| TIMEVALUE | TODAY | TRANSPOSE | TREND |
| TRIM | TRIMMEAN | TRUE | TRUNC |
| TYPE | UNFORMATTED | UPPER | USERGROUPS |
| USERLOCALE | VALUE | VAR.P | VAR.S |
| VARA | VARIFS | VARPA | VARPIFS |
| VLOOKUP | WEBSERVICE | WEEKDAY | WEEKNUM |
| WEIBULL.DIST | XIRR | XNPV | XOR |
| YEAR | Z.TEST |
2. Special Spreadsheet Functions
In addition to the Jedox/PALO functions in category Jedox (implemented in Jedox Web and in Jedox Excel Add-in), Jedox Spreadsheets (only in Jedox Web) have specially programmed functions for applications and for database access:
| Information Functions | |
|---|---|
| CONFIG.GET("Key") |
Returns the value of the specified key from the entries of Jedox Web Administration/Settings, e.g. =CONFIG.GET("studio.files.default_storage_path"). |
| CURRENTUSER() |
Returns the current user. |
| ERROR.MSG(cellreference) | This function returns a detailed error message for OLAP functions or macro functions when pointed to a cell containing such a function. Examples: Entry in A2: |
| SHEETNAME() |
Returns the name of the worksheet on which the formula is used. |
| USERGROUPS() |
Returns the groups of the current user. |
| USERLOCALE() |
Returns the language setting of the current user. |
| Text Functions | |
|---|---|
| CONCATENATE.range(cellrange,string_delimiter) | Concatenates entries of cellrange, separated with string_delimiter (optional). Note: This function is not supported in combination with DynaRanges. |
| LOCALIZE("Text") | Returns the translation of "Text" in the language which is used in Jedox Web if there exists the translation in localization tool. |
| UNFORMATTED(text) |
Returns the value that represents a particular formatted string. |
| Engineering Functions | ||||
|---|---|---|---|---|
| IMARCCOS | IMARCCOSH | IMARCCOT | IMARCCOTH |
IMARCCSC |
| IMARCCSCH | IMARCSEC | IMARCSECH | IMARCSIN | IMARCSINH |
| IMARCTAN | IMARCTANH | IMINV | IMNEG | IMNORM |
| IMPOLAR | IMTANH | |||
3. ODBC Functions
With ODBC functions you can read databases that have an ODBC interface.
Before you can import data from an ODBC database, you must register the database source name(DSN). In addition, the computer on which Jedox Web is installed must have an installation of the "Data Connectivity Components" from Microsoft (in a search engine you can find these components under the given name to download). For Linux installations, no additional components are needed.
You can create ODBC connections (using Windows System DSNs) in Connection Manager.
As an example, Jedox Setup installs the ODBC connection "DWH_Biker".
Under Tools - ODBC query you can access a SQL query editor with preview for the ODBC function ODBCEXEC. Only those ODBC connections that are established as such in Administration/Connections can be edited.
You can use ODBC results directly as source for Comboboxes, or display results comfortably in DynaRanges.
Function Category: Database
ODBCINIT(dsn,username,password)
Initializes a connection to a database using an existing system DSN(Database Source Name)
Note: Using empty strings for user and password will set them as default to the DSN defined user and password (where appropriate).
ODBCEXEC(odbcinit,sql_querytext)
Executes a SQL query through a previously initialized ODBC connection. Define the SQL statement using the syntax of the database accordingly. One or more ODBCEXEC functions can use the same ODBC connection in parallel.
| odbcinit | Cell of the ODBCINIT() function |
| sql_querytext | SQL query |
ODBCDATA(odbcexec,row_number,column_index)
The function returns the result of an executed SQL query. The first parameter is the cell address that contains the ODBCEXEC function. The result of ODBCEXEC is organized as an array of rows and columns. Use row_number “1” and column_index “1” to display the first value.
| odbcexec | Cell of the ODBCEXEC() function |
| row_number | Row of the result array |
| column_index | Column of the result array |
ODBCCOUNT(odbcexec)
Displays the number of rows returned by an executed SQL query.
| odbcexec | Cell of the ODBCEXEC() function |
ODBCERROR(odbcinit)
Displays the error code returned by an executed SQL query. In general, a positive value means success, a negative failure.
| odbcinit | Cell of the ODBCINIT() function |
4. Functions that do not exist in Excel or that differ from those in Excel
After an export the following Jedox Spreadsheet functions return #NAME? because they do not exist in Excel:
| ASARRAY | CONCATENATE.range | CONFIG.GET | CURRENTUSER |
| ERROR.MSG | IMARCCOS | IMARCCOSH | IMARCCOT |
| IMARCCOTH | IMARCCSC | IMARCCSCH | IMARCSEC |
| IMARCSECH | IMARCSIN | IMARCSINH | IMARCTAN |
| IMARCTANH | IMCSCH | IMINV | IMNEG |
| IMNORM | IMPOLAR | IMTANH | LOCALIZE |
| MAXIFS | MINIFS | NOEXPAND | ODBCCOUNT |
| ODBCDATA | ODBCERROR | ODBCEXEC | ODBCINIT |
| PRODUCTIFS | REGRESSION | SHEETNAME | SHOWPICT |
| STDEVIFS | STDEVPIFS | UNFORMATTED | USERGROUPS |
| USERLOCALE | VARIFS | VARPIFS |
The functions below differ from Excel functions.
| CELL() | The Jedox Spreadsheet function CELL(info_type,reference) has following differences to the corresponding Excel function:
|
| HIDECOLUMN() and HIDEROW() | The functions HIDECOLUMN and HIDEROW also do not exist in Excel, but they do not return #NAME. Instead they are converted to PALO.HIDECOLUMN and PALO.HIDEROW for internal reasons, but these functions are not supported in Excel. |
| HYPERLINK() | The Jedox HYPERLINK() function supports more arguments than in Excel. After export to Excel, the HYPERLINK function supports a maximum of two arguments. See HYPERLINK() Function for more information. |
| IFERROR() ISERROR() |
The functions IFERROR() and ISERROR() only check for the internal data type ERROR. A manually typed string in a cell (like "#VALUE!") will not be recognized as an error. DynaRanges receive errors as a normal string from Spreadsheet Server. As such, the data type for the error has changed and is therefore no longer recognized as an error by these functions. |
| INFO() | The Jedox INFO() function has partially the same arguments as in Excel, but some arguments don't exist in Excel. If the exported argument of the INFO function doesn’t exist in Excel, you will receive #VALUE! in the corresponding cell. See INFO() Function for more information. |
| REGRESSION() | This function does not exist in Excel, but the syntax is similar to TREND() or GROWTH() functions: REGRESSION(reg_type, known_y's, [known_x's], [new_x's], [const]) The only difference is the first parameter, which is a numerical representation of the regression type. Supported regression types and their corresponding numbers are 1 - linear |
| SUBTOTAL() | SUBTOTAL function in Excel has a range of subfunctions (1xx) that allow values in hidden rows or columns to be ignored. This behavior is supported in Jedox Web for rows or columns that are dynamically hidden using the HIDEROW / HIDECOLUMN functions. However, when hiding a row or column statically (via the row / column context menu), the SUBTOTAL result will only change after some other change to the sheet content has been made. |
5. Supported data types in the SQL server
The following list shows whether and how a SQL data type will be transferred to Jedox Spreadsheet:
| SQL Data Type | Jedox Spreadsheet |
| Bigint | number |
| Binary(n) | string |
|
Bit |
boolean |
| char(n) | string |
|
Date |
string |
|
Datetime |
number |
| Datetime2(n) | string |
|
datetimeoffset(7) |
string |
| decimal(n, decimals) | number |
| float | number |
| geography | not supported; cast it to string in SQL queries |
| geometry | not supported; cast it to string in SQL queries |
| hierarchyid | not supported; cast it to string in SQL queries |
| Image | string |
| Int | number |
| Money | number |
| Nchar(n) | string |
| Ntext | string |
| numeric(n, decimals) | numeric |
| nvarchar(n) | string |
| nvarchar(MAX) | string, max. size of 64000 |
| Real | numeric |
| Smalldatetime | number |
| Smallint | numeric |
| Smallmoney | numeric |
| sql_variant | not supported; cast it to string in SQL queries |
| Text | not supported; cast it to string in SQL queries |
| Time(n) | not supported; cast it to string in SQL queries |
| Timestamp | string |
| Tinyint | number |
| Uniqueidentifier | not supported; cast it to string in SQL queries |
| varbinary(n) | string |
| varbinary(MAX) | string, max. size of 64000 |
| varchar(n) | string |
| varchar(NAX) | string, max. size of 64000 |
| Xml | not supported; cast it to string in SQL queries |
Updated November 4, 2024