Supported Spreadsheet Functions | EnergySys Help Center

The platform's calculation engine executes Process logic workbooks server-side. It supports a defined set of Excel functions. Not every Excel function is available on the platform, and some functions may behave differently from their desktop Excel equivalents.
Only use functions that have been verified to work with the calculation engine. Functions outside the supported set may produce unexpected results or fail silently.

Array functions

Array functions are not recommended. Their behaviour and performance can vary between versions of the calculation engine, leading to inconsistent results. Where possible, build workbook logic using standard functions rather than array formulas.

Differences from desktop Excel

The calculation engine may interpret some functions differently from desktop Excel. This can affect calculation results, how certain data types are handled, or performance. Always test workbook logic against the platform's calculation engine, results that look right in desktop Excel can differ when executed server-side.

In particular, check date handling, number precision, and any function that depends on locale or system settings. See Process Logic Workbook for guidance on formatting requirements for output data.

Database functions

Function	Description
`DCOUNT`	Counts the cells containing numbers in a field (column) of records that match specified criteria.
`DSUM`	Adds the numbers in a field (column) of records that match the specified criteria.

Date and time functions

Function	Description
`DATE`	Returns the serial number for a given date.
`DATEDIF`	Returns the difference between two dates in years, months or days.
`DATEVALUE`	Converts a date stored as text to a serial number.
`DAY`	Returns the day of the month (1 to 31) for a date.
`DAYS`	Returns the number of days between two dates.
`EDATE`	Returns the date a given number of months before or after a start date.
`EOMONTH`	Returns the last day of the month a given number of months before or after a start date.
`HOUR`	Returns the hour (0 to 23) for a time value.
`MINUTE`	Returns the minute (0 to 59) for a time value.
`MONTH`	Returns the month (1 to 12) for a date.
`NETWORKDAYS`	Returns the number of working days between two dates, excluding weekends and any holidays you specify.
`NOW`	Returns the current date and time as a serial number.
`SECOND`	Returns the second (0 to 59) for a time value.
`TIME`	Returns the decimal value for a given hour, minute and second.
`TIMEVALUE`	Converts a time stored as text to a decimal value.
`TODAY`	Returns the current date as a serial number.
`WEEKDAY`	Returns the day of the week for a date as a number.
`YEAR`	Returns the year for a date as an integer.

Financial functions

Function	Description
`IRR`	Returns the internal rate of return for a series of cash flows.
`NPV`	Returns the net present value of an investment, based on a discount rate and a series of future payments and income.
`PRICE`	Returns the price per $100 face value of a security that pays periodic interest.

Information functions

Function	Description
`CELL`	Returns information about the formatting, location or contents of a cell.
`ISBLANK`	Returns TRUE if the cell is empty.
`ISERR`	Returns TRUE if the value is any error except #N/A.
`ISERROR`	Returns TRUE if the value is any error, including #N/A.
`ISLOGICAL`	Returns TRUE if the value is a logical value.
`ISNA`	Returns TRUE if the value is #N/A.
`ISNONTEXT`	Returns TRUE if the value is not text.
`ISNUMBER`	Returns TRUE if the value is a number.
`ISODD`	Returns TRUE if the number is odd.
`ISREF`	Returns TRUE if the value is a reference.
`ISTEXT`	Returns TRUE if the value is text.
`NA`	Returns the #N/A error value.
`TYPE`	Returns a number indicating the data type of a value.

Logical functions

Function	Description
`AND`	Returns TRUE if all arguments are TRUE, and FALSE if any argument is FALSE.
`FALSE`	Returns the logical value FALSE.
`IF`	Returns one value if a condition is TRUE and another if it is FALSE.
`IFERROR`	Returns a value you specify if a formula results in an error; otherwise returns the formula's result.
`IFNA`	Returns a value you specify if an expression results in #N/A; otherwise returns the expression's result.
`IFS`	Checks one or more conditions and returns the value for the first condition that is TRUE.
`NOT`	Reverses a logical value: TRUE becomes FALSE and FALSE becomes TRUE.
`OR`	Returns TRUE if any argument is TRUE, and FALSE if all arguments are FALSE.
`TRUE`	Returns the logical value TRUE.

Lookup and reference functions

Function	Description
`ADDRESS`	Returns a cell address as text, given row and column numbers.
`CHOOSE`	Returns a value from a list, based on an index number.
`COLUMN`	Returns the column number of a reference.
`COLUMNS`	Returns the number of columns in a range.
`HLOOKUP`	Looks for a value in the top row of a table and returns a value from the same column in a row you specify.
`INDEX`	Returns the value at a given row and column position in a range.
`INDIRECT`	Returns the reference specified by a text string.
`LOOKUP`	Looks up a value in a single row or column and returns the value from the same position in a second row or column.
`MATCH`	Returns the relative position of an item in a range that matches a specified value.
`OFFSET`	Returns a reference to a range a given number of rows and columns away from a starting cell or range.
`ROW`	Returns the row number of a reference.
`ROWS`	Returns the number of rows in a range.
`TRANSPOSE`	Converts a vertical range of cells to a horizontal one, or vice versa.
`VLOOKUP`	Looks for a value in the leftmost column of a table and returns a value from the same row in a column you specify.

