# Ants & Apps functions reference

Isavailable in Ants & Apps?

Ants & Apps supports Google Sheets / Excel-like functions as well as some programming and proprietary Ants & Apps ones. Functions can be used to create formulas that manipulate data (including lists and arrays), calculate numbers and strings.

Here is a list of all Ants & Apps functions available in each category. Please remember to add quotation marks around all function components made of alphabetic characters that aren't refeering to cells or columns.

• ## Math & Trigonometry Functions

• ABS

Returns the absolute value of a number.

• ACOS

The ACOS function returns the inverse cosine of a value in radians.

• ACOSH

The ACOSH function returns the inverse hyperbolic cosine of a number.

• ACOT

The ACOT function returns the inverse cotangent of a value in radians.

• ACOTH

The ACOTH function returns the inverse hyperbolic cotangent of a value in radians.

• AGGREGATE

Returns an aggregate in a list or database

• ARABIC

Converts a Roman number to Arabic, as a number

• ASIN

The ASIN function returns the inverse sine of a value in radians.

• ASINH

The ASINH function returns the inverse hyperbolic sine of a number.

• ATAN

The ATAN function returns the inverse tangent of a value in radians.

• ATAN2

The ATAN2 function returns the angle between the x-axis and a line segment from the origin (0,0) to the specified coordinate pair (`x`,`y`), in radians.

• ATANH

The ATANH function returns the inverse hyperbolic tangent of a number.

• BASE

Converts a number into a text representation with the given radix (base)

• BESSELI

Calculates the modified Bessel functions

• BESSELJ

Returns the Bessel function

• BESSELK

Calculates the modified Bessel functions. Kn(x), which are also known as the hyperbolic Bessel Functions

• BESSELY

Returns the Bessel function, Yn(x), (also known as the Weber function or the Neumann function), for a specified order and value of x.

• CEIL

Round fractions up

• CEILING

The CEILING function rounds a number up to the nearest integer multiple of specified significance.

• CEILING_MATH

Rounds a number up, to the nearest integer or to the nearest multiple of significance

• CEILING_PRECISE

Rounds a number the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up.

• COMBIN

The COMBIN function returns the number of ways to choose some number of objects from a pool of a given size of objects.

• COMBINA

Returns the number of combinations with repetitions for a given number of items

• COS

The COS function returns the cosine of an angle provided in radians.

• COSH

The COSH function returns the hyperbolic cosine of any real number.

• COT

The COT function returns the cotangent of an angle provided in radians.

• COTH

The COTH function returns the hyperbolic cotangent of any real number.

• COUNTIFS

Returns the count of a range depending on multiple criteria.

• CSC

The CSC function returns the cosecant of an angle provided in radians.

• CSCH

The CSCH function returns the hyperbolic cosecant of any real number.

• DECIMAL

Converts a text representation of a number in a given base into a decimal number

Converts the number in degrees to the radian equivalent

• DEGREES

The DEGREES function converts an angle value in radians to degrees.

• DIVIDE

Divide number

• ERFC_PRECISE

Returns the complementary Gauss error function of a value.

• EVEN

Rounds a number up to the nearest even integer.

• EXP

Returns Euler's number, e (~2.718) raised to a power.

• FACT

The FACT function returns the factorial of a number.

• FACTDOUBLE

Returns the "double factorial" of a number.

• FLOOR

The FLOOR function rounds a number down to the nearest integer multiple of specified significance.

• FLOOR_MATH

Rounds a number down, to the nearest integer or to the nearest multiple of significance

• FLOOR_PRECISE

Rounds a number the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up.

• FMOD

Returns the floating point remainder (modulo) of the division of the arguments

• GCD

Returns the greatest common divisor of one or more integers.

• GT

Returns `TRUE` if the first argument is strictly greater than the second, and `FALSE` otherwise. Equivalent to the `>` operator.

• GTE

Returns `TRUE` if the first argument is greater than or equal to the second, and `FALSE` otherwise. Equivalent to the `>=` operator.

• HYPOT

Calculate the length of the hypotenuse of a right-angle triangle

• IF_NUMBER_EQUAL

Determines whether the first number is equal with second

• IF_NUMBER_GREATER

Determines whether the first number is greater than second

• INT

Rounds a number down to the nearest integer that is less than or equal to it.

• IS_FINITE

Finds whether a value is a legal finite number

• IS_INFINITE

Finds whether a value is infinite

• IS_NAN

Finds whether a value is not a number

• ISO_CEILING

Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance

• LCM

Returns the least common multiple of one or more integers.

• LN

