Formula Functions — ReoGrid Docs

ReoGrid provides the following built-in formula functions that are compatible with Microsoft Excel.

47 new Excel-compatible functions were added in v4.5. Newly added functions are marked with 🆕.

Statistics

Function	Description
`SUM(range)`	Sum of all values in a range
`SUMIF(range, criteria, [sum_range])`	Sum values that meet a condition
`SUMIFS(sum_range, range1, criteria1, ...)`	Sum values that meet multiple conditions 🆕
`AVERAGE(range)`	Average of all values in a range
`AVERAGEIF(range, criteria, [avg_range])`	Average of values that meet a condition
`AVERAGEIFS(avg_range, range1, criteria1, ...)`	Average of values that meet multiple conditions 🆕
`COUNT(range)`	Count of numeric values in a range
`COUNTA(range)`	Count of non-empty cells in a range
`COUNTIF(range, criteria)`	Count of cells that meet a condition
`COUNTIFS(range1, criteria1, ...)`	Count of cells that meet multiple conditions 🆕
`MIN(range)`	Minimum value in a range
`MAX(range)`	Maximum value in a range
`MEDIAN(range)`	Median value in a range 🆕
`RANK(number, range, [order])`	Rank of a value within a range 🆕
`LARGE(range, k)`	k-th largest value 🆕
`SMALL(range, k)`	k-th smallest value 🆕
`STDEV(range)`	Sample standard deviation 🆕
`STDEVP(range)`	Population standard deviation 🆕
`VAR(range)`	Sample variance 🆕
`VARP(range)`	Population variance 🆕

Conditions

Function	Description
`IF(condition, true_value, false_value)`	Return value based on condition
`IFS(condition1, value1, ...)`	Evaluate conditions in order and return the first one that is true 🆕
`IFERROR(value, value_if_error)`	Return an alternative value when the value is an error 🆕
`AND(value1, value2, ...)`	True if all arguments are true
`OR(value1, value2, ...)`	True if any argument is true
`NOT(value)`	Reverse a boolean value

Lookup & Reference

Function	Description
`VLOOKUP(value, range, col, [exact])`	Vertical lookup in a table
`HLOOKUP(value, range, row, [exact])`	Horizontal lookup in a table
`XLOOKUP(value, lookup_range, return_range)`	Extended lookup (flexible search)
`MATCH(value, range, [match_type])`	Find position of a value in a range
`XMATCH(value, range)`	Extended match
`INDEX(range, row, [col])`	Value at a given position within a range 🆕
`OFFSET(reference, rows, cols, [height], [width])`	A reference offset from a base cell 🆕
`CHOOSE(index, value1, value2, ...)`	Return the value at the given index 🆕
`ADDRESS(row, col, [abs_type])`	Create a cell address string
`INDIRECT(address)`	Reference a cell by address string
`ROW([cell])`	Row number of a cell
`COLUMN([cell])`	Column number of a cell
`CELL(info, [cell])`	Information about a cell
`VALUE(text)`	Convert text to a number

See VLOOKUP for a detailed VLOOKUP tutorial.

Math

Function	Description
`ABS(number)`	Absolute value
`SIGN(number)`	Sign of a number (1 if positive, 0 if zero, -1 if negative) 🆕
`INT(number)`	Integer part (rounded down to the nearest integer) 🆕
`TRUNC(number, [digits])`	Truncate the decimal part 🆕
`ROUND(number, digits)`	Round to specified decimal places
`ROUNDUP(number, digits)`	Round up to the specified number of digits 🆕
`ROUNDDOWN(number, digits)`	Round down to the specified number of digits 🆕
`CEILING(number, significance)`	Round up to nearest multiple
`FLOOR(number, significance)`	Round down to nearest multiple
`MOD(number, divisor)`	Remainder of division
`POWER(base, exponent)`	Number raised to a power
`SQRT(number)`	Square root 🆕
`SUMPRODUCT(range1, range2, ...)`	Sum of the products of corresponding elements 🆕
`PI()`	The constant π 🆕
`EXP(number)`	e raised to a power
`LOG(number, [base])`	Logarithm
`LOG10(number)`	Base-10 logarithm
`SIN(radians)`	Sine
`COS(radians)`	Cosine
`TAN(radians)`	Tangent
`ASIN(number)`	Arcsine
`ACOS(number)`	Arccosine
`ATAN(number)`	Arctangent
`ATAN2(x, y)`	Arctangent of x/y