Mathematical functions

Function	Description
`ABS`	Returns the absolute value of a number.
`EXP`	Returns e (2.71828…) raised to the power of a given number.
`INT`	Rounds a number down to the nearest integer.
`LN`	Returns the natural (base e) logarithm of a number.
`LOG`	Returns the logarithm of a number to the base you specify.
`MOD`	Returns the remainder after division.
`MROUND`	Rounds a number to the nearest multiple you specify.
`PI`	Returns the value of pi (3.14159265358979), accurate to 15 digits.
`POWER`	Returns a number raised to a given power.
`PRODUCT`	Multiplies all the numbers given as arguments.
`QUOTIENT`	Returns the integer portion of a division.
`RAND`	Returns a random number greater than or equal to 0 and less than 1. A new number is generated each time the workbook is calculated.
`RANDBETWEEN`	Returns a random integer between two numbers you specify. A new number is generated each time the workbook is calculated.
`ROUND`	Rounds a number to a given number of digits.
`ROUNDDOWN`	Rounds a number down, towards zero.
`ROUNDUP`	Rounds a number up, away from zero.
`SIGN`	Returns 1 if a number is positive, 0 if it is zero, and -1 if it is negative.
`SQRT`	Returns the positive square root of a number.
`SUBTOTAL`	Returns a subtotal in a list or database.
`SUM`	Adds all the numbers in a range of cells.
`SUMIF`	Adds the cells in a range that meet a single criterion.
`SUMIFS`	Adds the cells in a range that meet multiple criteria.
`SUMSQ`	Returns the sum of the squares of the arguments.
`TRUNC`	Truncates a number to an integer by removing its decimal part.

Statistical functions

Function	Description
`AVERAGE`	Returns the average (arithmetic mean) of its arguments.
`AVERAGEIF`	Returns the average of the cells in a range that meet a single criterion.
`AVERAGEIFS`	Returns the average of the cells that meet multiple criteria.
`COUNT`	Counts the cells that contain numbers, including dates and formulas that evaluate to numbers.
`COUNTA`	Counts the cells that are not empty.
`COUNTBLANK`	Counts the empty cells in a range.
`COUNTIF`	Counts the cells in a range that meet a single criterion.
`COUNTIFS`	Counts the cells in a range that meet multiple criteria.
`LARGE`	Returns the k-th largest value in a data set.
`MAX`	Returns the largest value in a set of values.
`MAXIFS`	Returns the largest value among cells that meet a given set of criteria.
`MIN`	Returns the smallest value in a set of values.
`MINIFS`	Returns the smallest value among cells that meet a given set of criteria.
`SMALL`	Returns the k-th smallest value in a data set.
`STDEVA`	Estimates standard deviation based on a sample, including text and logical values.

Text functions

Function	Description
`CHAR`	Returns the character for a given code number.
`CLEAN`	Removes all non-printable characters from text.
`CODE`	Returns the numeric code for the first character in a text string (the opposite of CHAR).
`CONCAT`	Joins text from multiple ranges and/or strings.
`EXACT`	Returns TRUE if two text strings are exactly the same (case-sensitive), and FALSE otherwise.
`FIND`	Returns the starting position of one text string within another (case-sensitive).
`FIXED`	Rounds a number to a given number of decimals and returns it as text, with optional thousands separators.
`LEFT`	Returns the first character(s) of a text string.
`LEN`	Returns the number of characters in a text string.
`LOWER`	Converts text to lowercase.
`MID`	Returns a given number of characters from a text string, starting at the position you specify.
`NUMBERVALUE`	Converts text to a number regardless of locale.
`PROPER`	Capitalises the first letter of each word in a text string.
`REPLACE`	Replaces part of a text string with different text, based on a given number of characters.
`REPT`	Repeats text a given number of times.
`RIGHT`	Returns the last character(s) of a text string.
`SEARCH`	Returns the starting position of one text string within another (not case-sensitive).
`SUBSTITUTE`	Replaces existing text with new text in a text string.
`T`	Returns the text a value refers to.
`TEXT`	Converts a value to text in a specified number format.
`TRIM`	Removes extra spaces from text, leaving single spaces between words.
`UNICODE`	Returns the number (code point) for the first character of a text string.
`UPPER`	Converts text to uppercase.
`VALUE`	Converts a text string that represents a number to a number.

Compatibility functions (pre-Excel 2010)

These functions are supported for compatibility with workbooks built in older versions of Excel.

Function	Description
`CONCATENATE`	Joins several text strings into one.
`FLOOR`	Rounds a number down, towards zero, to the nearest multiple of significance.
`FORECAST`	Predicts a future value from existing values, using linear regression.
`RANK`	Returns the rank of a number in a list of numbers.
`STDEV`	Estimates standard deviation based on a sample.