Returns the the logarithm of a number, base e (Euler's number).

• LOG

Returns the the logarithm of a number given a base.

• LOG10

Returns the the logarithm of a number, base 10.

• LT

Returns `TRUE` if the first argument is strictly less than the second, and `FALSE` otherwise. Equivalent to the `<` operator.

• LTE

Returns `TRUE` if the first argument is less than or equal to the second, and `FALSE` otherwise. Equivalent to the `<=` operator.

• MAKE_NEGATIVE

Transforms a number to a negative, even if it is negative

• MAKE_POSITIVE

Transforms a number to a positive, even if it is positive

• MDETERM

Returns the matrix determinant of a square matrix specified as an array or range.

• MINVERSE

Returns the multiplicative inverse of a square matrix specified as an array or range.

• MMULT

Calculates the matrix product of two matrices specified as arrays or ranges.

• MOD

Returns the result of the modulo operator, the remainder after a division operation.

• MROUND

Rounds one number to the nearest integer multiple of another.

• MT_RAND

Generate a random value via the Mersenne Twister Random Number Generator

• MULTINOMIAL

Returns the factorial of the sum of values divided by the product of the values' factorials.

• MULTIPLY

Multiply numbers

• MUNIT

Returns the unit matrix or the specified dimension

• ODD

Rounds a number up to the nearest odd integer.

• PI

The PI function returns the value of pi to 9 decimal places.

• POW

Exponential expression

• POWER

Returns a number raised to a power.

• PRODUCT

Returns the result of multiplying a series of numbers together.

• QUOTIENT

Returns one number divided by another, without the remainder.

Converts the radian number to the equivalent number in degrees

• RAND

Returns a random number between 0 inclusive and 1 exclusive.

• RANDARRAY

Returns an array of random numbers between 0 and 1. However, you can specify the number of rows and columns to fill, minimum and maximum values, and whether to return whole numbers or decimal values.

• RANDBETWEEN

Returns a uniformly random integer between two values, inclusive.

• ROMAN

Formats a number in Roman numerals.

• ROUND

The ROUND function rounds a number to a certain number of decimal places according to standard rules.

• ROUNDDOWN

The ROUNDDOWN function rounds a number to a certain number of decimal places, always rounding down to the next valid increment.

• ROUNDUP

Rounds a number to a certain number of decimal places, always rounding up to the next valid increment.

• SEC

The SEC function returns the secant of an angle measured in radians.

• SECH

The SECH function returns the hyperbolic secant of an angle.

• SEQUENCE

Generates a list of sequential numbers in an array, such as 1, 2, 3, 4

• SERIESSUM

Given parameters x, n, m, and a, returns the power series sum a1xn + a2x(n+m) + ... + aix(n+(i-1)m), where i is the number of entries in range `a`.

• SIGN

Given an input number, returns `-1` if it is negative, `1` if positive, and `0` if it is zero.

• SIN

The SIN function returns the sine of an angle provided in radians.

• SINH

The SINH function returns the hyperbolic sine of any real number.

• SQRT

Returns the positive square root of a positive number.

• SQRTPI

Returns the positive square root of the product of Pi and the given positive number.

• SUBSTRACT

Substract numbers

• SUBTOTAL

Returns a subtotal for a vertical range of variables using a specified aggregation function.

• SUM

Returns the sum of a series of numbers and/or variables.

• SUMIF

Returns a conditional sum across a range.

• SUMPRODUCT

The SUMPRODUCT function calculates the sum of the products of corresponding entries in 2 equally sized arrays or ranges.

• SUMSQ

Returns the sum of the squares of a series of numbers and/or variables.

• SUMX2MY2

Calculates the sum of the differences of the squares of values in two arrays.

• SUMX2PY2

Calculates the sum of the sums of the squares of values in two arrays.

• SUMXMY2

Calculates the sum of the squares of differences of values in two arrays.

• TAN

The TAN function returns the tangent of an angle provided in radians.

• TANH

The TANH function returns the hyperbolic tangent of any real number.

• TRUNC

Truncates a number to a certain number of significant digits by omitting less significant digits.

• ## Financial Functions

• ACCRINT

Calculates the accrued interest of a security that has periodic payments.

• ACCRINTM

Calculates the accrued interest of a security that pays interest at maturity.

• AMORDEGRC

Returns the depreciation for a given accounting using a depreciation coefficient determined by asset life.

• AMORLINC

The AMORLINC function returns the depreciation for an accounting period, or the prorated depreciation if the asset was purchased in the middle of a period.This function is available for users of the French accounting system.

• COUPDAYBS

Calculates the number of days from the first coupon, or interest payment, until settlement.

• COUPDAYS

Calculates the number of days in the coupon, or interest payment, period that contains the specified settlement date.

• COUPDAYSNC

Calculates the number of days from the settlement date until the next coupon, or interest payment.

• COUPNCD

Calculates next coupon, or interest payment, date after the settlement date.

• COUPNUM

Calculates the number of coupons, or interest payments, between the settlement date and the maturity date of the investment.

• COUPPCD

Calculates last coupon, or interest payment, date before the settlement date.

• CUMIPMT

Calculates the cumulative interest over a range of payment periods for an investment based on constant-amount periodic payments and a constant interest rate.

• CUMPRINC

Calculates the cumulative principal paid over a range of payment periods for an investment based on constant-amount periodic payments and a constant interest rate.

• DB

The DB function calculates the depreciation of an asset for a specified period using the arithmetic declining balance method.

• DDB

The DDB function calculates the depreciation of an asset for a specified period using the double-declining balance method.

• DISC

Calculates the discount rate of a security based on price.

• DOLLARDE

Converts a price quotation given as a decimal fraction into a decimal value.

• DOLLARFR

Converts a price quotation given as a decimal value into a decimal fraction.

• DURATION

Returns the annual duration of a security with periodic interest payments

• EFFECT

Calculates the annual effective interest rate given the nominal rate and number of compounding periods per year.

• FV

The FV function calculates the future value of an annuity investment based on constant-amount periodic payments and a constant interest rate.

• FVSCHEDULE

The FVSCHEDULE function calculates the future value of some principal based on a specified series of potentially varying interest rates.

• INTRATE

Calculates the effective interest rate generated when an investment is purchased at one price and sold at another with no interest or dividends generated by the investment itself.

• IPMT

The IPMT function calculates the payment on interest for an investment based on constant-amount periodic payments and a constant interest rate.

• IRR

Calculates the internal rate of return on an investment based on a series of periodic cash flows.

• ISPMT

The ISPMT function calculates the interest paid during a particular period of an investment.

• MDURATION

Returns the Macauley modified duration for a security with an assumed par value of \$100

• MIRR

Calculates the modified internal rate of return on an investment based on a series of periodic cash flows and the difference between the interest rate paid on financing versus the return received on reinvested income.

• NOMINAL

Calculates the annual nominal interest rate given the effective rate and number of compounding periods per year.

• NPER

The NPER function calculates the number of payment periods for an investment based on constant-amount periodic payments and a constant interest rate.

• NPV

Calculates the net present value of an investment based on a series of periodic cash flows and a discount rate.

• ODDFPRICE

Returns the price per \$100 face value of a security with an odd first period

• ODDFYIELD

Returns the yield of a security with an odd first period

• ODDLPRICE

Returns the price per \$100 face value of a security with an odd last period

• ODDLYIELD

Returns the yield of a security with an odd last period

• PDURATION

The PDURATION function returns the number of periods for an investment to reach a specific value at a given rate. Mathematically, PDURATION = (log(futureValue) - log (currentValue))/(log(1 + rate)).

• PMT

The PMT function calculates the periodic payment for an annuity investment based on constant-amount periodic payments and a constant interest rate.

• PPMT

The PPMT function calculates the payment on the principal of an investment based on constant-amount periodic payments and a constant interest rate.

• PRICE

Calculates the price of a security paying periodic interest, such as a US Treasury Bond, based on expected yield.

• PRICEDISC

Calculates the price of a discount (non-interest-bearing) security, based on expected yield.

• PRICEMAT

Calculates the price of a security paying interest at maturity, based on expected yield.

• PV

Calculates the present value of an annuity investment based on constant-amount periodic payments and a constant interest rate.

• RATE

Calculates the interest rate of an annuity investment based on constant-amount periodic payments and the assumption of a constant interest rate.

Calculates the amount received at maturity for an investment in fixed-income securities purchased on a given date.

• RRI

The RRI function returns the interest rate needed for an investment to reach a specific value within a given number of periods.

• SLN

The SLN function calculates the depreciation of an asset for one period using the straight-line method.

• SYD

The SYD function calculates the depreciation of an asset for a specified period using the sum of years digits method.

• TBILLEQ

Calculates the equivalent annualized rate of return of a US Treasury Bill based on discount rate.

• TBILLPRICE

Calculates the price of a US Treasury Bill based on discount rate.

• TBILLYIELD

Calculates the yield of a US Treasury Bill based on price.

• VDB

Returns the depreciation of an asset for a specified or partial period by using a declining balance method

• XIRR

Calculates the internal rate of return of an investment based on a specified series of potentially irregularly spaced cash flows.

• XNPV

Calculates the net present value of an investment based on a specified series of potentially irregularly spaced cash flows and a discount rate.

• YIELD

Returns the yield on a security that pays periodic interest

• YIELDDISC

Calculates the annual yield of a discount (non-interest-bearing) security, based on price.

• YIELDMAT

The YIELDMAT function calculates the annual yield of a security paying interest at maturity, based on price.

• ## Array & List Functions

• ARRAY_CHANGE_KEY_CASE

Changes the case of all keys in an array

• ARRAY_CHUNK

Split an array into chunks

• ARRAY_COLUMN

Return the values from a single column in the input array

• ARRAY_COMBINE

Creates an array by using one array for keys and another for its values

• ARRAY_COUNT_VALUES

Counts all the values of an array

• ARRAY_DIFF

Computes the difference of arrays

• ARRAY_DIFF_ASSOC

Computes the difference of arrays with additional index check

• ARRAY_DIFF_KEY

Computes the difference of arrays using keys for comparison

• ARRAY_FILL

Fill an array with values

• ARRAY_FILL_KEYS

Fill an array with values, specifying keys

• ARRAY_FLIP

Exchanges all keys with their associated values in an array

• ARRAY_INTERSECT

Computes the intersection of arrays

• ARRAY_INTERSECT_ASSOC

Computes the intersection of arrays with additional index check

• ARRAY_INTERSECT_KEY

Computes the intersection of arrays using keys for comparison

• ARRAY_KEY_EXISTS

Checks if the given key or index exists in the array

• ARRAY_KEY_FIRST

Gets the first key of an array

• ARRAY_KEY_LAST

Gets the last key of an array

• ARRAY_KEYS

Return all the keys or a subset of the keys of an array

• ARRAY_MERGE

Merge one or more arrays

• ARRAY_MERGE_RECURSIVE

Merge one or more arrays recursively

Pad array to the specified length with a value

• ARRAY_POP

Pop the element off the end of array

• ARRAY_PUSH

Push one or more elements onto the end of array

• ARRAY_RAND

Pick one or more random keys out of an array

• ARRAY_REPLACE

Replaces elements from passed arrays into the first array

• ARRAY_REPLACE_RECURSIVE

Replaces elements from passed arrays into the first array recursively

• ARRAY_REVERSE

Return an array with elements in reverse order

• ARRAY_SEARCH

Searches the array for a given value and returns the first corresponding key if successful

• ARRAY_SHIFT

Shift an element off the beginning of array

• ARRAY_SLICE

Extract a slice of the array

• ARRAY_SPLICE

Remove a portion of the array and replace it with something else

• ARRAY_SUM

Calculate the sum of values in an array

• ARRAY_UNIQUE

Removes duplicate values from an array

• ARRAY_UNSHIFT

Prepend one or more elements to the beginning of an array

• ARRAY_VALUES

Return all the values of an array

• ARSORT

Sort an array in reverse order and maintain index association

• ASORT

Sort an array and maintain index association

• CONCAT_ARRAY

Method is used to join two or more arrays.

• CONTAINS_ARRAY

Determines whether a value exists in array

• COUNT_ARRAY_ELEMENTS

Count all elements in an array, or something in an object

• DISTINCT

Removes duplicates elements in array.

• EXPLODE

Split a string by a string

• FILL

Fills the specified elements in an array with a static value.

• FIRST

Returns first element value from array or object

• FLATTEN

Creates a new array with all sub-array elements concatenated into it recursively up to the specified depth.

• GET

Returns value from array or object by a specific key

• GET_COMPLEX

Returns value from object by a specific deep key

• IMPLODE

Join array elements with a string

• IN_ARRAY

Checks if a value exists in an array

• INDEXOF_ARRAY

Searches the array for the specified item, and returns its position.

• ISARRAY

Determines whether an object is an array.

• JOIN

Concatenate all the elements of array into a one string with a string

• JOIN_ARRAY

Join array elements with a string

• KEY_EXISTS

Checks if the given key or index exists in the array

• KEYS

Return all the keys or a subset of the keys of an array

• KRSORT

Sort an array by key in reverse order

• KSORT

Sort an array by key

• LENGTH_ARRAY

Count all elements in an array, or something in an object

• MERGE

Merge one or more arrays

• NATCASESORT

Sort an array using a case insensitive "natural order" algorithm

• NATSORT

Sort an array using a "natural order" algorithm

• POP

Removes the last element of an array

• PUSH

Adds new items to the end of an array

• RANGE

Create an array containing a range of elements

• REMOVE

Removes elements from array by value

• REVERSE

Return an array with elements in reverse order

• RSORT

Sort an array in reverse order

• SHIFT

Removes the first item of an array.

• SHUFFLE

Shuffle an array

• SIZEOF

Count all elements in an array, or something in an object

• SLICE

Extract a slice of the array

• SORT

Sorts values of array.

• SORT_ARRAY

Sort an array

• ## Text Functions

Quote string with slashes in a C style

Quote string with slashes

• ASC

Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters

• BAHTTEXT

Converts a number to text, using the ß (baht) currency format

• CAPITALIZE

Makes capital letter the first character of every word

• CHARAT

Returns the character at the specified index in a string

• CHARCODEAT

Returns the Unicode of the character at the specified index in a string

• CHR

Generate a single-byte string from a number

• CONTAINS

Determines whether a string contains the characters of a specified string. Case sensitive

• CONVERT_TO_ASCII

Removes all non-ASCII characters from string

• CONVERT_UUDECODE

Decode a uuencoded string

• CONVERT_UUENCODE

Uuencode a string

• CURRENCY_TO_WORDS

Converts currency to words

• DBCS

Changes half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters

• DEFAULT_VALUE

Returns the default value if the first value is empty

• DUMMY_TEXT

Dummy text generator (lorem ipsum)

• ENDSWITH

Determines whether a string ends with the characters of a specified string

• FROMCHARCODE

Converts Unicode values into characters.

• ICONV

Convert string to requested character encoding

• IF_TEXT_ENDS

Determines whether a string ends with the characters of a specified string

• IF_TEXT_MATCH

Determines whether a string contains the characters of a specified string. Case sensitive

• IF_TEXT_STARTS

Determines whether a string begins with the characters of a specified string.

• INCLUDES

Determines whether a string contains the characters of a specified string. Case sensitive

• INDEXOF

Returns the position of the first occurrence of a specified value in a string.

• IS_PALINDROME

Determines whether a string is palindrome

• JIS

Changes half-width (single-byte) characters within a string to full-width (double-byte) characters

• LASTINDEXOF

Returns the position of the last occurrence of a specified value in a string or last element index in array

• LENGTH

Returns the length of a string (number of characters) or element count in array

• LOREMIPSUM

Lorem Ipsum generator

• LOWERCASE

Make a string lowercase

• LTRIM

Strip whitespace (or other characters) from the beginning of a string

• MB_STRLEN

Get string length

• MB_STRPOS

Find position of first occurrence of string in a string

• MB_STRTOLOWER

Make a string lowercase. Returns string with all alphabetic characters converted to lowercase.

• MB_STRTOUPPER

Make a string uppercase. Returns string with all alphabetic characters converted to uppercase.

• MB_SUBSTR

Get part of string

• MIRROR_TEXT

Reverse the string

• NL2BR

Inserts HTML line breaks before all newlines in a string

• NUMBER_FORMAT

Format a number with grouped thousands

• NUMBER_TO_WORDS

Converts number to words

• ORD

Convert the first byte of a string to a value between 0 and 255

• PARSEFLOAT

Parses a string and returns a floating point number.

• PARSEINT

Parses a string and returns an integer

• PHONETIC

Extracts the phonetic (furigana) characters from a text string

• PLURALIZE

Transforms all words into plural (EN, RU). (cookie - cookies, man - men, potato - potatoes)

• PREG_REPLACE

Perform a regular expression search and replace

• QUOTEMETA

Quote meta characters

• RANDOM_NAME

Generates random First name and Last name

• RANDOM_STRING

Generates random alphabetic string

• REPEAT

Returns a new string with a specified number of copies of the string it was called on.

• RTRIM

Strip whitespace (or other characters) from the end of a string

• SIMILAR_TEXT

Calculate the similarity between two strings

• SINGULARIZE

Transforms all words into singular (EN, RU). (cookies - cookie, men - man, potatoes - potato)

• SPLIT_TEXT

Splits the text by specific character or a word. Returns a specified part by index, starting from 0

• STARTCASE

Make a string uppercase. Returns string with all alphabetic characters converted to uppercase.

• STARTSWITH

Determines whether a string begins with the characters of a specified string.

• STR_IREPLACE

Case-insensitive Replace all occurrences of the search string with the replacement string

Pad a string to a certain length with another string

• STR_REPEAT

Repeat a string

• STR_REPLACE

Replace all occurrences of the search string with the replacement string

• STR_ROT13

Perform the rot13 transform on a string

• STR_SHUFFLE

Randomly shuffles a string

• STR_SPLIT

Convert a string to an array

• STR_WORD_COUNT

Return information about words used in a string

• STRIPCSLASHES

• STRIPOS

Find the position of the first occurrence of a case-insensitive substring in a string

• STRIPSLASHES

Un-quotes a quoted string

• STRISTR

Case-insensitive Find the first occurrence of a string

• STRLEN

Get string length

• STRPOS

Find the position of the first occurrence of a substring in a string

• STRRCHR

Find the last occurrence of a character in a string

• STRREV

Reverse a string

• STRRIPOS

Find the position of the last occurrence of a case-insensitive substring in a string

• STRRPOS

Find the position of the last occurrence of a substring in a string

• STRSPN

Finds the length of the initial segment of a string consisting entirely of characters contained within a given mask

• STRSTR

Find the first occurrence of a string

• STRTOLOWER

Make a string lowercase

• STRTOUPPER

Make a string uppercase

• SUBSTR

Return part of a string

• SUBSTR_COUNT

Count the number of substring occurrences

• SUBSTR_REPLACE

Replace text within a portion of a string

• SUBSTRING

Extracts the characters from a string, between two specified indices, and returns the new sub string

• TEXT_LOWERCASE

Converts a string to lowercase letters.

• TEXT_REPLACE

Replace all occurrences of the search string with the replacement string

• TEXT_TITLE

Makes capital letter the first character of every word

• TEXT_UPPERCASE

Converts a string to uppercase letters.

• TOLOWERCASE

Converts a string to lowercase letters.

• TOSTRING

Convert a Boolean value to a string

• TOUPPERCASE

Converts a string to uppercase letters.

• TRIM_WHITESPACE

• TRUNCATE

Limits the text string to a specific length of characters

• UCFIRST

Make a string's first character uppercase

• UCWORDS

Uppercase the first character of each word in a string

• UPPERCASE

Converts a string to uppercase letters.

• WORD_COUNT

Returns the number of words in a string

• WORDWRAP

Wraps a string to a given number of characters

• ## Date & Time Functions

Returns a new date adding a number of days to a date

Returns a new date adding a number for hours

Returns a new date time adding a number of minutes

Returns a new date time adding a number of months

Returns a new date time adding a number of months

Returns a new date time adding a number of years

• DATE

Converts a year, month, and day into a date.

• DATE_FORMAT

Format a local time/date

• DATEDIF

Calculates the number of days, months, or years between two dates.

• DATEVALUE

Converts a provided date string in a known format to a date value.

• DAY

Returns the day of the month that a specific date falls on, in numeric format.

• DAYS

The DAYS function returns the number of days between two dates.

• DAYS360

Returns the difference between two days based on the 360-day year used in some financial interest calculations.

• EDATE

Returns a date a specified number of months before or after another date.

• EOMONTH

Returns a date representing the last day of a month which falls a specified number of months before or after another date.

• FORMAT_DATE

Format a local time/date

• FORMATDATE

Returns a date formatted according to the specified format

• GETDAY

Return the day of the week. Sunday is 0, Monday is 1, and so on.

• GETFULLYEAR

Return the year

• GETHOURS

Return the hour

• GETMINUTES

Return the minutes

• GETMONTH

Return the month. Note: January is 0, February is 1, and so on.

• GETSECONDS

Return the seconds, according to local time

• GETTIME

Return the number of milliseconds since 1970/01/01. Unix time

• HOUR

Returns the hour component of a specific time, in numeric format.

• IF_DATE_AFTER

Determines whether the first date is after the second

• IF_DATE_EQUAL

Determines whether both dates are equal

• ISOWEEKNUM

Returns the number of the ISO (International Organization for Standardization) week of the year where the provided date falls.

• MINUTE

Returns the minute component of a specific time, in numeric format.

• MONTH

Returns the month of the year a specific date falls in, in numeric format.

• NETWORKDAYS

Returns the number of net working days between two provided days.

• NETWORKDAYS_INTL

Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days

• NOW

Returns the current date and time as a date value.

• PARSEDATE

Returns a date in ISO 8601 format from the specified format

• SECOND

Returns the second component of a specific time, in numeric format.

• SETDATE

Set the day of the month

• SETDAY

Set the day of the month using number of English name

• SETFULLYEAR

Set the year (four digits for dates between year 1000 and 9999) of the date string

• SETHOURS

Sets the hour of a date string.

• SETMINUTES

Sets the minutes of a date string.

• SETMONTH

Sets the month of a date string. January is 0, February is 1, and so on.

• SETSECONDS

Sets the seconds of a date string.

• SETTIME

Add N seconds to January 1, 1970, and display the new date and time

• SETYEAR

Set the year (four digits for dates between year 1000 and 9999) of the date string

• TIME

Converts an hour, minute, and second into a time.

• TIMEVALUE

Returns the fraction of a 24-hour day the time represents.

• TODAY

Returns the current date as a date value.

• WEEKDAY

Returns a number representing the day of the week of the date provided.

• WEEKNUM

Returns a number representing the week of the year where the provided date falls.

• WORKDAY

Calculates the end date after a specified number of working days.

• WORKDAY_INTL

Returns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days

• YEAR

Returns the year specified by a given date.

• YEARFRAC

Returns the number of years, including fractional years, between two dates using a specified day count convention.

• ## Lookup & Reference Functions

Returns a reference as text to a single cell in a worksheet

• AREAS

Returns the number of areas in a reference

• CHOOSE

Returns an element from a list of choices based on index.

• COLUMN

Returns the column number of a reference

• COLUMNS

Returns the number of columns in a reference

• FILTER

Filters a range of data based on criteria you define

• FORMULATEXT

Returns the formula at the given reference as text

• GETPIVOTDATA

Returns data stored in a PivotTable report

• HLOOKUP

Horizontal lookup. Searches across the first row of a range for a key and returns the value of a specified variable in the column found.

• INDEX

Returns the content of an element, specified by row and column offset.

• INDIRECT

Returns a reference indicated by a text value

• LOOKUP

Looks through a sorted row or column for a key and returns the value of the variable in a result range located in the same position as the search row or column.

• MATCH

Returns the relative position of an item in a range that matches a specified value.

• OFFSET

Returns a range reference shifted a specified number of rows and columns from a starting cell reference.

• ROW

Returns the row number of a reference

• ROWS

Returns the number of rows in a reference

• RTD

Retrieves real-time data from a program that supports COM automation

• SORTBY

Sorts the contents of a range or array based on the values in a corresponding range or array

• TRANSPOSE

Transposes the rows and columns of an array or range of variables.

• UNIQUE

Returns a list of unique values in a list or range

• VLOOKUP

Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified variable in the row found.

• XLOOKUP

Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

• XMATCH

Returns the relative position of an item in an array or range of cells.

• ## Logical Functions

• AND

The AND function returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false.

• FALSE

Returns the logical value `FALSE`.

• IF

Returns one value if a logical expression is `TRUE` and another if it is `FALSE`.

• IFNA

The IFNA function evaluates a value. If the value is an #N/A error, return the specified value.

• IFS

Evaluates multiple conditions and returns a value that corresponds to the first true condition.

• NOT

Returns the opposite of a logical value - `NOT(TRUE)` returns `FALSE`; `NOT(FALSE)` returns `TRUE`.

• OR

The OR function returns true if any of the provided arguments are logically true, and false if all of the provided arguments are logically false.

• SUMIFS

Sums items that meet multiple criteria.

• SWITCH

Tests an expression against a list of cases and returns the corresponding value of the first matching case, with an optional default value if nothing else is met.

• TRUE

Returns the logical value `TRUE`.

• XOR

The XOR function returns TRUE if an odd number of the provided arguments are logically true, and FALSE otherwise.

• ## Statistical Functions

• AVEDEV

Calculates the average of the magnitudes of deviations of data from a dataset's mean.

• AVERAGE

The AVERAGE function returns the numerical average value in a dataset, ignoring text.

• AVERAGEA

Returns the numerical average value in a dataset.

• AVERAGEIF

Returns the average of a range depending on criteria.

• AVERAGEIFS

Returns the average (arithmetic mean) of all cells that meet multiple criteria.

• BETA_DIST

Returns the probability of a given value as defined by the beta distribution function.

• BETA_INV

Returns the value of the inverse beta distribution function for a given probability.

The BETADIST function returns the probability of a given value as defined by the beta distribution function.

• BETAINV

The BETAINV function returns the value of the inverse beta distribution function for a given probability.

• BINOM_DIST

Calculates the probability of drawing a certain number of successes (or a maximum number of successes) in a certain number of tries given a population of a certain size containing a certain number of successes, with replacement of draws.

• BINOM_DIST_RANGE

Returns the probability of a trial result using a binomial distribution

• BINOM_INV

Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value

• BINOMDIST

Calculates the probability of drawing a certain number of successes (or a maximum number of successes) in a certain number of tries given a population of a certain size containing a certain number of successes, with replacement of draws.

• CHIDIST

Calculates the right-tailed chi-squared distribution, often used in hypothesis testing.

• CHIINV

Calculates the inverse of the right-tailed chi-squared distribution.

• CHISQ_DIST

Returns the cumulative beta probability density function

• CHISQ_DIST_RT

Returns the one-tailed probability of the chi-squared distribution

• CHISQ_INV

Returns the cumulative beta probability density function

• CHISQ_INV_RT

Returns the inverse of the one-tailed probability of the chi-squared distribution

• CHISQ_TEST

Returns the test for independence

• CHITEST

Returns the test for independence

• CONFIDENCE

Calculates the width of half of the confidence interval for a normal distribution.

• CONFIDENCE_NORM

Returns the confidence interval for a population mean

• CONFIDENCE_T

Returns the confidence interval for a population mean, using a Student's t distribution

• CORREL

Calculates r, the Pearson product-moment correlation coefficient of a dataset.

• COUNT

Returns the number of numeric values in a dataset.

• COUNTA

Returns the number of values in a dataset.

• COUNTBLANK

Returns the number of empty variables in a given range.

• COUNTIF

Returns a conditional count across a range.

• COVAR

Calculates the covariance of a dataset.

• COVARIANCE_P

Returns covariance, the average of the products of paired deviations

• COVARIANCE_S

Returns the sample covariance, the average of the products deviations for each data point pair in two data sets

• CRITBINOM

Calculates the smallest value for which the cumulative binomial distribution is greater than or equal to a specified criteria.

• DEVSQ

Calculates the sum of squares of deviations based on a sample.

• EXPON_DIST

Returns the value of the exponential distribution function with a specified lambda at a specified value.

• EXPONDIST

Returns the value of the exponential distribution function with a specified lambda at a specified value.

• F_DIST

Returns the F probability distribution

• F_DIST_RT

Returns the F probability distribution

• F_INV

Returns the inverse of the F probability distribution

• F_INV_RT

Returns the inverse of the F probability distribution

• F_TEST

Returns the result of an F-test

• FINV

Returns the inverse of the F probability distribution

• FISHER

Returns the Fisher transformation of a specified value.

• FISHERINV

Returns the inverse Fisher transformation of a specified value.

• FORECAST

Calculates the expected y-value for a specified x based on a linear regression of a dataset.

• FORECAST_ETS

Returns a future value based on existing (historical) values by using the AAA version of the Exponential Smoothing (ETS) algorithm

• FORECAST_ETS_CONFINT

Returns a confidence interval for the forecast value at the specified target date

• FORECAST_ETS_SEASONALITY

Returns the length of the repetitive pattern Excel detects for the specified time series

• FORECAST_ETS_STAT

Returns a statistical value as a result of time series forecasting

• FORECAST_LINEAR

Returns a future value based on existing values

• FREQUENCY

Returns a frequency distribution as a vertical array

• GAMMA

Returns the Gamma function value

• GAMMA_DIST

The GAMMA_DIST function calculates the gamma distribution, a 2-parameter continuous probability distribution.

• GAMMA_INV

The GAMMA_INV function returns the value of the inverse gamma cumulative distribution function for the specified probability, alpha, and beta parameters.

The GAMMADIST function calculates the gamma distribution, a 2-parameter continuous probability distribution.

• GAMMAINV

The GAMMAINV function returns the value of the inverse gamma cumulative distribution function for the specified probability, alpha, and beta parameters.

• GAMMALN

Returns the the logarithm of a specified Gamma function, base e (Euler's number).

• GAMMALN_PRECISE

Returns the natural logarithm of the gamma function, Γ(x)

• GAUSS

Returns 0.5 less than the standard normal cumulative distribution

• GEOMEAN

Calculates the geometric mean of a dataset.

• GROWTH

Given partial data about an exponential growth trend, fits an ideal exponential growth trend and/or predicts further values.

• HARMEAN

Calculates the harmonic mean of a dataset.

• HYPGEOM_DIST

Calculates the probability of drawing a certain number of successes in a certain number of tries given a population of a certain size containing a certain number of successes, without replacement of draws.

• HYPGEOMDIST

Calculates the probability of drawing a certain number of successes in a certain number of tries given a population of a certain size containing a certain number of successes, without replacement of draws.

• INTERCEPT

Calculates the y-value at which the line resulting from linear regression of a dataset will intersect the y-axis (x=0).

• KURT

Calculates the kurtosis of a dataset, which describes the shape, and in particular the "peakedness" of that dataset.

• LARGE

Returns the nth largest element from a data set, where n is user-defined.

• LINEST

Given partial data about a linear trend, calculates various parameters about the ideal linear trend using the least-squares method.

• LOGEST

Given partial data about an exponential growth curve, calculates various parameters about the best fit ideal exponential growth curve.

Returns the value of the inverse log-normal cumulative distribution with given mean and standard deviation at a specified value.

• LOGNORM_DIST

Returns the value of the log-normal cumulative distribution with given mean and standard deviation at a specified value.

• LOGNORM_INV

Returns the inverse of the lognormal cumulative distribution

• LOGNORMDIST

Returns the value of the log-normal cumulative distribution with given mean and standard deviation at a specified value.

• MAX

Returns the maximum value in a numeric dataset.

• MAXA

Returns the maximum numeric value in a dataset.

• MAXIFS

Returns the maximum value in a range of cells, filtered by a set of criteria.

• MEDIAN

Returns the median value in a numeric dataset.

• MIN

Returns the minimum value in a numeric dataset.

• MINA

Returns the minimum numeric value in a dataset.

• MINIFS

Returns the minimum value in a range of cells, filtered by a set of criteria.

• MODE

Returns the most commonly occurring value in a dataset.

• MODE_MULT

Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data

• MODE_SNGL

Returns the most frequently occurring number in a numeric data set. For example, MODE_SNGL(1,2,4,4,5,5,5,6) returns 5.

• NEGBINOM_DIST

Calculates the probability of drawing a certain number of failures before a certain number of successes given a probability of success in independent trials.

• NEGBINOMDIST

Calculates the probability of drawing a certain number of failures before a certain number of successes given a probability of success in independent trials.

• NORM_DIST

Returns the value of the normal distribution function (or normal cumulative distribution function) for a specified value, mean, and standard deviation.

• NORM_S_DIST

Returns the value of the standard normal cumulative distribution function for a specified value.

• NORM_S_INV

Returns the value of the inverse standard normal distribution function for a specified value.

• NORMDIST

The NORMDIST function returns the value of the normal distribution function (or normal cumulative distribution function) for a specified value, mean, and standard deviation.

• NORMINV

Returns the value of the inverse normal distribution function for a specified value, mean, and standard deviation.

• NORMSDIST

Returns the value of the standard normal cumulative distribution function for a specified value.

• NORMSINV

Returns the value of the inverse standard normal distribution function for a specified value.

• PEARSON

Calculates r, the Pearson product-moment correlation coefficient of a dataset.

• PERCENTILE

Returns the value at a given percentile of a dataset.

• PERCENTILE_EXC

Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive

• PERCENTILE_INC

Returns the k-th percentile of values in a range

• PERCENTRANK

Returns the percentage rank (percentile) of a specified value in a dataset.

• PERCENTRANK_EXC

Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set

• PERCENTRANK_INC

Returns the percentage rank of a value in a data set

• PERMUT

Returns the number of ways to choose some number of objects from a pool of a given size of objects, considering order.

• PERMUTATIONA

Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects

• PHI

Returns the value of the density function for a standard normal distribution

• POISSON

Returns the value of the Poisson distribution function (or Poisson cumulative distribution function) for a specified value and mean.

• POISSON_DIST

Returns the Poisson distribution

• PROB

Returns the probability that values in a range are between two limits

• QUARTILE

Returns a value nearest to a specified quartile of a dataset.

• QUARTILE_EXC

Returns the quartile of the data set, based on percentile values from 0..1, exclusive

• QUARTILE_INC

Returns the quartile of a data set

• RANK

Returns the rank of a specified value in a dataset.

• RANK_AVG

Returns the rank of a number in a list of numbers

• RANK_EQ

Returns the rank of a number in a list of numbers

• RSQ

Calculates the square of r, the Pearson product-moment correlation coefficient of a dataset.

• SKEW

Calculates the skewness of a dataset, which describes the symmetry of that dataset about the mean.

• SKEW_P

Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean

• SLOPE

Calculates the slope of the line resulting from linear regression of a dataset.

• SMALL

Returns the nth smallest element from a data set, where n is user-defined.

• STANDARDIZE

Calculates the normalized equivalent of a random variable given mean and standard deviation of the distribution.

• STDEV

The STDEV function calculates the standard deviation based on a sample.

• STDEV_P

Calculates the standard deviation for a sample set of data. STDEV_P calculates standard deviation using the "n" method, ignoring logical values and text.

• STDEV_S

Calculates the standard deviation for a sample set of data. STDEV_S replaces the older STDEV function, with the same behavior.

• STDEVA

Calculates the standard deviation based on a sample, setting text to the value `0`.

• STDEVP

Calculates the standard deviation based on an entire population.

• STDEVPA

Calculates the standard deviation based on an entire population, setting text to the value `0`.

• STEYX

Calculates the standard error of the predicted y-value for each x in the regression of a dataset.

• T_DIST

Returns the Percentage Points (probability) for the Student t-distribution

• T_DIST_2T

Returns the Percentage Points (probability) for the Student t-distribution

• T_DIST_RT

Returns the Student's t-distribution

• T_INV

Calculates the negative inverse of the one-tailed TDIST function.

• T_INV_2T

Returns the inverse of the Student's t-distribution

• T_TEST

Returns the probability associated with a Student's t-test

• TDIST

Calculates the probability for Student's t-distribution with a given input (x).

• TINV

Calculates the inverse of the two-tailed TDIST function.

• TREND

Given partial data about a linear trend, fits an ideal linear trend using the least squares method and/or predicts further values.

• TRIMMEAN

Calculates the mean of a dataset excluding some proportion of data from the high and low ends of the dataset.

• VAR

Calculates the variance based on a sample.

• VAR_P

Returns the variance in an entire population. If data represents a sample of the population, use the VAR_S function.

• VAR_S

Returns the variance of a sample. If data represents the entire population, use the VAR_P function. VAR_S ignores text values and logicals in references.

• VARA

Calculates the variance based on a sample, setting text to the value `0`.

• VARP

Calculates the variance based on an entire population.

• VARPA

Calculates the variance based on an entire population, setting text to the value `0`.

• WEIBULL

Returns the value of the Weibull distribution function (or Weibull cumulative distribution function) for a specified shape and scale.

• WEIBULL_DIST

Returns the Weibull distribution

• Z_TEST

Returns the one-tailed probability-value of a z-test

• ZTEST

Returns the one-tailed P-value of a Z-test with standard distribution.

• ## Data Functions

• BASE64

Encodes data with MIME base64

• BASE64_DECODE

Decodes data encoded with MIME base64

• BASE64_ENCODE

Encodes data with MIME base64

• BINDEC

Binary to decimal.

• CALL

Calls a procedure in a dynamic link library or code resource

• CUBEKPIMEMBER

Returns a key performance indicator (KPI) name, property, and measure, and displays the name and property in the cell. A KPI is a quantifiable measurement, such as monthly gross profit or quarterly employee turnover, used to monitor an organization's performance.

• CUBEMEMBER

Returns a member or tuple in a cube hierarchy. Use to validate that the member or tuple exists in the cube.

• CUBEMEMBERPROPERTY

Returns the value of a member property in the cube. Use to validate that a member name exists within the cube and to return the specified property for this member.

• CUBERANKEDMEMBER

Returns the nth, or ranked, member in a set. Use to return one or more elements in a set, such as the top sales performer or top 10 students.

• CUBESET

Defines a calculated set of members or tuples by sending a set expression to the cube on the server, which creates the set, and then returns that set to Microsoft Office Excel.

• CUBESETCOUNT

Returns the number of items in a set.

• CUBEVALUE

Returns an aggregated value from a cube.

• DECBIN

Decimal to binary

• DECHEX

• DECOCT

Decimal to octal

• EUROCONVERT

Converts a number to euros, converts a number from euros to a euro member currency, or converts a number from one euro member currency to another by using the euro as an intermediary (triangulation).

• FDIST

Returns the F probability distribution

• FILTER_VAR

Validates a variable with a specified filter

• FTEST

Returns the result of an F-test

• HEXDEC

• MD5

Calculate the md5 hash of a string

• NORM_INV

Returns the value of the inverse normal distribution function for a specified value, mean, and standard deviation.

• OCTDEC

Octal to decimal

• REGISTER_ID

Returns the register ID of the specified dynamic link library (DLL) or code resource that has been previously registered

• STRCMP

Binary safe string comparison

• TTEST

Returns the probability associated with a Student's t-test

• ## Engineering Functions

• BIN2DEC

The BIN2DEC function converts a signed binary number to decimal format.

• BIN2HEX

The BIN2HEX function converts a signed binary number to signed hexadecimal format.

• BIN2OCT

The BIN2OCT function converts a signed binary number to signed octal format.

• BITAND

The BITAND function returns the bitwise boolean AND of two numbers.

• BITLSHIFT

The BITLSHIFT function shifts the bits of the input a certain number of places to the left. Bits on the right are filled with zeroes (0s).

• BITOR

The BITOR function returns the bitwise Boolean OR of 2 numbers. The BITOR truth table is below:

• BITRSHIFT

The BITRSHIFT function shifts the bits of the input a certain number of places to the right. Bits on the left are filled with zeroes.

• BITXOR

The BITXOR function is a bitwise XOR (exclusive or) of 2 numbers that returns a bit of "1" if 2 bits are different, and a bit of "0" otherwise. This function returns a number that's the result of performing an XOR function at each bit of the 2 given numbers.

• COMPLEX

The COMPLEX function creates a complex number, given real and imaginary coefficients.

• CONVERT

Converts a numeric value to a different unit of measure.

• DEC2BIN

The DEC2BIN function converts a decimal number to signed binary format.

• DEC2HEX

The DEC2HEX function converts a decimal number to signed hexadecimal format.

• DEC2OCT

The DEC2OCT function converts a decimal number to signed octal format.

• DELTA

Compare two numeric values, returning 1 if they're equal.

• ERF

The ERF function returns the integral of the Gauss error function over an interval of values.

• ERF_PRECISE

Returns the result of the Gauss error function.

• ERFC

The ERFC function returns the complementary Gauss error function of a value.

• GESTEP

The GESTEP function returns 1 if the rate is strictly greater than or equal to the provided step value, or 0 otherwise. If no step value is provided, then the default value of 0 is used.

• HEX2BIN

The HEX2BIN function converts a signed hexadecimal number to signed binary format.

• HEX2DEC

The HEX2DEC function converts a signed hexadecimal number to decimal format.

• HEX2OCT

The HEX2OCT function converts a signed hexadecimal number to signed octal format.

• IMABS

Returns the absolute value of a complex number.

• IMAGINARY

Returns the imaginary coefficient of a complex number.

• IMARGUMENT

The IMARGUMENT function returns the angle (also known as the argument, or theta) of the given complex number in radians. This is the angle θ such that, for any complex number in Cartesian form x + yi, x + yi = reiθ where r is the magnitude of the number.

• IMCONJUGATE

Returns the complex conjugate of a number.

• IMCOS

The IMCOS function returns the cosine of the given complex number. For example, a given complex number "x+yi" returns "cos(x+yi)."

• IMCOSH

The IMCOSH function returns the hyperbolic cosine of the given complex number. For example, a given complex number "x+yi" returns "cosh(x+yi)."

• IMCOT

The IMCOT function returns the cotangent of the given complex number. For example, a given complex number "x+yi" returns "cot(x+yi)."

• IMCSC

The IMCSC function returns the cosecant of the given complex number. For example, a given complex number "x+yi" returns "csc(x+yi)."

• IMCSCH

The IMCSCH function returns the hyperbolic cosecant of the given complex number. For example, a given complex number "x+yi" returns "csch(x+yi)."

• IMDIV

Returns one complex number divided by another.

• IMEXP

The IMEXP function returns Euler's number, e (~2.718) raised to a complex power.

• IMLN

The IMLN function returns the logarithm of a complex number, base e (Euler's number).

• IMLOG10

The IMLOG10 function returns the logarithm of a complex number with base 10.

• IMLOG2

The IMLOG2 function returns the logarithm of a complex number with base 2.

• IMPOWER

The IMPOWER function returns a complex number raised to a power.

• IMPRODUCT

Returns the result of multiplying a series of complex numbers together.

• IMREAL

Returns the real coefficient of a complex number.

• IMSEC

The IMSEC function returns the secant of the given complex number. For example, a given complex number "x+yi" returns "sec(x+yi)."

• IMSECH

The IMSECH function returns the hyperbolic secant of the given complex number. For example, a given complex number "x+yi" returns "sech(x+yi)."

• IMSIN

The IMSIN function returns the sine of the given complex number. For example, a given complex number "x+yi" returns "sin(x+yi)."

• IMSINH

The IMSINH function returns the hyperbolic sine of the given complex number. For example, a given complex number "x+yi" returns "sinh(x+yi)."

• IMSQRT

The IMSQRT function computes the square root of a complex number.

• IMSUB

Returns the difference between two complex numbers.

• IMSUM

Returns the sum of a series of complex numbers or variables or both.

• IMTAN

The IMTAN function returns the tangent of the given complex number. For example, a given complex number "x+yi" returns "tan(x+yi)."

• OCT2BIN

The OCT2BIN function converts a signed octal number to signed binary format.

• OCT2DEC

The OCT2DEC function converts a signed octal number to decimal format.

• OCT2HEX

The OCT2HEX function converts a signed octal number to signed hexadecimal format.

• ## Information Functions

• CELL

Returns information about the formatting, location, or contents of a cell

• ERROR_TYPE

Returns a number corresponding to an error type

• IFEMPTY

Returns the first value if this value is not empty, otherwise returns the second value

• IFERROR

Receives two values (or expressions) and tests if the first of these evaluates to an error.

• INFO

Returns information about the current operating environment

• ISBLANK

Checks whether the referenced variable is empty.

• ISEMAIL

Validates value as Email

• ISERR

Checks whether a value is an error other than `#N/A`.

• ISERROR

Checks whether a value is an error.

• ISEVEN

Checks whether the provided value is even.

• ISFORMULA

Returns TRUE if there is a reference to a cell that contains a formula

• ISLOGICAL

Checks whether a value is `TRUE` or `FALSE`.

• ISNA

Checks whether a value is the error `#N/A`.

• ISNONTEXT

Checks whether a value is non-textual.

• ISNUMBER

Checks whether a value is a number.

• ISODD

Checks whether the provided value is odd.

• ISREF

Returns TRUE if the value is a reference

• ISTEXT

Checks whether a value is text.

• ISURL

Validates value as URL

• N

Returns the argument provided as a number.

• NA

Returns the "value not available" error, `#N/A`.

• SHEET

Returns the sheet number of the referenced sheet

• SHEETS

Returns the number of sheets in a reference

• TYPE

Returns a number associated with the type of data passed into the function.

• ## Text & Data Functions

• CHAR

Convert a number into a character according to the current Unicode table.

• CLEAN

Returns the text with the non-printable ASCII characters removed.

• CODE

Returns the numeric Unicode map value of the first character in the string provided.

• CONCAT

Returns the concatenation of two values. Equivalent to the `&` operator.

• CONCATENATE

Appends strings to one another.

• DOLLAR

Formats a number into the locale-specific currency format.

• EXACT

Tests whether two strings are identical.

• FIND

Returns the position at which a string is first found within text, case-sensitive.

• FINDB

Returns the position at which a string is first found within text counting each double-character as 2.

• FIXED

Formats a number with a fixed number of decimal places.

• LEFT

Returns a substring from the beginning of a specified string.

• LEFTB

The LEFTB function returns the left portion of a string up to a certain number of bytes.

• LEN

Returns the length of a string.

• LENB

The LENB function returns the length of a string in bytes.

• LOWER

LOWER("LOREM IPSUM")

• MID

Returns a segment of a string.

• MIDB

The MIDB function returns a section of a string starting at a given character and up to a specified number of bytes.

• NUMBERVALUE

Converts a number in text format to numeric value, using specified decimal and group separators. This function can be used to convert locale-specific values into locale-independent values.

• PROPER

Capitalizes each word in a specified string.

• REPLACE

Replaces part of a text string with a different text string.

• REPLACEB

The REPLACEB function replaces part of a text string, based on a number of bytes, with a different text string.

• REPT

Returns specified text repeated a number of times.

• RIGHT

Returns a substring from the end of a specified string.

• RIGHTB

The RIGHTB function returns the right portion of a string up to a certain number of bytes.

• SEARCH

Returns the position at which a string is first found within text, ignoring case.

• SEARCHB

Returns the position at which a string is first found within text counting each double-character as 2.

• SUBSTITUTE

Replaces existing text with new text in a string.

• T

Returns string arguments as text.

• TEXT

Converts a number into text according to a specified format.

• TEXTJOIN

Combines the text from multiple strings and/or arrays, with a specifiable delimiter separating the different texts.

• TRIM

Removes leading, trailing, and repeated spaces in text.

• UNICHAR

Returns the Unicode character for a number. This method supports returning characters in both the UTF-8 and UTF-16 character set.

• UNICODE

The UNICODE function returns the decimal Unicode value of the first character of the text.

• UPPER

Converts a specified string to uppercase.

• VALUE

Converts text that appears in a recognized format (i.e. a number, date, or time format) into a numeric value

• ## Web Functions

• CONTRAST_COLOR_HEX

Returns contrast color by given HEX color code

• CONTRAST_COLOR_RGB

Returns contrast color by given RGB

• DECODEURI

Decodes URL-encoded string

• DECODEURL

Decodes URL-encoded string

• ENCODEURI

URL-encodes string

• ENCODEURL

URL-encodes string

• FILTERXML

Returns specific data from the XML content by using the specified XPath

• HEX_TO_RGB

Transforms HEX color code to RGB. (#ffffff = 255,255,255)

• HTTP_NAME_TO_STATUS_CODE

Returns HTTP status code by name

• HTTP_STATUS_CODE_NAME

Returns HTTP status code name by code

Creates a hyperlink in HTML format

• KEYCODE_BY_NAME

Returns JavaScript Key Code number by keyboard key name

• KEYCODE_TO_NAME

Returns keyboard key name by JavaScript Key Code number

• RAWURLDECODE

Decode URL-encoded strings

• RAWURLENCODE

URL-encode according to RFC 3986

• RGB_TO_HEX

Transforms RGB color to HEX. (255,255,255 = #ffffff)

• URLDECODE

Decodes URL-encoded string

• URLENCODE

URL-encodes string

• WEBSERVICE

Returns data from a web service.

• ## HTML Processing Functions

• CONVERT_MARKDOWN_TO_HTML

Returns valid HTML from Markdown

• ESCAPEHTML

Convert special characters to HTML entities

• HTML_ENTITY_DECODE

Convert HTML entities to their corresponding characters

• HTML_TO_TEXT

Converts HTML to plain text and removes all code

• HTMLENTITIES

Convert all applicable characters to HTML entities

• HTMLSPECIALCHARS

Convert special characters to HTML entities

• HTMLSPECIALCHARS_DECODE

Convert special HTML entities back to characters

• MARKDOWN2HTML

Returns valid HTML from Markdown

• RANDOM_MAC

• REMOVE_HTML_TAGS

Converts HTML to plain text and removes all code

• STRIP_TAGS

Strip HTML and PHP tags from a string

• STRIPHTML

Strip HTML and PHP tags from a string

• ## GEO Data Functions

• COUNTRY_CURRENCY

Returns currecy code by country. "gb" - "GBP"

• COUNTRY_LANGUAGE

Returns language code by country. "de" - "German"

• COUNTRY_NAME

Returns country name by code. "eg" - "Egypt"

• COUNTRY_NAME_ISO

Returns country ISO 3166-1 alpha2 code. "de" - "DE"

• COUNTRY_NATIVE_NAME

Returns country native name by code. "de" - "Deutschland"

• COUNTRY_OFFICIAL_NAME

Returns country official name by code. "de" - "Bundesrepublik Deutschland"

• GEO_DISTANCE

Measures distance between two coordinates. Returns distance in meters

• ## Database Functions

• DAVERAGE

Returns the average of a set of values selected from a database table-like array or range using a SQL-like query.

• DCOUNT

Counts numeric values selected from a database table-like array or range using a SQL-like query.

• DCOUNTA

Counts values, including text, selected from a database table-like array or range using a SQL-like query.

• DGET

Returns a single value from a database table-like array or range using a SQL-like query.

• DMAX

Returns the maximum value selected from a database table-like array or range using a SQL-like query.

• DMIN

Returns the minimum value selected from a database table-like array or range using a SQL-like query.

• DPRODUCT

Returns the product of values selected from a database table-like array or range using a SQL-like query.

• DSTDEV

Returns the standard deviation of a population sample selected from a database table-like array or range using a SQL-like query.

• DSTDEVP

Returns the standard deviation of an entire population selected from a database table-like array or range using a SQL-like query.

• DSUM

Returns the sum of values selected from a database table-like array or range using a SQL-like query.

• DVAR

Returns the variance of a population sample selected from a database table-like array or range using a SQL-like query.

• DVARP

Returns the variance of an entire population selected from a database table-like array or range using a SQL-like query.

• ## Parsing Functions

• DETECTLANGUAGE

Identifies the language used in text within the specified range.

Finds the email address in a text, returns the first email only

• EXTRACT_NUMBER

Finds a number in text, returns the first pack only

• EXTRACT_PATTERN

Returns the first item of regular expression in given text.

• EXTRACT_PHONE_NUMBER

Finds the phone number in a text, returns the first number only

• EXTRACT_URL

Finds the web URL links in a text, returns the first link only

• PARSENUMBER

Parses a string and returns a floating point number.

• ## Excel Formulas

• EXCEL_LETTER_TO_NUMBER

Transforms an Excel letter to number. A = 0, B = 1 etc

• EXCEL_NUMBER_TO_LETTER

Transforms an number to Excel letter. 0 = A, 1 = B etc

• ## Number Functions

• FLOATVAL

Get float value of a variable

• INTVAL

Get the integer value of a variable

• IS_NUMERIC

Finds whether a variable is a number or a numeric string

• ## Formatting Functions

• FORMAT_CURRENCY

Transforms a number to a specified currency

• FORMAT_PHONE_NUMBER

Transforms a phone number to a specified style

• FORMATNUMBER

Format a number with grouped thousands

• ## Encryption Functions

• HASH

Generate a hash value (message digest)

• SHA1

Calculate the sha1 hash of a string

• SHA256

Generates the sha256 hash of the string

• SHA512

Generates the sha512 hash of the string

• ## Regular Expression Functions

• IF_REGEX_MATCH

Perform a regular expression match

• PREG_MATCH

Perform a regular expression match

• ## HTTP Functions

• ISIP

• ISMAC

• RANDOM_IP

Generates random IP address in IPv4 format

• ## JSON Functions

• JSON_DECODE

Decodes a JSON string

• JSON_ENCODE

Returns the JSON representation of a value

• JSONPARSE

Parses a string and returns as array or object.

• JSONSTRINGIFY

Converts array or object into string.