Date & Time

Function	Description
`NOW()`	Current date and time
`TODAY()`	Current date
`DATE(year, month, day)`	Create a date from year, month, and day 🆕
`DATEVALUE(text)`	Convert a date string to a date (serial) value 🆕
`TIME(hour, minute, second)`	Create a time value
`TIMEVALUE(text)`	Convert a time string to a time value 🆕
`YEAR(date)`	Year from a date
`MONTH(date)`	Month from a date
`DAY(date)`	Day from a date
`HOUR(time)`	Hour from a time
`MINUTE(time)`	Minute from a time
`SECOND(time)`	Second from a time
`MILLISECOND(time)`	Millisecond from a time
`WEEKDAY(date, [type])`	Day-of-week number for a date 🆕
`WEEKNUM(date, [type])`	Week number for a date 🆕
`DAYS(end_date, start_date)`	Number of days between two dates
`EDATE(date, months)`	Date a given number of months before or after 🆕
`EOMONTH(date, months)`	Last day of the month a given number of months before or after 🆕
`NETWORKDAYS(start, end, [holidays])`	Number of working days, excluding weekends and holidays 🆕
`WORKDAY(start, days, [holidays])`	Date a given number of working days later 🆕

String

Function	Description
`LEFT(text, count)`	Leftmost characters
`RIGHT(text, count)`	Rightmost characters
`MID(text, start, count)`	Substring from a position
`UPPER(text)`	Convert to uppercase
`LOWER(text)`	Convert to lowercase
`LEN(text)`	Length of text
`FIND(find_text, within_text, [start])`	Find text position
`TRIM(text)`	Remove leading/trailing spaces
`TEXT(value, format)`	Convert a number or date to a formatted string 🆕
`SUBSTITUTE(text, old, new, [instance])`	Replace occurrences of a substring 🆕
`REPLACE(text, start, count, new)`	Replace text at a specific position 🆕
`CONCAT(text1, text2, ...)`	Join multiple strings 🆕
`CONCATENATE(text1, text2, ...)`	Join multiple strings (same as `CONCAT`) 🆕
`TEXTJOIN(delimiter, ignore_empty, text1, ...)`	Join strings with a delimiter 🆕

Double-byte Text (CJK) 🆕

Primarily used for full-width / half-width conversion and phonetic (furigana) retrieval in CJK text (added in v4.5).

Function	Description
`ASC(text)`	Convert full-width (double-byte) characters to half-width (single-byte) 🆕
`JIS(text)`	Convert half-width (single-byte) characters to full-width (double-byte) 🆕
`DBCS(text)`	Convert half-width characters to full-width (same as `JIS`) 🆕
`PHONETIC(reference)`	Get the phonetic (furigana) reading of a string (simplified) 🆕

Information & Error Checking

Function	Description
`ISERROR(value)`	True if value is an error
`ISNA(value)`	True if value is the `#N/A` error 🆕
`ISNUMBER(value)`	True if value is a number
`ISTEXT(value)`	True if value is text 🆕
`ISNONTEXT(value)`	True if value is not text 🆕
`ISLOGICAL(value)`	True if value is a boolean 🆕
`ISBLANK(cell)`	True if cell is empty
`ISEVEN(number)`	True if the number is even 🆕
`ISODD(number)`	True if the number is odd 🆕

ReoGrid-Specific Functions

These functions are available only in ReoGrid, primarily used with conditional styles and data source scenarios:

Function	Description
`COLVAL(column)`	Get value from a column in the current data source row
`COLVAL_OFFSET(column, offset)`	Get value from a column with offset
`CIDVAL(column_id)`	Get value by column identifier
`ROWVAL(row)`	Get value from a row
`ROWVAL_OFFSET(row, offset)`	Get value from a row with offset

Localization

ReoGrid supports localized function names. Use FormulaExtension to switch:

// Use standard English function names (default)
FormulaExtension.ChangeToStandardFunctionNames();

// Use Russian function names
FormulaExtension.ChangeToRussianFunctionNames();

Custom Functions

To add custom functions, see Custom Function.

Formula Overview — Formula basics and cell references
Custom Function — Creating custom functions
VLOOKUP — VLOOKUP tutorial