Spreadsheet Editor Formulas

Requirements

These packages are required to use these features in production. Trial keys have unlimited access to all features

Web SDKPackage: Spreadsheet EditorLive demo

Formulas are expressions that calculate values in cells. Supported formulas and functions in Spreadsheet Editor are consistent with Excel, including:

• Standard mathematical operations
• Statistical calculations
• Date/time functions
• Other common formulas

For examples of how to programmatically use formulas, see the Edit Mode guide's Formula Bar section.

The following formulas are supported:

Formula	Description
ABS	Absolute value of a number.
ACOS	Inverse cosine of a value, in radians.
ACOSH	Inverse hyperbolic cosine of a number.
ADDRESS	Cell reference as a string.
AND	Logical and operator.
ARABIC	Computes the value of a Roman numeral.
ASIN	Inverse sine of a value, in radians.
ASINH	Inverse hyperbolic sine of a number.
ATAN	Inverse tangent of a value, in radians.
ATAN2	Arctangent of a value.
ATANH	Inverse hyperbolic tangent of a number.
AVERAGE	Numerical average value in a dataset, ignoring text.
AVERAGEIF	Average of values depending on criteria.
AVERAGEIFS	Average of values depending on multiple criteria.
BIN2DEC	Converts a signed binary number to decimal format.
BIN2HEX	Converts a binary number to hexadecimal.
BIN2OCT	Converts a binary number to octal.
BINOM_DIST	Binomial distribution probability.
CEILING	Rounds number up to nearest multiple of a factor.
CELL	Gets information about a cell.
CHAR	Gets character associated with number.
CHOOSE	An element from a list of choices based on index.
CLEAN	Removes non-printable characters from a piece of text.
CODE	Numeric unicode map value of character.
COLUMN	Column number of a specified cell.
COLUMNS	Number of columns in a specified array or range.
COMBIN	Number of combinations from a set of objects.
COMPLEX	Creates a complex number.
CONCAT	Concatenation of two values.
CONCATENATE	Appends strings to one another.
CONVERT	Unit conversion for numbers.
COS	Cosine of an angle provided in radians.
COSH	Hyperbolic cosine of any real number.
COUNT	The number of numeric values in dataset.
COUNTA	The number of values in a dataset.
COUNTBLANK	Number of empty values.
COUNTIF	A conditional count across a range.
COUNTIFS	Count values depending on multiple criteria.
COUNTUNIQUE	Counts number of unique values in a range.
COUPDAYBS	Number of days from first coupon to settlement.
COUPDAYS	Days in coupon period containing settlement date.
COUPNUM	Number of coupons between settlement and maturity.
CUMIPMT	Cumulative interest paid over a set of periods.
CUMPRINC	Cumulative principal paid over a set of periods.
DATE	Converts year/month/day into a date.
DATEDIF	Date difference.
DATEVALUE	Converts a date string to a date value.
DAVERAGE	Average of a set of values from a table-like range.
DAY	Day of the month that a specific date falls on.
DAYS	Number of days between two dates.
DAYS360	Days between two dates on a 360-day year.
DB	Depreciation via declining balance method.
DCOUNT	Counts values from a table-like range.
DCOUNTA	Counts values and text from a table-like range.
DDB	Depreciation via double-declining balance method.
DEGREES	Converts an angle value in radians to degrees.
DELTA	Compare two numeric values.
DEVSQ	The sum of squares of deviations based on a sample.
DGET	Single value from a table-like range.
DISC	The discount rate of a security based on price.
DISCRETESPLINES	Generate sparklines discrete graph.
DMAX	Maximum of values from a table-like range.
DMIN	Minimum of values from a table-like range.
DOLLAR	Formats a number as currency specific to your spreadsheet locale.
DOLLARDE	Converts a decimal fraction to decimal value.
DOLLARFR	Converts a decimal value to decimal fraction.
DPRODUCT	Product of values from a table-like range.
DSTDEV	Standard deviation of population sample from table.
DSTDEVP	Standard deviation of entire population from table.
DSUM	Sum of values from a table-like range.
DURATION	Number of periods for an investment to reach a value.
DVAR	Variance of population sample from table-like range.
DVARP	Variance of a population from a table-like range.
EDATE	Date a number of months before/after another date.
EFFECT	Annual effective interest rate.
EOMONTH	Last day of a month before or after a date.
ERFC	Complementary gauss error function of a value.
EVEN	Rounds a number up to the nearest even integer.
EXACT	Tests whether two strings are identical.
EXP	Euler's number, e (~2.718) raised to a power.
FACT	Factorial of a number.
FACTDOUBLE	"double factorial" of a number.
FALSE	Logical value false.
FILTER	Filters a range based off provided conditions.
FIND	First position of string found in text, case-sensitive.
FINDB	Position at which a string is first found within text (binary).
FISHER	Fisher transformation of a specified value.
FISHERINV	Inverse fisher transformation of a specified value.
FIXED	Formats number with fixed number of decimal places.
FORECAST	Expected y-value based of linear regression.
FREQUENCY	The frequency distribution of array.
FV	Future value of an annuity investment.
FVSCHEDULE	Future value of principal from series of rates.
GAMMALN	Logarithm of gamma function.
GCD	Greatest common divisor of one or more integers.
GEOMEAN	The geometric mean of a dataset.
GETPIVOTDATA	Extracts an aggregated value from a pivot table that corresponds to the specified row and column headings.
GROWTH	Fits points to exponential growth trend.
HARMEAN	The harmonic mean of a dataset.
HEX2BIN	Converts a hexadecimal number to binary.
HEX2DEC	Converts a hexadecimal number to decimal.
HEX2OCT	Converts a hexadecimal number to octal.
HLOOKUP	Horizontal lookup
HOUR	Hour component of a specific time.
HYPGEOMDIST	Hypergeometric distribution probability.
IF	Returns value depending on logical expression.
IFERROR	Value if it is not an error, otherwise 2nd argument.
IMABS	The absolute value of a complex number.
IMAGINARY	The imaginary coefficient of a complex number.
IMCONJUGATE	The complex conjugate of a number.
IMDIV	One complex number divided by another.
IMPRODUCT	Result of multiplying a series of complex numbers together.
IMREAL	The real coefficient of a complex number.
IMSUB	The difference between two complex numbers.
IMSUM	Sum of a series of complex numbers.
INDEX	Content of cell specified by row and column offset.
INDIRECT	A cell reference specified by a string.
INT	Rounds number down to nearest integer.
INTRATE	Calculates effective interest rate.
IPMT	Payment on interest for an investment.
IRR	Internal rate of return given periodic cashflows.
ISBLANK	Whether the referenced cell is empty.
ISDATE	Whether a value is a date.
ISERR	Whether a value is an error other than #n/a.
ISERROR	Whether a value is an error.
ISEVEN	Whether the provided value is even.
ISFORMULA	Whether a value is a formula.
ISLOGICAL	Whether a value is true or false.
ISNA	Whether a value is the error #n/a.
ISNONTEXT	Whether a value is non-textual.
ISNUMBER	Whether a value is a number.
ISODD	Whether the provided value is odd.
ISOWEEKNUM	ISO week number of the year.
ISREF	Whether a value is a valid cell reference.
ISTEXT	Whether a value is text.
JOIN	Concatenates elements of arrays with delimiter.
KURT	Kurtosis of a dataset.
LARGE	Nth largest element from a data set.
LCM	Least common multiple of one or more integers.
LEFT	Substring from beginning of specified string.
LEN	Length of a string.
LENB	Length of a string in bytes.
LINEST	Best-fit linear trend via least-squares.
LN	The logarithm of a number, base e (euler's number).
LOG	The logarithm of a number with respect to a base.
LOG10	The logarithm of a number, base 10.
LOGEST	Best-fit exponential growth curve.
LOOKUP	Look up a value.
LOWER	Converts a specified string to lowercase.
MATCH	Position of item in range that matches value.
MAX	Maximum value in a numeric dataset.
MAXA	Maximum numeric value in a dataset.
MDETERM	Matrix determinant of a square matrix.
MDURATION	Modified Macaulay duration.
MEDIAN	Median value in a numeric dataset.
MIN	Minimum value in a numeric dataset.
MINA	Minimum numeric value in a dataset.
MINUTE	Minute component of a specific time.
MINVERSE	Multiplicative inverse of square matrix.
MIRR	Modified internal rate of return.
MMULT	The matrix product of two matrices.
MOD	Modulo (remainder) operator.
MONTH	Month of the year a specific date falls in.
MROUND	Rounds a number to the nearest integer multiple.
MULTINOMIAL	Multinomial distribution function.
N	Argument provided as a number.
NA	The #N/A error.
NETWORKDAYS	Net working days between two provided days.
NOMINAL	Annual nominal interest rate.
NOT	Returns opposite of provided logical value.
NOW	Current date and time as a date value.
NPER	Number of payment periods for an investment.
NPV	The net present value of an investment based on a series of periodic cash flows and a discount rate.
OCT2BIN	Converts an octal number to binary.
OCT2DEC	Converts a signed octal number to decimal format.
OCT2HEX	Converts an octal number to hexadecimal.
ODD	Rounds a number up to the nearest odd integer.
OFFSET	A range reference offset relative to a cell.
OR	Logical or operator.
PEARSON	Pearson Product-Moment Correlation Coefficient.
PERCENTRANK_INC	Percentage rank (percentile) from 0 to 1 inclusive.
PERMUT	Number of permutations from a number of objects.
PI	The number pi.
PMT	Periodic payment for an annuity investment.
POWER	A number raised to a power.
PRICE	Price of a security paying periodic interest.
PRICEDISC	Price of a discount security.
PRICEMAT	Price of security paying interest at maturity.
PROB	Probability values lie in a range.
PRODUCT	Result of multiplying a series of numbers together.
PROPER	Capitalizes each word in a specified string.
PV	Present value of an annuity investment.
QUOTIENT	One number divided by another.
RADIANS	Converts an angle value in degrees to radians.
RAND	A random number between 0 inclusive and 1 exclusive.
RANDARRAY	Returns a grid of random numbers.
RANDBETWEEN	Random integer between two values, inclusive.
RATE	Interest rate of an annuity investment.
RECEIVED	Amount received at maturity for a security.
REGEXEXTRACT	Extracts matching substrings with regular expression.
REGEXMATCH	Whether a piece of text matches regular expression.
REGEXREPLACE	Replaces text with regular expressions.
REPLACE	Replaces part of a text string with different text.
REPT	Specified text repeated a number of times.
RIGHT	A substring from the end of a specified string.
ROMAN	Formats a number in Roman numerals.
ROUND	Rounds a number according to standard rules.
ROUNDDOWN	Rounds down a number.
ROUNDUP	Rounds up a number.
ROW	Row number of a specified cell.
ROWS	Number of rows in a specified array or range.
RSQ	Square of the correlation coefficient.
SEARCH	First position of string found in text, ignoring case.
SECOND	Second component of a specific time.
SEQUENCE	Returns a grid of sequential numbers.
SERIASESSUM	Sum of a power series.
SIGN	Sign of a provided number (+/-/0).
SIN	Sine of an angle provided in radians.
SINH	Hyperbolic sine of any real number.
SKEW	Skewness of a dataset.
SLN	Depreciation of asset using the straight-line method.
SLOPE	Slope of line from linear regression of data.
SMALL	Nth smallest element in a data set.
SORT	Sorts rows of range by specified column.
SQRT	Positive square root of a positive number.
SQRTPI	Square root of the product of pi and number.
STANDARDIZE	Normalized equivalent of a random variable.
STDEVA	Standard deviation of sample (text as 0).
STDEVP	Standard deviation of an entire population.
STEYX	Standard error of predicted y-values in regression.
SUBSTITUTE	Replaces existing text with new text in a string.
SUBTOTAL	Subtotal for a range using a specific function.
SUM	Sum of a series of numbers and/or cells.
SUMIF	A conditional sum across a range.
SUMIFS	Sums a range depending on multiple criteria.
SUMPRODUCT	Sum of products of elements in two arrays.
SUMSQ	Sum of squares.
SUMX2MY2	Sum of the differences of squares.
SUMX2PY2	Sum of the sums of squares.
SUMXMY2	Sum of the squares of differences.
T	String arguments as text.
TAN	Tangent of an angle provided in radians.
TANH	Hyperbolic tangent of any real number.
TBILLEQ	Equivalent rate of return for a Treasury bill.
TBILLPRICE	Price of US treasury bill.
TBILLYIELD	The yield of a US treasury bill based on price.
TEXT	Formats a number into text.
TIME	Converts hour/minute/second into a time.
TIMEVALUE	onverts a time string into its serial number representation.
TO_DATE	Converts a provided number to a date.
TO_DOLLARS	Converts a provided number to a dollar value.
TO_PERCENT	Converts a provided number to a percentage.
TO_PURE_NUMBER	Converts any numeric value to a pure number.
TO_TEXT	Converts a provided numeric value to a text value.
TODAY	Current date as a date value.
TRANSPOSE	Transposes the rows and columns of an array.
TREND	Fits points to linear trend derived via least-squares.
TRIM	Removes space characters.
TRIMMEAN	Mean of a dataset excluding high/low ends.
TRUE	Logical value true.
TRUNC	Truncates a number.
TYPE	Get the type of a value.
UNIQUE	Unique rows in the provided source range.
UPPER	Converts a specified string to uppercase.
VALUE	Converts a date/time/number string into a number.
VARA	Variance of sample (text as 0).
VARPA	Variance of entire population (text as 0).
VLOOKUP	Vertical lookup.
WEEKDAY	Day of the week of the date provided (as number).
WEEKNUM	Week number of the year.
WORKDAY	Number of working days from start date.
XIRR	Internal rate of return given non-periodic cashflows.
XNPV	Net present value given non-periodic cashflows.
YEAR	Year specified by a given date.
YEARFRAC	Exact number of years between two dates.
YIELD	Annual yield of a security paying periodic interest.
YIELDDISC	Annual yield of a discount security.