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.

  • ADD

    Add numbers

  • 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

  • DEG2RAD

    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.

  • ISO_CEILING

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

  • 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

  • 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.

  • RAD2DEG

    Converts the radian number to the equivalent number in degrees

  • RADIANS

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

  • 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.

  • RECEIVED

    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.

  • Text Functions

  • ADDCSLASHES

    Quote string with slashes in a C style

  • ADDSLASHES

    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.

  • STRIPCSLASHES

    Un-quote string quoted with ADDCSLASHES

  • 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

  • STR_IREPLACE

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

  • STR_PAD

    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

  • SUBSTR

    Return part of a string

  • SUBSTRING

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

  • SUBSTR_COUNT

    Count the number of substring occurrences

  • SUBSTR_REPLACE

    Replace text within a portion of a 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

    Removes leading whitespaces from text

  • 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.

  • WORDWRAP

    Wraps a string to a given number of characters

  • WORD_COUNT

    Returns the number of words in a string

  • Date & Time Functions

  • ADDDAYS

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

  • ADDHOURS

    Returns a new date adding a number for hours

  • ADDMINUTES

    Returns a new date time adding a number of minutes

  • ADDMONTHS

    Returns a new date time adding a number of months

  • ADDSECONDS

    Returns a new date time adding a number of months

  • ADDYEARS

    Returns a new date time adding a number of years

  • DATE

    Converts a year, month, and day into a 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.

  • DATE_FORMAT

    Format a local time/date

  • 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.

  • FORMATDATE

    Returns a date formatted according to the specified format

  • FORMAT_DATE

    Format a local time/date

  • 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

  • ADDRESS

    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.

  • Array & List Functions

  • ADD_TO_ARRAY

    Adds values to an array

  • 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_KEYS

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

  • 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_MERGE

    Merge one or more arrays

  • ARRAY_MERGE_RECURSIVE

    Merge one or more arrays recursively

  • ARRAY_PAD

    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

  • INDEXOF_ARRAY

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

  • IN_ARRAY

    Checks if a value exists in an array

  • 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

  • KEYS

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

  • KEY_EXISTS

    Checks if the given key or index exists in the 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

  • 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.

  • BETADIST

    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.

  • 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.

  • 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.

  • 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

  • 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.

  • EXPONDIST

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

  • EXPON_DIST

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

  • 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

  • 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

  • GAMMA

    Returns the Gamma function value

  • GAMMADIST

    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)

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • LOGINV

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

  • LOGNORMDIST

    Returns the value of the 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

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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`.

  • 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.

  • STEYX

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

  • 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.

  • 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

  • VAR

    Calculates the variance based on a sample.

  • 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`.

  • 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.

  • 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

  • ZTEST

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

  • Z_TEST

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

  • 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

    Decimal to hexadecimal

  • 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

    Hexadecimal to decimal

  • 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.

  • ERFC

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

  • ERF_PRECISE

    Returns the result of the Gauss error function.

  • 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

  • HYPERLINK

    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

  • HTMLENTITIES

    Convert all applicable characters to HTML entities

  • HTMLSPECIALCHARS

    Convert special characters to HTML entities

  • HTMLSPECIALCHARS_DECODE

    Convert special HTML entities back to characters

  • HTML_ENTITY_DECODE

    Convert HTML entities to their corresponding characters

  • HTML_TO_TEXT

    Converts HTML to plain text and removes all code

  • MARKDOWN2HTML

    Returns valid HTML from Markdown

  • RANDOM_MAC

    Generates random MAC address

  • REMOVE_HTML_TAGS

    Converts HTML to plain text and removes all code

  • STRIPHTML

    Strip HTML and PHP tags from a string

  • STRIP_TAGS

    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.

  • EXTRACT_EMAIL_ADDRESS

    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

  • FORMATNUMBER

    Format a number with grouped thousands

  • FORMAT_CURRENCY

    Transforms a number to a specified currency

  • FORMAT_PHONE_NUMBER

    Transforms a phone number to a specified style

  • 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

    Validates value as IP address

  • ISMAC

    Validates value as MAC address

  • RANDOM_IP

    Generates random IP address in IPv4 format

  • JSON Functions

  • JSONPARSE

    Parses a string and returns as array or object.

  • JSONSTRINGIFY

    Converts array or object into string.

  • JSON_DECODE

    Decodes a JSON string

  • JSON_ENCODE

    Returns the JSON representation of a value