> ## Documentation Index > Fetch the complete documentation index at: https://help.dingtalk.io/llms.txt > Use this file to discover all available pages before exploring further. # Formula function reference > Complete reference of AI Table formula functions — logical, info, date/time, text, math, engineering, statistics, finance, and more. # **Logical** | Function | Description | Example | Arguments | | --------- | ---------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `AND` | Returns true if all arguments are logically true; returns false if any is false. | AND(\\\[expr1], \\\[expr2]) | expr1: an expression or cell reference that evaluates to a logical value (TRUE / FALSE) or can be coerced to one. expr2: more expressions evaluating to logical values. | | `FALSE` | Returns the logical value FALSE. | FALSE() | | | `IF` | Returns one value when the expression is TRUE, another when FALSE. | IF(\\\[expr], \\\[value\_if\_true], \\\[value\_if\_false]) | expr: a logical expression or reference. value\_if\_true: returned when expr is TRUE. value\_if\_false: returned when expr is FALSE. | | `IFERROR` | Returns the first arg if it's not an error; otherwise returns the second arg, or blank if not provided. | IFERROR(\\\[value], \\\[value\_if\_error]) | value: the value to return when not an error. value\_if\_error: returned when value is an error. | | `IFS` | Evaluates multiple conditions and returns the value matching the first TRUE one. | IFS(\\\[cond1], \\\[val1], \\\[cond2], \\\[val2]) | cond1: first condition to evaluate. val1: returned when cond1 is TRUE. cond2: next condition if previous was FALSE. val2: returned when cond2 is TRUE. | | `NOT` | Returns the inverse of a logical value — NOT(TRUE) → FALSE, NOT(FALSE) → TRUE. | NOT(\\\[expr]) | expr: an expression or cell reference representing a logical value. | | `OR` | Returns true if any argument is logically true; false if all are false. | OR(\\\[expr1], \\\[expr2]) | expr1: an expression evaluating to a logical value. expr2: more expressions evaluating to logical values. | | `SWITCH` | Tests an expression against a list of cases and returns the matching value; returns default when nothing matches (optional). | SWITCH(\[expr], \[case1], \[val1], \[case2\_or\_default], \[val2]) | expr: the value to check. case1: first case to compare. val1: returned when case1 matches. case2\_or\_default: next case to try, or default if no match. val2: returned when case2 matches. | | `TRUE` | Returns the logical value TRUE. | TRUE() | | | `XOR` | Returns true when an odd number of args are logically true; false when even. | XOR(\\\[expr1], \\\[expr2]) | expr1: an expression evaluating to a logical value. expr2: more expressions evaluating to logical values. | # **Information** | Function | Description | Example | Arguments | | ------------ | ---------------------------------------------------------------------- | --------------------- | ---------------------------------------------------------------------------------- | | `ERROR.TYPE` | Returns the number corresponding to an error value in another cell. | ERROR.TYPE(\\\[ref]) | ref: cell to look up the error number — you may also pass an error value directly. | | `ISBLANK` | Checks whether the referenced cell is empty. | ISBLANK(\\\[value]) | value: reference to the cell to check. | | `ISERR` | Checks whether a value is an error other than #N/A. | ISERR(\\\[value]) | value: the value to test. | | `ISERROR` | Checks whether a value is any error. | ISERROR(\\\[value]) | value: the value to test. | | `ISEVEN` | Checks whether a value is even. | ISEVEN(\\\[value]) | value: the value to test. | | `ISLOGICAL` | Checks whether a value is TRUE or FALSE. | ISLOGICAL(\\\[value]) | value: the value to test. | | `ISNA` | Checks whether a value is the #N/A error. | ISNA(\\\[value]) | value: the value to compare against #N/A. | | `ISNONTEXT` | Checks whether a value is non-text. | ISNONTEXT(\\\[value]) | value: the value to test. | | `ISNUMBER` | Checks whether a value is a number. | ISNUMBER(\\\[value]) | value: the value to test. | | `ISODD` | Checks whether a value is odd. | ISODD(\\\[number]) | number: the value to test. | | `ISTEXT` | Checks whether a value is text. | ISTEXT(\\\[value]) | value: the value to test. | | `N` | Returns the argument as a number. Text becomes 0; errors pass through. | N(\\\[value]) | value: the argument to convert. | | `NA` | Returns the "no value" error #N/A. | NA() | | | `URL` | Returns the link of the referenced cell. | URL(\\\[ref]) | ref: a cell reference. | # **Date & time** | Function | Description | Example | Arguments | | ------------------ | ------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `DATE` | Converts the given year, month, day to a date. | DATE(year, month, day) | year: year number — manual or field. month: month number — manual or field. day: day number — manual or field. | | `DATEADD` | Adds/subtracts a time amount from a base time, with a unit. E.g., `DATEADD("2025-01-01", 2, "weeks")` → 2025-01-15. | DATEADD(\\\[start], \\\[amount], \\\[unit]) | start: base time, required. amount: amount to add/subtract, required. unit: unit of `amount`, required. Valid: "ms", "milliseconds", "s", "seconds", "m", "minutes", "h", "hours", "d", "days", "w", "weeks", "M", "months", "q", "quarters", "y", "years". | | `DATEDIF` | Computes days, months, or years between two dates. | DATEDIF(start, end, unit) | start: start date — date field, numeric date, or formula. end: end date — date field, numeric date, or formula. unit: unit abbreviation, e.g., Y / M / D. | | `DATEVALUE` | Converts a known-format date string to a date value. | DATEVALUE(\\\[date\_string]) | date\_string: a string representing a date. | | `DAY` | Returns the day of the month for a date as a number. | DAY(date\_value) | date\_value: the date to extract the day from. | | `DAYS` | Returns days between two dates. | DAYS(end, start) | end: end date — date field, numeric date, or formula. start: start date. | | `DAYS360` | Returns the day count between two dates assuming a 360-day year (used in interest calc). | DAYS360(\\\[start], \\\[end], \\\[method]) | start: start date — date cell ref, date-returning function, or number. end: end date. method: which day-count method to use. | | `EDATE` | Returns a date that's a number of months before/after another date. | EDATE(start, months) | start: reference date. months: months before (negative) or after (positive). | | `EOMONTH` | Returns the last day of the month, a given number of months before/after another date. | EOMONTH(start, months) | start: starting date. months: positive → future; negative → past. | | `HOUR` | Returns the hour part of a time as a number. | HOUR(time) | time: the time to compute. | | `ISOWEEKNUM` | Returns the ISO week number of the year for a given date. | ISOWEEKNUM(\\\[date]) | date: the date — cell ref, date-returning function, or number. | | `MINUTE` | Returns the minute part of a time as a number. | MINUTE(time) | time: the time to compute. | | `MONTH` | Returns the month for a date as a number. | MONTH(date\_value) | date\_value: the date to extract the month from. | | `NETWORKDAYS` | Returns net working days between two dates. | NETWORKDAYS(start, end, \\\[holidays]) | start: start date. end: end date. holidays: defaults to weekends; you can also pass a date range or field. | | `NETWORKDAYS.INTL` | Returns net working days between two dates (excluding specified weekends and holidays). | NETWORKDAYS.INTL(\\\[start], \\\[end], \\\[weekend], \\\[holidays]) | start: period start. end: period end. weekend: number or string indicating which days are weekend. holidays: range or array of dates considered holidays. | | `NOW` | Returns the current date and time as a date value. | NOW() | / | | `SECOND` | Returns the second part of a time as a number. | SECOND(time) | time: the time to compute. | | `TODAY` | Returns the current date as a date value. | TODAY() | / | | `WEEKDAY` | Returns a number for the day of the week of a date. | WEEKDAY(date\_value, \\\[type]) | date\_value: target date. type: which day starts week 1 — 1 = Sunday, 2 = Monday. Default 1. | | `WEEKNUM` | Returns the week number of the year for a date. | WEEKNUM(date, \\\[type]) | date: target date. type: which day starts week 1 — 1 = Sunday, 2 = Monday. Default 1. | | `WORKDAY` | Computes an end date given working days. | WORKDAY(start, days, \\\[holidays]) | start: start date — date function or numeric field. days: working days from start. holidays: defaults to weekends; you can also pass a range or array. | | `WORKDAY.INTL` | Computes a date a given working-days later (excluding specified weekends and holidays). | WORKDAY.INTL(\\\[start], \\\[days], \\\[weekend], \\\[holidays]) | start: start date. days: working days forward (negative for backward). weekend: number or string indicating which days are weekend. holidays: range or array of dates considered holidays. | | `YEAR` | Returns the year of a given date. | YEAR(date\_value) | date\_value: the date to extract the year from. | | `YEARFRAC` | Returns the fractional years between two dates using a specified day-count method. | YEARFRAC(\\\[start], \\\[end], \\\[method]) | start: start date — cell ref, date-returning function, or number. end: end date. method: which day-count method to use. | # **Text** | Function | Description | Example | Arguments | | ------------- | --------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `CHAR` | Returns the Unicode character for a given number. | CHAR(number) | number: the number to convert. | | `CLEAN` | Returns text with non-printable ASCII characters removed. | CLEAN(\\\[text]) | text: the text to clean. | | `CODE` | Returns the Unicode value of the first character of the given string. | CODE(\\\[string]) | string: the string to inspect. | | `CONCAT` | Returns the concatenation of two values. Equivalent to `&`. | CONCAT(\\\[value1], \\\[value2]) | value1: the value before. value2: the value to append. | | `CONCATENATE` | Concatenates multiple strings. | CONCATENATE(string1, \\\[string2, ...]) | string1: initial string. string2: additional strings to append. | | `EXACT` | Compares two strings for equality. | EXACT(\\\[string1], \\\[string2]) | string1: first string. string2: second string. | | `FIND` | Searches for a value starting at a position and returns its first-occurrence position; returns -1 if not found. | FIND(search\_value, search\_range, \\\[start\_position]) | search\_value: the value to find — case-sensitive. search\_range: where to search — value, array, or field. start\_position: starting position. Defaults to 1. | | `FINDB` | Returns the first-occurrence position of a string in text (each double-byte character counts as 2). | FINDB(\\\[search\_for], \\\[text\_to\_search], \\\[start\_position]) | search\_for: string to find. text\_to\_search: where to search. start\_position: starting character position. | | `FIXED` | Formats a number with a fixed number of decimal places. | FIXED(\\\[number], \\\[decimals], \\\[no\_separator]) | number: the number to format. decimals: how many decimals to show. no\_separator: whether to suppress thousands separator (0 / false → use separator). | | `LEFT` | Returns a substring from the beginning of a string. | LEFT(string, \\\[count]) | string: source string. count: number of characters to return from the left. | | `LEFTB` | Returns a substring from the beginning of a string (in bytes). | LEFTB(\[string], \[number\_of\_bytes]) | string: source string. number\_of\_bytes: number of bytes from the left. | | `LEN` | Returns the length of a string. | LEN(text) | text: the string. | | `LENB` | Returns the length of a string in bytes. | LENB(\\\[text]) | text: the string. | | `LOWER` | Converts a string to lowercase. | LOWER(text) | text: the string. | | `MID` | Returns a substring of a given length starting at a given position. | MID(string, start, length) | string: source string. start: 1-based starting index. length: number of characters to extract. | | `MIDB` | Returns a substring (in bytes). | MIDB(\[string], \[starting\_at], \[extract\_length\_bytes]) | string: source string. starting\_at: 1-based byte offset. extract\_length\_bytes: substring length in bytes. | | `PROPER` | Capitalizes the first letter of each word in a string. | PROPER(\\\[text]) | text: the string — first letter of each word becomes uppercase, others lowercase. | | `REGEXTEST` | Checks whether any part of a text matches a regular expression. | REGEXTEST(\\\[text], \\\[regex], \\\[case\_sensitive]) | text: text to check. regex: the regular expression. case\_sensitive: whether matching is case-sensitive. Default true. | | `REPLACE` | Replaces part of a text string with another text. | REPLACE(text, position, length, new\_text) | text: source text. position: 1-based start of replacement. length: number of characters to replace. new\_text: replacement text. | | `REPLACEB` | Replaces part of a text string with another (in bytes). | REPLACEB(\\\[text], \\\[position], \\\[bytes], \\\[new\_text]) | text: source text. position: 1-based byte position. bytes: number of bytes to replace. new\_text: replacement text. | | `REPT` | Repeats text a specified number of times. | REPT(\\\[text], \\\[count]) | text: the string to repeat. count: how many times. | | `RIGHT` | Returns a substring from the end of a string. | RIGHT(string, \\\[count]) | string: source string. count: number of characters from the right. | | `RIGHTB` | Returns a substring from the end of a string (in bytes). | RIGHTB(\[string], \[number\_of\_bytes]) | string: source string. number\_of\_bytes: bytes from the right. | | `SEARCH` | Returns the first-occurrence position of a string in text. Returns #VALUE! if not found. | SEARCH(\\\[search\_string], \\\[text\_to\_search], \\\[start\_position]) | search\_string: string to find. text\_to\_search: where to search. start\_position: starting character position. | | `SEARCHB` | Returns the first-occurrence position in text (each double-byte character counts as 2). | SEARCHB(\\\[search\_for], \\\[text\_to\_search], \\\[start\_position]) | search\_for: string to find. text\_to\_search: where to search. start\_position: starting character position. | | `SUBSTITUTE` | Replaces part of an existing text with new text. | SUBSTITUTE(text, old\_text, new\_text, \\\[occurrence]) | text: original text. old\_text: text to replace. new\_text: replacement. occurrence: which occurrence to replace; if omitted, replaces all. | | `T` | Returns the argument as text; returns empty string if not text. | T(\\\[value]) | value: the argument to convert to text. | | `TEXT` | Converts a number to text in a specified format. | TEXT(value, format) | value: the value to convert. format: a custom number format — common: "YYYY/MM/DD", "MM/DD HH:MM", "DDDD" (full weekday), "DDD" (short weekday), "0.0%". | | `TEXTJOIN` | Joins strings and/or arrays with a customizable delimiter. | TEXTJOIN(\\\[delimiter], \\\[ignore\_empty], \\\[text1], \\\[text2]) | delimiter: a string or reference (may be empty — concatenates plainly). ignore\_empty: boolean; TRUE skips empty cells. text1: any text — string or array. text2: more text. | | `TRIM` | Removes leading, trailing, and duplicate spaces from text. | TRIM(text) | text: the string or reference to clean. | | `UNICHAR` | Returns the character for a decimal Unicode number. | UNICHAR(\\\[number]) | number: the Unicode number. | | `UNICODE` | Returns the decimal Unicode value of the first character of a string. | UNICODE(\\\[text]) | text: a string containing the character to evaluate. | | `UPPER` | Converts a string to uppercase. | UPPER(text) | text: the string. | | `VALUE` | Converts a recognized date, time, or number-formatted string to a number. | VALUE(\\\[text]) | text: the string to convert. | # **Math & trigonometry** | Function | Description | Example | Arguments | | ---------------- | ----------------------------------------------------------------------------------------------------- | ------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `ABS` | Returns the absolute value of a number. | ABS(number) | number: the value. | | `ACOS` | Returns the inverse cosine in radians. | ACOS(number) | number: the value. | | `ACOSH` | Returns the inverse hyperbolic cosine. | ACOSH(number) | number: the value. | | `ACOT` | Returns the inverse cotangent in radians. | ACOT(\\\[value]) | value: the number. | | `ACOTH` | Returns the inverse hyperbolic cotangent in radians. | ACOTH(\\\[value]) | value: the number — must not lie in \[-1, 1]. | | `ARABIC` | Computes the value of a Roman numeral. | ARABIC(\\\[roman]) | roman: the Roman numeral — must be 1 to 3999. | | `ASIN` | Returns the inverse sine in radians. | ASIN(number) | number: the value. | | `ASINH` | Returns the inverse hyperbolic sine in radians. | ASINH(number) | number: the value. | | `ATAN` | Returns the inverse tangent in radians. | ATAN(number) | number: the value. | | `ATAN2` | Returns the inverse tangent of given X / Y coordinates. | ATAN2(x, y) | x: x-coordinate. y: y-coordinate. | | `ATANH` | Returns the inverse hyperbolic tangent. | ATANH(number) | number: the value. | | `BASE` | Converts a number to text in another numeric base (e.g., base 2 for binary). | BASE(\\\[value], \\\[base], \\\[min\_length]) | value: number to convert. base: the target base. min\_length: minimum length of returned text. | | `CEILING` | Rounds up to the nearest multiple of a factor. | CEILING(\\\[value], \\\[factor]) | value: the number to round up. factor: round-to multiple. | | `CELING.MATH` | Rounds up to the nearest multiple of significance; for negative numbers, direction depends on mode. | CEILING.MATH(\\\[number], \\\[significance], \\\[mode]) | number: the number to round. significance: round-to multiple — sign ignored. mode: for negatives, controls direction. 0 / blank → toward zero; otherwise away from zero. | | `CELING.PRECISE` | Rounds up to the nearest multiple of significance. Both positive and negative numbers round. | CEILING.PRECISE(\\\[number], \\\[significance]) | number: the number to round. significance: round-to multiple. | | `COMBIN` | Returns the number of ways to choose k from n (combinations). | COMBIN(\\\[n], \\\[k]) | n: total objects. k: number to choose. | | `COMBINA` | Returns the number of ways to choose k from n with repetition. | COMBINA(\\\[n], \\\[k]) | n: total objects. k: number to choose. | | `COS` | Returns the cosine of an angle (in radians). | COS(angle) | angle: the angle in radians. | | `COSH` | Returns the hyperbolic cosine of a real number. | COSH(number) | number: the value. | | `COT` | Returns the cotangent of an angle (in radians). | COT(\\\[angle]) | angle: the angle in radians. | | `COTH` | Returns the hyperbolic cotangent of a real number. | COTH(\\\[value]) | value: the real number. | | `CSC` | Returns the cosecant of an angle (in radians). | CSC(\\\[angle]) | angle: the angle in radians. | | `CSCH` | Returns the hyperbolic cosecant of a real number. | CSCH(\\\[value]) | value: the real number. | | `DECIMAL` | Converts a number-text in another base to a base-10 number. | DECIMAL(\\\[value], \\\[base]) | value: the number-text to convert. base: the source base. | | `DEGREES` | Converts an angle from radians to degrees. | DEGREES(\\\[angle]) | angle: the angle in radians. | | `EVEN` | Rounds up to the nearest even integer. | EVEN(\\\[number]) | number: the value to round. | | `EXP` | Returns Euler's number e (\~2.718) raised to a power. | EXP(\\\[exponent]) | exponent: the power to raise e to. | | `FACT` | Returns the factorial of a number. | FACT(\\\[number]) | number: the value. | | `FACTDOUBLE` | Returns the double factorial of a number. | FACTDOUBLE(\\\[number]) | number: the value. | | `FLOOR` | Rounds down to the nearest multiple of a factor. | FLOOR(\\\[value], \\\[factor]) | value: the number to round down. factor: round-to multiple. | | `FLOOR.MATH` | Rounds down to the nearest multiple of significance; for negative numbers, direction depends on mode. | FLOOR.MATH(\\\[number], \\\[significance], \\\[mode]) | number: the number to round. significance: round-to multiple — sign ignored. mode: for negatives, 0 / blank → away from zero; otherwise toward zero. | | `FLOOR.PRECISE` | Rounds down to the nearest multiple of significance. Both positive and negative numbers round. | FLOOR.PRECISE(\\\[number], \\\[significance]) | number: the number to round. significance: round-to multiple. | | `GCD` | Returns the greatest common divisor of one or more integers. | GCD(\\\[number1], \\\[number2]) | number1: first number or range. number2: more numbers or ranges. | | `INT` | Rounds down to the nearest integer ≤ the value. | INT(number) | number: the value. | | `LCM` | Returns the least common multiple of one or more integers. | LCM(\\\[number1], \\\[number2]) | number1: first number or range. number2: more numbers or ranges. | | `LN` | Returns the natural log (base e) of a number. | LN(\\\[number]) | number: the value. | | `LOG` | Returns the log of a number in a specified base. | LOG(\\\[number], \\\[base]) | number: the value. base: the log base. | | `LOG10` | Returns the base-10 log of a number. | LOG10(\\\[number]) | number: the value. | | `MMULT` | Computes the matrix product of two matrices given as arrays / ranges. | MMULT(\\\[matrix1], \\\[matrix2]) | matrix1: first matrix as array or range. matrix2: second matrix as array or range. | | `MOD` | Returns the remainder of division. | MOD(dividend, divisor) | dividend: the dividend. divisor: the divisor. | | `MROUND` | Rounds to the nearest multiple of another integer. | MROUND(\\\[number], \\\[factor]) | number: the value to round. factor: round-to multiple. | | `MULTINOMIAL` | Returns the factorial of the sum of arguments divided by the product of their factorials. | MULTINOMIAL(\\\[number1], \\\[number2]) | number1: first number or range. number2: more numbers or ranges. | | `MUNIT` | Returns an n × n identity matrix where n is the input. | MUNIT(\\\[size]) | size: the dimension — must be positive. | | `ODD` | Rounds up to the nearest odd integer. | ODD(\\\[number]) | number: the value to round. | | `PI` | Returns the math constant π (3.14159265358979) to 15 digits. | PI() | / | | `POWER` | Returns the result of raising a number to a power. | POWER(base, exponent) | base: any real number. exponent: the power. | | `PRODUCT` | Returns the product of multiplying a set of numbers. | PRODUCT(\\\[number1], \\\[number2]) | number1: first number or range. number2: more numbers or ranges. | | `QUOTIENT` | Returns the integer portion of a division — drops the remainder. | QUOTIENT(dividend, divisor) | dividend: the dividend. divisor: the divisor. | | `RADIANS` | Converts an angle from degrees to radians. | RADIANS(\\\[angle]) | angle: the angle in degrees. | | `RAND` | | RAND() | | | `RANDBETWEEN` | Returns a random integer between two integers (inclusive). | RANDBETWEEN(\\\[low], \\\[high]) | low: lower bound. high: upper bound. | | `ROMAN` | Formats a number as a Roman numeral. | ROMAN(\\\[number], \\\[simplification]) | number: the number to format — 1 to 3999. simplification: simplification level — 0 to 4. | | `ROUND` | Rounds a number to a specified number of digits. | ROUND(number, digits) | number: the value to round. digits: digits — 1 = 1 decimal, 0 = integer, -1 = tens. | | `ROUNDDOWN` | Rounds a number toward zero to a specified number of digits. | ROUNDDOWN(number, digits) | number: the value to round. digits: 1 = 1 decimal, 0 = integer, -1 = tens. | | `ROUNDUP` | Rounds a number away from zero to a specified number of digits. | ROUNDUP(number, digits) | number: the value to round. digits: 1 = 1 decimal, 0 = integer, -1 = tens. | | `SEC` | Returns the secant of an angle (in radians). | SEC(\\\[angle]) | angle: the angle in radians. | | `SECH` | Returns the hyperbolic secant of a real number. | SECH(\\\[value]) | value: the real number. | | `SIGN` | Returns -1 for negative, 1 for positive, 0 for zero. | SIGN(\\\[number]) | number: the value. | | `SIN` | Returns the sine of an angle (in radians). | SIN(angle) | angle: the angle in radians. | | `SINH` | Returns the hyperbolic sine of a real number. | SINH(number) | number: the value. | | `SQRT` | Returns the positive square root of a positive number. | SQRT(\\\[number]) | number: the value. | | `SQRTP` | Returns the positive square root of (PI × a positive number). | SQRTPI(\\\[number]) | number: the value to multiply by PI. | | `SUM` | Returns the sum of all values in the target array. | SUM(value1, \\\[value2, ...]) | value1: first number or column reference. value2: more numbers or columns. | | `SUMPRODUCT` | Returns the sum of products of corresponding elements in two equal-sized arrays / ranges. | SUMPRODUCT(\\\[array1], \\\[array2]) | array1: first array or range. array2: second array or range. | | `SUMSQ` | Returns the sum of squares of a set of values. | SUMSQ(\\\[number1], \\\[number2]) | number1: first number or range. number2: more numbers or ranges. | | `SUMX2MY2` | Returns the sum of the differences of squares of corresponding values in two arrays. | SUMX2MY2(\[array\_x], \[array\_y]) | array\_x: first array or range. array\_y: second array or range. | | `SUMX2PY2` | Returns the sum of the sums of squares of corresponding values in two arrays. | SUMX2PY2(\[array\_x], \[array\_y]) | array\_x: first array or range. array\_y: second array or range. | | `TAN` | Returns the tangent of an angle (in radians). | TAN(angle) | angle: the angle in radians. | | `TANH` | Returns the hyperbolic tangent of a real number. | TANH(number) | number: the value. | | `TRUNC` | Truncates a number to a specified number of significant digits. | TRUNC(\\\[number], \\\[digits]) | number: the value to truncate. digits: digits to keep after the decimal. | # **Engineering** | Function | Description | Example | Arguments | | -------------- | ----------------------------------------------------------------------------------------------------------- | ------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------ | | `BESSELI` | Returns the modified Bessel function — equals the Bessel function evaluated for purely imaginary arguments. | BESSELI(\\\[value], \\\[order]) | value: input. order: Bessel function order — non-integer is truncated. | | `BESSELJ` | Returns the Bessel function. | BESSELJ(\\\[value], \\\[order]) | value: input. order: Bessel function order — non-integer is truncated. | | `BESSELK` | Returns the modified Bessel function — equals the Bessel function evaluated for purely imaginary arguments. | BESSELK(\\\[value], \\\[order]) | value: input. order: Bessel function order — non-integer is truncated. | | `BESSELY` | Returns the Bessel function. | BESSELY(\\\[value], \\\[order]) | value: input. order: Bessel function order — non-integer is truncated. | | `BIN2DEC` | Converts a signed binary number to decimal. | BIN2DEC(\\\[signed\_binary]) | signed\_binary: a 10-bit signed binary number as a string. | | `BIN2HEX` | Converts a signed binary number to signed hexadecimal. | BIN2HEX(\\\[signed\_binary], \\\[significant\_digits]) | signed\_binary: a 10-bit signed binary number as a string. significant\_digits: padding digits. | | `BIN2OCT` | Converts a signed binary number to signed octal. | BIN2OCT(\\\[signed\_binary], \\\[significant\_digits]) | signed\_binary: a 10-bit signed binary number as a string. significant\_digits: padding digits. | | `BITAND` | Bitwise AND of two values. | BITAND(\\\[value1], \\\[value2]) | value1: first value. value2: second value. | | `BITLSHIFT` | Shifts a value left by a number of bits. | BITLSHIFT(\\\[value], \\\[shift\_amount]) | value: the value. shift\_amount: number of bits to shift left. | | `BITOR` | Bitwise OR of two values. | BITOR(\\\[value1], \\\[value2]) | value1: first value. value2: second value. | | `BITRSHIFT` | Shifts a value right by a number of bits. | BITRSHIFT(\\\[value], \\\[shift\_amount]) | value: the value. shift\_amount: number of bits to shift right. | | `BITXOR` | Bitwise XOR of two values. | BITXOR(\\\[value1], \\\[value2]) | value1: first value. value2: second value. | | `COMPLEX` | Creates a complex number from real and imaginary coefficients. | COMPLEX(\\\[real], \\\[imag], \\\[suffix]) | real: real coefficient. imag: imaginary coefficient. suffix: only "i" or "j"; defaults to "i". | | `DEC2BIN` | Converts a decimal number to signed binary. | DEC2BIN(\\\[decimal], \\\[significant\_digits]) | decimal: a decimal number as a string. significant\_digits: padding digits. | | `DEC2HEX` | Converts a decimal number to signed hexadecimal. | DEC2HEX(\\\[decimal], \\\[significant\_digits]) | decimal: a decimal number as a string. significant\_digits: padding digits. | | `DEC2OCT` | Converts a decimal number to signed octal. | DEC2OCT(\\\[decimal], \\\[significant\_digits]) | decimal: a decimal number as a string. significant\_digits: padding digits. | | `DELTA` | Compares two numbers — returns 1 if equal. | DELTA(\\\[number1], \\\[number2]) | number1: first number to compare. number2: second number to compare. | | `ERF` | Returns the result of the Gauss error function. | ERF(\\\[z1], \\\[z2]) | z1: if used alone, integral runs from 0 to z1. If z2 is given, this is the lower bound. z2: upper bound of the integral. | | `ERF.PRECISE` | Returns the result of the Gauss error function. | ERF.PRECISE(\\\[z1]) | z1: if used alone, integral runs from 0 to z1. If z2 is given, this is the lower bound. | | `ERFC` | Returns the complementary Gauss error function of a value. | ERFC(\\\[z]) | z: integral upper bound. | | `ERFC.PRECISE` | Returns the complementary Gauss error function of a value. | ERFC.PRECISE(\\\[z]) | z: integral upper bound. | | `GESTEP` | Returns 1 if a number is ≥ a step value, else 0. Default step is 0. | GESTEP(\\\[value], \\\[step]) | value: the value to test. step: the step. Returns 0 if blank. | | `HEX2BIN` | Converts a signed hexadecimal number to signed binary. | HEX2BIN(\\\[signed\_hex], \\\[significant\_digits]) | signed\_hex: a 40-bit signed hex number as a string. significant\_digits: padding digits. | | `HEX2DEC` | Converts a signed hexadecimal number to decimal. | HEX2DEC(\\\[signed\_hex]) | signed\_hex: a 40-bit signed hex number as a string. | | `HEX2OCT` | Converts a signed hexadecimal number to signed octal. | HEX2OCT(\\\[signed\_hex], \\\[significant\_digits]) | signed\_hex: a 40-bit signed hex number as a string. significant\_digits: padding digits. | | `IMABS` | Returns the absolute value of a complex number. | IMABS(\\\[number]) | number: the complex number. | | `IMAGINARY` | Returns the imaginary coefficient of a complex number. | IMAGINARY(\\\[complex]) | complex: a complex number of the form a+bi or a+bj. | | `IMARGUMENT` | Returns the argument θ of a complex number (angle in radians). | IMARGUMENT(\\\[number]) | number: the complex number. | | `IMCONJUGATE` | Returns the complex conjugate. | IMCONJUGATE(\\\[number]) | number: the complex number. | | `IMCOS` | Returns the cosine of a complex number in x + yi or x + yj form. | IMCOS(\\\[number]) | number: the complex number. | | `IMCOSH` | Returns the hyperbolic cosine of a complex number in x + yi or x + yj form. | IMCOSH(\\\[number]) | number: the complex number. | | `IMCOT` | Returns the cotangent of a complex number in x + yi or x + yj form. | IMCOT(\\\[number]) | number: the complex number. | | `IMCSC` | Returns the cosecant of a complex number in x + yi or x + yj form. | IMCSC(\\\[number]) | number: the complex number. | | `IMCSCH` | Returns the hyperbolic cosecant of a complex number in x + yi or x + yj form. | IMCSCH(\\\[number]) | number: the complex number. | | `IMDIV` | Returns the result of dividing one complex number by another. | IMDIV(\\\[dividend], \\\[divisor]) | dividend: the complex dividend. divisor: the complex divisor. | | `IMEXP` | Returns Euler's number e (\~2.718) raised to a complex power. | IMEXP(\\\[exponent]) | exponent: the complex exponent. | | `IMLN` | Returns the natural log (base e) of a complex number. | IMLN(\\\[complex]) | complex: the complex number. | | `IMLOG10` | Returns the base-10 log of a complex number. | IMLOG10(\\\[value]) | value: the complex number. | | `IMLOG2` | Returns the base-2 log of a complex number. | IMLOG2(\\\[value]) | value: the complex number. | | `IMPOWER` | Returns a complex number raised to a power. | IMPOWER(\\\[complex\_base], \\\[exponent]) | complex\_base: complex base. exponent: the power. | | `IMPRODUCT` | Returns the product of multiplying a set of complex numbers. | IMPRODUCT(\\\[number1], \\\[number2]) | number1: first complex number or range. number2: more complex numbers or ranges. | | `IMREAL` | Returns the real coefficient of a complex number. | IMREAL(\\\[complex]) | complex: a complex number of the form a+bi or a+bj. | | `IMSEC` | Returns the secant of a complex number in x + yi or x + yj form. | IMSEC(\\\[number]) | number: the complex number. | | `IMSECH` | Returns the hyperbolic secant of a complex number in x + yi or x + yj form. | IMSECH(\\\[number]) | number: the complex number. | | `IMSIN` | Returns the sine of a complex number in x + yi or x + yj form. | IMSIN(\\\[number]) | number: the complex number. | | `IMSINH` | Returns the hyperbolic sine of a complex number in x + yi or x + yj form. | IMSINH(\\\[number]) | number: the complex number. | | `IMSQRT` | Returns the square root of a complex number. | IMSQRT(\\\[complex]) | complex: a complex number of the form a+bi or a+bj. | | `IMSUB` | Returns the difference of two complex numbers. | IMSUB(\\\[number1], \\\[number2]) | number1: the complex number to subtract from. number2: the complex number to subtract. | | `IMSUM` | Returns the sum of a set of complex numbers. | IMSUM(\\\[value1], \\\[value2]) | value1: first complex number or range. value2: more complex numbers or ranges. | | `IMTAN` | Returns the tangent of a complex number in x + yi or x + yj form. | IMTAN(\\\[number]) | number: the complex number. | | `OCT2BIN` | Converts a signed octal number to signed binary. | OCT2BIN(\\\[signed\_octal], \\\[significant\_digits]) | signed\_octal: a 30-bit signed octal as a string. significant\_digits: padding digits. | | `OCT2DEC` | Converts a signed octal number to decimal. | OCT2DEC(\\\[signed\_octal]) | signed\_octal: a 30-bit signed octal as a string. | | `OCT2HEX` | Converts a signed octal number to signed hexadecimal. | OCT2HEX(\\\[signed\_octal], \\\[significant\_digits]) | signed\_octal: a 30-bit signed octal as a string. significant\_digits: padding digits. | # **Statistics** | Function | Description | Example | Arguments | | ------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `AVEDEV` | Returns the average of the absolute deviations from the mean of a data set. | AVEDEV(\\\[value1], \\\[value2]) | value1: first sample value or range. value2: more sample values or ranges. | | `AVERAGE` | Returns the arithmetic mean of a data set, ignoring text. | AVERAGE(\\\[value1], \\\[value2]) | value1: first number or range. value2: more numbers or ranges. | | `AVERAGEA` | Returns the arithmetic mean of a data set. | AVERAGEA(\\\[value1], \\\[value2]) | value1: first value or range. value2: more values or ranges. | | `BETA.DIST` | Returns the beta distribution probability for a given input. | BETA.DIST(\\\[value], \\\[alpha], \\\[beta], \\\[lower], \\\[upper], \\\[Arg6]) | value: input. alpha: first distribution parameter. beta: second distribution parameter. lower: lower bound, default 0. upper: upper bound, default 1. | | `BETA.INV` | Returns the inverse of the beta distribution for a given probability. | BETA.INV(\\\[probability], \\\[alpha], \\\[beta], \\\[lower], \\\[upper]) | probability: input probability. alpha: first parameter. beta: second parameter. lower: lower bound, default 0. upper: upper bound, default 1. | | `BETADIST` | Returns the beta distribution probability for a given input. | BINOM.DIST(\\\[successes], \\\[trials], \\\[probability], \\\[cumulative]) | value: input. alpha: first parameter. beta: second parameter. lower: lower bound, default 0. upper: upper bound, default 1. | | `BETAINV` | Returns the inverse of the beta distribution for a given probability. | BETAINV(\\\[probability], \\\[alpha], \\\[beta], \\\[lower], \\\[upper]) | probability: input probability. alpha: first parameter. beta: second parameter. lower: lower bound, default 0. upper: upper bound, default 1. | | `BINOM.DIST` | Given the trial success rate and that the sample is restored after each trial, returns the probability of a given number of (or up to a given number of) successes in a fixed number of trials. | BINOM.DIST(\\\[successes], \\\[trials], \\\[probability], \\\[cumulative]) | successes: number of successes whose probability is computed. trials: total number of equal-probability trials. probability: success probability per trial. cumulative: whether to use cumulative distribution. | | `BINOM.DIST.RANGE` | Given a population with a fixed number of successes and that the sample is restored, returns the probability of a given number of (or up to a given number of) successes in a number of trials. | BINOM.DIST.RANGE(\\\[trials], \\\[probability], \\\[successes], \\\[max\_successes]) | trials: number of independent trials. probability: success probability per trial. successes: success count to compute. max\_successes: max success count to compute. If omitted, only the exact-count probability is returned. | | `BINOM.INV` | Returns the smallest value such that the cumulative binomial distribution is ≥ a target probability. | BINOM.INV(\\\[trials], \\\[probability], \\\[target\_probability]) | trials: number of independent trials. probability: success probability per trial. target\_probability: critical probability. | | `BINOMDIST` | Given the trial success rate and that the sample is restored after each trial, returns the probability of a given number of (or up to a given number of) successes in a fixed number of trials. | BINOMDIST(\\\[successes], \\\[trials], \\\[probability], \\\[cumulative]) | successes: number of successes whose probability is computed. trials: total number of equal-probability trials. probability: success probability per trial. cumulative: whether to use cumulative distribution. | | `CHIDIST` | Computes the right-tailed chi-squared distribution (often used in hypothesis testing). | CHIDIST(\\\[x], \\\[degrees\_of\_freedom]) | x: input value. degrees\_of\_freedom: degrees of freedom. | | `CHIINV` | Computes the inverse of the right-tailed chi-squared distribution. | CHIINV(\\\[probability], \\\[degrees\_of\_freedom]) | probability: right-tail chi-squared probability. degrees\_of\_freedom: degrees of freedom. | | `CHISQ.DIST` | Computes the left-tailed chi-squared distribution (often used in hypothesis testing). | CHISQ.DIST(\\\[x], \\\[degrees\_of\_freedom], \\\[cumulative]) | x: input value. degrees\_of\_freedom: degrees of freedom. cumulative: whether to use cumulative distribution. | | `CHISQ.DIST.RT` | Computes the right-tailed chi-squared distribution (often used in hypothesis testing). | CHISQ.DIST.RT(\\\[x], \\\[degrees\_of\_freedom]) | x: input value. degrees\_of\_freedom: degrees of freedom. | | `CHISQ.INV` | Computes the inverse of the left-tailed chi-squared distribution. | CHISQ.INV(\\\[probability], \\\[degrees\_of\_freedom]) | probability: left-tail chi-squared probability. degrees\_of\_freedom: degrees of freedom. | | `CHISQ.INV.RT` | Computes the inverse of the right-tailed chi-squared distribution. | CHISQ.INV.RT(\\\[probability], \\\[degrees\_of\_freedom]) | probability: right-tail chi-squared probability. degrees\_of\_freedom: degrees of freedom. | | `CHISQ.TEST` | Returns the probability associated with the Pearson chi-squared test on two ranges — used to judge whether observed categorical data could come from the expected distribution. | CHISQ.TEST(\\\[observed], \\\[expected]) | observed: observed counts per category. expected: expected counts per category under the null hypothesis. | | `CHITEST` | Returns the probability associated with the Pearson chi-squared test on two ranges — used to judge whether observed categorical data could come from the expected distribution. | CHITEST(\\\[observed], \\\[expected]) | observed: observed counts per category. expected: expected counts per category under the null hypothesis. | | `CONFIDENCE` | Returns the half-width of the confidence interval for a normal distribution. | CONFIDENCE(\\\[alpha], \\\[std\_dev], \\\[size]) | alpha: 1 minus the desired confidence level. E.g., 0.1 → 90% confidence. std\_dev: population standard deviation. size: sample size. | | `CONFIDENCE.NORM` | Returns the half-width of the confidence interval for a normal distribution. | CONFIDENCE.NORM(\\\[alpha], \\\[std\_dev], \\\[size]) | alpha: 1 minus the desired confidence level. std\_dev: population standard deviation. size: sample size. | | `CONFIDENCE.T` | Returns the half-width of the confidence interval for a Student's T distribution. | CONFIDENCE.T(\\\[alpha], \\\[std\_dev], \\\[size]) | alpha: 1 minus the desired confidence level. std\_dev: sample standard deviation. size: sample size. | | `CORREL` | Returns the Pearson correlation coefficient r for a data set. | CORREL(\[data\_y], \[data\_x]) | data\_y: range or array of dependent data. data\_x: range or array of independent data. | | `COUNT` | Returns the count of numeric values in a data set. | COUNT(\\\[value1], \\\[value2]) | value1: first value or range to check. value2: more values or ranges. | | `COUNTA` | Returns the count of all values in a data set. | COUNTA(\\\[value1], \\\[value2]) | value1: first value or range to check. value2: more values or ranges. | | `COVAR` | Returns the covariance of a data set. | COVAR(\[data\_y], \[data\_x]) | data\_y: range or array of dependent data. data\_x: range or array of independent data. | | `COVARIANCE.P` | Returns the population covariance of a data set. | COVARIANCE.P(\[data\_y], \[data\_x]) | data\_y: range or array of dependent data. data\_x: range or array of independent data. | | `COVARIANCE.S` | Returns the sample covariance of a data set. | COVARIANCE.S(\[data\_y], \[data\_x]) | data\_y: range or array of dependent data. data\_x: range or array of independent data. | | `CRITBINOM` | Returns the smallest value such that the cumulative binomial distribution is ≥ a target probability. | CRITBINOM(\\\[trials], \\\[probability], \\\[target\_probability]) | trials: number of independent trials. probability: success probability per trial. target\_probability: critical probability. | | `DEVSQ` | Returns the sum of squared deviations from the sample mean. | DEVSQ(\\\[value1], \\\[value2]) | value1: first sample value or range. value2: more sample values or ranges. | | `EXPON.DIST` | Returns the exponential distribution value for a given lambda and value. | EXPON.DIST(\\\[x], \\\[lambda], \\\[cumulative]) | x: input. lambda: lambda parameter. cumulative: whether to use cumulative distribution. | | `EXPONDIST` | Returns the exponential distribution value for a given lambda and value. | EXPONDIST(\\\[x], \\\[lambda], \\\[cumulative]) | x: input. lambda: lambda parameter. cumulative: whether to use cumulative distribution. | | `F.DIST` | Given x, returns the right-tailed F probability distribution between two data sets — also known as the Fisher-Snedecor distribution. | F.DIST(\\\[x], \\\[df1], \\\[df2], \\\[cumulative]) | x: input. df1: numerator degrees of freedom. df2: denominator degrees of freedom. cumulative: form selector. | | `F.DIST.RT` | Given x, returns the right-tailed F probability distribution between two data sets — also known as the Fisher-Snedecor distribution. | F.DIST.RT(\\\[x], \\\[df1], \\\[df2]) | x: input. df1: numerator degrees of freedom. df2: denominator degrees of freedom. | | `F.INV` | Computes the inverse of the right-tailed F probability distribution. | F.INV(\\\[probability], \\\[df1], \\\[df2]) | probability: right-tail F probability. df1: numerator degrees of freedom. df2: denominator degrees of freedom. | | `F.INV.RT` | Computes the inverse of the right-tailed F probability distribution. | F.INV.RT(\\\[probability], \\\[df1], \\\[df2]) | probability: right-tail F probability. df1: numerator degrees of freedom. df2: denominator degrees of freedom. | | `F.TEST` | Returns the probability associated with the F-test for equality of variances. | F.TEST(\\\[range1], \\\[range2]) | range1: first sample or cell range. range2: second sample or cell range. | | `FDIST` | Given x, returns the right-tailed F probability distribution between two data sets — also known as the Fisher-Snedecor distribution. | FDIST(\\\[x], \\\[df1], \\\[df2]) | x: input. df1: numerator degrees of freedom. df2: denominator degrees of freedom. | | `FINV` | Computes the inverse of the right-tailed F probability distribution. | FINV(\\\[probability], \\\[df1], \\\[df2]) | probability: right-tail F probability. df1: numerator degrees of freedom. df2: denominator degrees of freedom. | | `FISHER` | Returns the Fisher transformation of a value. | FISHER(\\\[number]) | number: the value to transform. | | `FISHERINV` | Returns the inverse Fisher transformation of a value. | FISHERINV(\\\[value]) | value: the value to inverse-transform. | | `FORECAST` | Computes the predicted y for a given x based on linear regression on a data set. | FORECAST(\[x], \[data\_y], \[data\_x]) | x: x-axis value to predict. data\_y: range or array of dependent data. data\_x: range or array of independent data. | | `FORECAST.LINEAR` | Computes the predicted y for a given x based on linear regression on a data set. | FORECAST.LINEAR(\[x], \[data\_y], \[data\_x]) | x: x-axis value to predict. data\_y: range or array of dependent data. data\_x: range or array of independent data. | | `FREQUENCY` | Returns the frequency distribution of an array as a one-column array, given specified bins. | FREQUENCY(\\\[data], \\\[bins]) | data: array or range of values. bins: array or range of bin boundaries. | | `FTEST` | Returns the probability associated with the F-test for equality of variances. | FTEST(\\\[range1], \\\[range2]) | range1: first sample or cell range. range2: second sample or cell range. | | `GAMMA` | Returns the gamma function value for a given input. | GAMMA(\\\[number]) | number: the input. | | `GAMMA.DIST` | Computes the gamma distribution (a two-parameter continuous distribution). | GAMMA.DIST(\\\[x], \\\[alpha], \\\[beta], \\\[cumulative]) | x: input. alpha: first parameter. beta: second parameter. cumulative: TRUE → cumulative; FALSE → density. | | `GAMMA.INV` | Returns the inverse of the gamma cumulative distribution given a probability and the alpha and beta parameters. | GAMMA.INV(\\\[probability], \\\[alpha], \\\[beta]) | probability: input probability — must be 0 to 1. alpha: shape parameter — must be positive. beta: scale parameter — must be positive. | | `GAMMALN` | Returns the natural log (base e) of the gamma function. | GAMMALN(\\\[number]) | number: input — returns the natural log of Gamma(number). | | `GAUSS` | Returns the probability that a standard normal variable is within z standard deviations of the mean. | GAUSS(\\\[z]) | z: number of standard deviations from the mean. | | `GEOMEAN` | Returns the geometric mean of a data set. | GEOMEAN(\\\[value1], \\\[value2]) | value1: first sample value or range. value2: more sample values or ranges. | | `HARMEAN` | Returns the harmonic mean of a data set. | HARMEAN(\\\[value1], \\\[value2]) | value1: first sample value or range. value2: more sample values or ranges. | | `HYPGEOM.DIST` | Returns the probability of a number of successes in a number of trials, given the population success rate and that samples are not replaced. | HYPGEOM.DIST(\\\[successes], \\\[trials], \\\[population\_successes], \\\[population\_size]) | successes: required successes. trials: number of trials. population\_successes: total successes in population. population\_size: population size. | | `HYPGEOMDIST` | Returns the probability of a number of successes in a number of trials, given the population success rate and that samples are not replaced. | HYPGEOMDIST(\\\[successes], \\\[trials], \\\[population\_successes], \\\[population\_size]) | successes: required successes. trials: number of trials. population\_successes: total successes in population. population\_size: population size. | | `INTERCEPT` | Returns the y-value where the linear regression line for a data set crosses the y-axis (x = 0). | INTERCEPT(\[data\_y], \[data\_x]) | data\_y: range or array of dependent data. data\_x: range or array of independent data. | | `KURT` | Returns the kurtosis of a data set — describes the peakedness of the distribution. | KURT(\\\[value1], \\\[value2]) | value1: first value or range. value2: more values or ranges. | | `LARGE` | Returns the n-th largest element in a data set. | LARGE(\\\[data], \\\[n]) | data: range or array. n: rank from largest (1 = largest). | | `LOGINV` | Returns the inverse of the lognormal cumulative distribution for a value with a given mean and standard deviation. | LOGINV(\\\[x], \\\[mean], \\\[std\_dev]) | x: input. mean: distribution mean (mu). std\_dev: distribution standard deviation (sigma). | | `LOGNORM.DIST` | Returns the lognormal cumulative distribution for a value with a given mean and standard deviation. | LOGNORM.DIST(\\\[x], \\\[mean], \\\[std\_dev], ) | x: input. mean: distribution mean (mu). std\_dev: distribution standard deviation (sigma). | | `LOGNORM.INV` | Returns the inverse of the lognormal cumulative distribution for a value with a given mean and standard deviation. | LOGNORM.INV(\\\[x], \\\[mean], \\\[std\_dev]) | x: input. mean: distribution mean (mu). std\_dev: distribution standard deviation (sigma). | | `LOGNORMDIST` | Returns the lognormal cumulative distribution for a value with a given mean and standard deviation. | LOGNORMDIST(\\\[x], \\\[mean], \\\[std\_dev]) | x: input. mean: distribution mean (mu). std\_dev: distribution standard deviation (sigma). | | `MAX` | Returns the maximum of a numeric data set. | MAX(\\\[value1], \\\[value2]) | value1: first value or range. value2: more values or ranges. | | `MAXA` | Returns the maximum numeric value of a data set. | MAXA(\\\[value1], \\\[value2]) | value1: first value or range. value2: more values or ranges. | | `MEDIAN` | Returns the median of a numeric data set. | MEDIAN(\\\[value1], \\\[value2]) | value1: first value or range. value2: more values or ranges. | | `MIN` | Returns the minimum of a numeric data set. | MIN(\\\[value1], \\\[value2]) | value1: first value or range. value2: more values or ranges. | | `MINA` | Returns the minimum numeric value of a data set. | MINA(\\\[value1], \\\[value2]) | value1: first value or range. value2: more values or ranges. | | `MODE` | Returns the most-frequent value in a data set. | MODE(\\\[value1], \\\[value2]) | value1: first value or range. value2: more values or ranges. | | `MODE.MULT` | Returns the most-frequent value in a data set. | MODE.MULT(\\\[value1], \\\[value2]) | value1: first value or range. value2: more values or ranges. | | `MODE.SNGL` | Returns the most-frequent value in a data set. | MODE.SNGL(\\\[value1], \\\[value2]) | value1: first value or range. value2: more values or ranges. | | `NEGBINOM.DIST` | Given a fixed success probability, returns the probability of a number of failures before a number of successes. | NEGBINOM.DIST(\\\[failures], \\\[successes], \\\[probability]) | failures: number of failures to model. successes: number of successes to model. probability: per-trial success probability. | | `NEGBINOMDIST` | Given a fixed success probability, returns the probability of a number of failures before a number of successes. | NEGBINOMDIST(\\\[failures], \\\[successes], \\\[probability]) | failures: number of failures to model. successes: number of successes to model. probability: per-trial success probability. | | `NORM.DIST` | Returns the value of the normal distribution function (or its cumulative form) for given input, mean, and standard deviation. | NORM.S.DIST(\\\[x], \\\[Arg2]) | x: input. mean: distribution mean (mu). std\_dev: distribution standard deviation (sigma). cumulative: whether to use cumulative form. | | `NORM.INV` | Returns the inverse of the normal distribution function for given input, mean, and standard deviation. | NORM.INV(\\\[x], \\\[mean], \\\[std\_dev]) | x: input. mean: distribution mean (mu). std\_dev: distribution standard deviation (sigma). | | `NORM.S.DIST` | Returns the standard normal cumulative distribution for a given value. | NORM.S.DIST(\\\[x], \\\[Arg2]) | x: input. Arg2 | | `NORM.S.INV` | Returns the inverse of the standard normal distribution function for a given value. | NORM.S.INV(\\\[x]) | x: input. | | `NORMDIST` | Returns the value of the normal distribution function (or its cumulative form) for given input, mean, and standard deviation. | NORMDIST(\\\[x], \\\[mean], \\\[std\_dev], \\\[cumulative]) | x: input. mean: distribution mean (mu). std\_dev: distribution standard deviation (sigma). cumulative: whether to use cumulative form. | | `NORMINV` | Returns the inverse of the normal distribution function for given input, mean, and standard deviation. | NORMINV(\\\[x], \\\[mean], \\\[std\_dev]) | x: input. mean: distribution mean (mu). std\_dev: distribution standard deviation (sigma). | | `NORMSDIST` | Returns the standard normal cumulative distribution for a given value. | NORMSDIST(\\\[x]) | x: input. | | `NORMSINV` | Returns the inverse of the standard normal distribution function for a given value. | NORMSINV(\\\[x]) | x: input. | | `PEARSON` | Returns the Pearson correlation coefficient r for a data set. | PEARSON(\[data\_y], \[data\_x]) | data\_y: range or array of dependent data. data\_x: range or array of independent data. | | `PERCENTILE` | Returns the value at a given percentile in a data set. | PERCENTILE(\\\[data], \\\[percentile]) | data: array or range. percentile: target percentile. | | `PERCENTILE.EXC` | Returns the value at a given percentile (exclusive of 0 and 1) in a data set. | PERCENTILE.EXC(\\\[data], \\\[percentile]) | data: array or range. percentile: target percentile (exclusive of 0 and 1). | | `PERCENTILE.INC` | Returns the value at a given percentile in a data set. | PERCENTILE.INC(\\\[data], \\\[percentile]) | data: array or range. percentile: target percentile. | | `PERCENTRANK` | Returns the percentile rank (as a percentage) of a value in a data set. | PERCENTRANK(\\\[data], \\\[value], \\\[significant\_digits]) | data: array or range. value: the value to rank. significant\_digits: significant digits to use. Default 3. | | `PERCENTRANK.EXC` | Returns the percentile rank (between 0 and 1, exclusive) of a value in a data set. | PERCENTRANK.EXC(\\\[data], \\\[value], \\\[significant\_digits]) | data: array or range. value: the value to rank. significant\_digits: significant digits to use. Default 3. | | `PERCENTRANK.INC` | Returns the percentile rank (between 0 and 1, inclusive) of a value in a data set. | PERCENTRANK.INC(\\\[data], \\\[value], \\\[significant\_digits]) | data: array or range. value: the value to rank. significant\_digits: significant digits to use. Default 3. | | `PERMUT` | Returns the number of distinct ordered selections of k from n. | PERMUT(\\\[n], \\\[k]) | n: total objects. k: number to select. | | `PERMUTATIONA` | Returns the number of distinct ordered selections of k from n with repetition. | PERMUTATIONA(\\\[n], \\\[k]) | n: total objects. k: number to select. | | `PHI` | Returns the value of the standard normal distribution (mean 0, std dev 1). | PHI(\\\[x]) | x: input. | | `POISSON` | Returns the value of the Poisson distribution function (or its cumulative form) for given parameters. | POISSON(\\\[x], \\\[mean], \\\[cumulative]) | x: input. mean: distribution mean (mu). cumulative: whether to use cumulative form. | | `POISSON.DIST` | Returns the value of the Poisson distribution function (or its cumulative form) for given parameters. | POISSON.DIST(\\\[x], \\\[mean], \\\[cumulative]) | x: input. mean: distribution mean (mu). cumulative: whether to use cumulative form. | | `PROB` | Given a set of values and corresponding probabilities, computes the probability that a random selection falls within two bounds. | PROB(\\\[data], \\\[probability], \\\[lower], \\\[upper]) | data: array or range. probability: array or range of probabilities matching `data`. lower: lower bound. upper: upper bound. | | `QUARTILE` | Returns the value at a given quartile in a data set. | QUARTILE(\\\[data], \\\[quartile]) | data: array or range. quartile: which quartile to return. | | `QUARTILE.EXC` | Returns the value at a given quartile (exclusive of 0 and 4) in a data set. | QUARTILE.EXC(\\\[data], \\\[quartile]) | data: array or range. quartile: which quartile to return. | | `QUARTILE.INC` | Returns the value at a given quartile in a data set. | QUARTILE.INC(\\\[data], \\\[quartile]) | data: array or range. quartile: which quartile to return. | | `RSQ` | Returns r squared, where r is the Pearson correlation coefficient. | RSQ(\[data\_y], \[data\_x]) | data\_y: range or array of dependent data. data\_x: range or array of independent data. | | `SKEW` | Returns the skewness of a data set — describes asymmetry around the mean. | SKEW(\\\[value1], \\\[value2]) | value1: first value or range. value2: more values or ranges. | | `SKEW.P` | Returns the skewness of a data set — describes asymmetry around the mean. Treats the data set as the entire population. | SKEW\.P(\\\[value1], \\\[value2]) | value1: first value or range. value2: more values or ranges. | | `SLOPE` | Returns the slope of the linear regression line for a data set. | SLOPE(\[data\_y], \[data\_x]) | data\_y: range or array of dependent data. data\_x: range or array of independent data. | | `SMALL` | Returns the n-th smallest element in a data set. | SMALL(\\\[data], \\\[n]) | data: array or range. n: rank from smallest (1 = smallest). | | `STANDARDIZE` | Returns the normalized value of a random variable given the distribution mean and standard deviation. | STANDARDIZE(\\\[value], \\\[mean], \\\[std\_dev]) | value: random variable to normalize. mean: distribution mean. std\_dev: distribution standard deviation. | | `STDEV` | Returns the sample standard deviation. | STDEV(\\\[value1], \\\[value2]) | value1: first sample value or range. value2: more sample values or ranges. | | `STDEV.P` | Returns the population standard deviation. | STDEV.P(\\\[value1], \\\[value2]) | value1: first sample value or range. value2: more values or ranges. | | `STDEV.S` | Returns the sample standard deviation. | STDEV.S(\\\[value1], \\\[value2]) | value1: first sample value or range. value2: more sample values or ranges. | | `STDEVA` | Returns the sample standard deviation, treating text as 0. | STDEVA(\\\[value1], \\\[value2]) | value1: first sample value or range. value2: more sample values or ranges. | | `STDEVP` | Returns the population standard deviation. | STDEVP(\\\[value1], \\\[value2]) | value1: first sample value or range. value2: more values or ranges. | | `STDEVPA` | Returns the population standard deviation, treating text as 0. | STDEVPA(\\\[value1], \\\[value2]) | value1: first sample value or range. value2: more values or ranges. | | `STEYX` | Returns the standard error of predicted y values per x in regression analysis. | STEYX(\[data\_y], \[data\_x]) | data\_y: range or array of dependent data. data\_x: range or array of independent data. | | `SUM` | Returns the sum of a set of values and/or cells. | SUM(\\\[value1], \\\[number2]) | value1: first number or range to sum. number2: more numbers or ranges. | | `T.DIST` | Given input (x), returns the Student's t-distribution probability. | T.DIST(\\\[x], \\\[degrees\_of\_freedom], \\\[tails]) | x: input. degrees\_of\_freedom: degrees of freedom. tails: 1 = one-tailed, 2 = two-tailed. | | `T.DIST.2T` | Given input (x), returns the Student's t-distribution probability. | T.INV.2T(\\\[x], \\\[degrees\_of\_freedom]) | x: input. degrees\_of\_freedom: degrees of freedom. tails: 1 = one-tailed, 2 = two-tailed. | | `T.DIST.RT` | Given input (x), returns the right-tailed Student's t-distribution. | T.DIST.RT(\\\[x], \\\[degrees\_of\_freedom]) | x: input. degrees\_of\_freedom: degrees of freedom. | | `T.INV` | Returns the inverse of the two-tailed TDIST function. | T.INV(\\\[probability], \\\[degrees\_of\_freedom]) | probability: two-tailed t probability. degrees\_of\_freedom: degrees of freedom. | | `T.INV.2T` | Returns the inverse of the two-tailed TDIST function. | T.INV.2T(\\\[probability], \\\[degrees\_of\_freedom]) | probability: two-tailed t probability. degrees\_of\_freedom: degrees of freedom. | | `T.TEST` | Returns the inverse of the two-tailed TDIST function. | T.TEST(\\\[range1], \\\[range2], \\\[tails], \\\[type]) | range1: first sample or cell range. range2: second sample or cell range. tails: distribution tails. type: type of t-test. | | `TINV` | t-test. Returns the probability associated with Student's t-test — used to judge whether two samples likely came from populations with equal means. | TINV(\\\[probability], \\\[degrees\_of\_freedom]) | probability: two-tailed t probability. degrees\_of\_freedom: degrees of freedom. | | `TRIMMEAN` | Returns the inverse of the two-tailed TDIST function. | TRIMMEAN(\\\[data], \\\[exclude\_proportion]) | data: array or range. exclude\_proportion: fraction of extreme values to exclude. | | `TTEST` | Returns the mean computed after excluding portions of the high and low ends. | TTEST(\\\[range1], \\\[range2], \\\[tails], \\\[type]) | value1: first sample value or range. value2: more sample values or ranges. | | `VAR` | t-test. Returns the probability associated with Student's t-test — used to judge whether two samples likely came from populations with equal means. | VAR(\\\[value1], \\\[value2]) | value1: first sample value or range. value2: more sample values or ranges. | | `VAR.P` | Returns the variance based on the sample. | VAR.P(\\\[value1], \\\[value2]) | value1: first sample value or range. value2: more values or ranges. | | `VAR.S` | Returns the variance based on the population. | VAR.S(\\\[value1], \\\[value2]) | value1: first sample value or range. value2: more sample values or ranges. | | `VARA` | Returns the variance based on the sample. | VARA(\\\[value1], \\\[value2]) | value1: first sample value or range. value2: more sample values or ranges. | | `VARP` | Returns the variance based on the sample, treating text as 0. | VARP(\\\[value1], \\\[value2]) | value1: first sample value or range. value2: more values or ranges. | | `VARPA` | Returns the variance based on the population. | VARPA(\\\[value1], \\\[value2]) | value1: first sample value or range. value2: more values or ranges. | | `WEIBULL` | Returns the variance based on the population, treating text as 0. | WEIBULL(\\\[x], \\\[shape], \\\[scale], \\\[cumulative]) | x: input. shape: Weibull shape parameter. scale: Weibull scale parameter. cumulative: whether to use cumulative distribution. | | `WEIBULL.DIST` | Returns the value of the Weibull distribution (or its cumulative form) for given shape and scale. | WEIBULL.DIST(\\\[x], \\\[shape], \\\[scale], \\\[cumulative]) | x: input. shape: Weibull shape parameter. scale: Weibull scale parameter. cumulative: whether to use cumulative distribution. | | `Z.TEST` | Returns the value of the Weibull distribution (or its cumulative form) for given shape and scale. | Z.TEST(\\\[data], \\\[value], \\\[std\_dev]) | data: array or range. value: test statistic value for the Z-test. std\_dev: assumed standard deviation; if omitted, the data's standard deviation is used. | | `ZTEST` | Returns the one-tailed P-value of a Z-test against the standard distribution. | ZTEST(\\\[data], \\\[value], \\\[std\_dev]) | data: array or range. value: test statistic value for the Z-test. std\_dev: assumed standard deviation; if omitted, the data's standard deviation is used. | # **Finance** | Function | Description | Example | Arguments | | ------------ | ------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `COUPDAYBS` | Returns the days from the first coupon or interest payment date to the settlement date. | COUPDAYBS(\\\[settlement], \\\[maturity], \\\[frequency], \\\[basis]) | settlement: bond settlement date — when the bond is delivered to the buyer. maturity: bond maturity date — when redemption occurs at par. frequency: coupon payments per year (1, 2, or 4). basis: day-count method to use. | | `COUPDAYSNC` | Returns the days from the settlement date to the next coupon or interest payment date. | COUPDAYSNC(\\\[settlement], \\\[maturity], \\\[frequency], \\\[basis]) | settlement: bond settlement date. maturity: bond maturity date. frequency: coupon payments per year. basis: day-count method. | | `COUPNCD` | Returns the next coupon or interest payment date after the settlement date. | COUPNCD(\\\[settlement], \\\[maturity], \\\[frequency], \\\[basis]) | settlement: bond settlement date. maturity: bond maturity date. frequency: coupon payments per year. basis: day-count method. | | `COUPNUM` | Returns the number of coupons (interest payments) between the settlement and maturity dates. | COUPNUM(\\\[settlement], \\\[maturity], \\\[frequency], \\\[basis]) | settlement: bond settlement date. maturity: bond maturity date. frequency: coupon payments per year. basis: day-count method. | | `COUPPCD` | Returns the previous coupon or interest payment date before the settlement date. | COUPPCD(\\\[settlement], \\\[maturity], \\\[frequency], \\\[basis]) | settlement: bond settlement date. maturity: bond maturity date. frequency: coupon payments per year. basis: day-count method. | | `CUMIPMT` | Returns the cumulative interest paid on an investment over a series of periods, given equal payments and a fixed rate. | CUMIPMT(\\\[rate], \\\[nper], \\\[pv], \\\[start\_period], \\\[end\_period], \\\[type]) | rate: interest rate. nper: number of payment periods. pv: present value. start\_period: first period to include. end\_period: last period to include. type: timing — 0 = end of period, 1 = beginning. | | `CUMPRINC` | Returns the cumulative principal repaid on an investment over a series of periods, given equal payments and a fixed rate. | CUMPRINC(\\\[rate], \\\[nper], \\\[pv], \\\[start\_period], \\\[end\_period], \\\[type]) | rate: interest rate. nper: number of payment periods. pv: present value. start\_period: first period to include. end\_period: last period to include. type: timing — 0 = end of period, 1 = beginning. | | `DB` | Returns the depreciation of an asset for a specified period using the fixed-declining-balance method. | DB(\\\[cost], \\\[salvage], \\\[life], \\\[period], \\\[months]) | cost: initial asset cost. salvage: value at end of useful life. life: useful life in periods. period: the depreciation period. months: months in the first year. | | `DDB` | Returns the depreciation of an asset for a specified period using the double-declining-balance method. | DDB(\\\[cost], \\\[salvage], \\\[life], \\\[period], \\\[factor]) | cost: initial asset cost. salvage: value at end of useful life. life: useful life in periods. period: the depreciation period. factor: rate of decline. | | `DISC` | Returns the discount rate of a bond from its price. | DISC(\\\[settlement], \\\[maturity], \\\[price], \\\[redemption], \\\[basis]) | settlement: bond settlement date. maturity: bond maturity date. price: per $100 face value. redemption: redemption value per $100 face value. basis: day-count method. | | `DOLLARDE` | Converts a price expressed as a fraction to a decimal number. | DOLLARDE(\\\[fractional\_price], \\\[fraction]) | fractional\_price: the price as an agreed fractional form. fraction: denominator size — e.g., 8 for 1/8, 32 for 1/32. | | `DOLLARFR` | Converts a decimal price to a fractional representation. | DOLLARFR(\\\[decimal\_price], \\\[fraction]) | decimal\_price: the price as a decimal. fraction: denominator size — e.g., 8 for 1/8, 32 for 1/32. | | `EFFECT` | Returns the effective annual interest rate from the nominal rate and the number of compounding periods per year. | EFFECT(\\\[nominal\_rate], \\\[periods]) | nominal\_rate: annual nominal rate. periods: compounding periods per year. | | `FV` | Returns the future value of an annuity investment given equal payments and a fixed rate. | FV(\\\[rate], \\\[nper], \\\[pmt], \\\[pv], \\\[type]) | rate: interest rate. nper: number of payment periods. pmt: payment per period. pv: present value. type: timing — 0 = end of period, 1 = beginning. | | `IPMT` | Returns the interest paid in a given period of an investment given equal payments and a fixed rate. | IPMT(\\\[rate], \\\[period], \\\[nper], \\\[pv], \\\[fv], \\\[type]) | rate: interest rate. period: the period (in periods). nper: number of payment periods. pv: present value. fv: remaining future value after final payment. type: timing — 0 = end of period, 1 = beginning. | | `IRR` | Returns the internal rate of return for a series of periodic cash flows. | IRR(\\\[cash\_flows], \\\[rate\_guess]) | cash\_flows: array or range of investment-related amounts. rate\_guess: an estimate of the IRR. | | `ISPMT` | Returns the interest paid during a specific investment period. | ISPMT(\\\[rate], \\\[period], \\\[nper], \\\[pv]) | rate: interest rate. period: the period to view. nper: number of payment periods. pv: present value. | | `NOMINAL` | Returns the nominal annual interest rate from the effective rate and the number of compounding periods per year. | NOMINAL(\\\[effective\_rate], \\\[periods]) | effective\_rate: effective annual rate. periods: compounding periods per year. | | `NPER` | Returns the number of payment periods to repay an investment given equal payments and a fixed rate. | NPER(\\\[rate], \\\[pmt], \\\[pv], \\\[fv], \\\[type]) | rate: interest rate. pmt: payment per period. pv: present value. fv: remaining future value after final payment. type: timing — 0 = end of period, 1 = beginning. | | `NPV` | Returns the net present value of an investment given a series of periodic cash flows and a discount rate. | NPV(\\\[discount\_rate], \\\[cash\_flow1], \\\[cash\_flow2]) | discount\_rate: per-period discount rate. cash\_flow1: first future cash flow. cash\_flow2: more future cash flows. | | `PDURATION` | Returns the number of periods required for an investment to reach a target value. | PDURATION(\\\[rate], \\\[pv], \\\[fv]) | rate: per-period growth rate. pv: present value. fv: target future value. | | `PMT` | Returns the periodic payment for an annuity investment given equal payments and a fixed rate. | PMT(\\\[rate], \\\[nper], \\\[pv], \\\[fv], \\\[type]) | rate: interest rate. nper: number of payment periods. pv: present value. fv: remaining future value after final payment. type: timing — 0 = end of period, 1 = beginning. | | `PPMT` | Returns the principal repayment in a given period of an investment given equal payments and a fixed rate. | PPMT(\\\[rate], \\\[period], \\\[nper], \\\[pv], \\\[fv], \\\[type]) | rate: interest rate. period: the period (in periods). nper: number of payment periods. pv: present value. fv: remaining future value after final payment. type: timing — 0 = end of period, 1 = beginning. | | `PRICEDISC` | Returns the price of a discounted (no-coupon) bond given an expected yield. | PRICEDISC(\\\[settlement], \\\[maturity], \\\[discount], \\\[redemption], \\\[basis]) | settlement: bond settlement date. maturity: bond maturity date. discount: discount rate at purchase. redemption: redemption value per \$100 face value. basis: day-count method. | | `PV` | Returns the present value of an annuity investment given equal payments and a fixed rate. | PV(\\\[rate], \\\[nper], \\\[pmt], \\\[fv], \\\[type]) | rate: interest rate. nper: number of payment periods. pmt: payment per period. fv: remaining future value after final payment. type: timing — 0 = end of period, 1 = beginning. | | `RATE` | Returns the interest rate of an annuity investment given equal payments and fixed terms. | RATE(\\\[nper], \\\[pmt], \\\[pv], \\\[fv], \\\[type], \\\[rate\_guess]) | nper: number of payment periods. pmt: payment per period. pv: present value. fv: remaining future value after final payment. type: timing — 0 = end of period, 1 = beginning. rate\_guess: an estimate of the rate. | | `RECEIVED` | Returns the amount received at maturity for a fixed-income bond purchased on a given date. | RECEIVED(\\\[settlement], \\\[maturity], \\\[investment], \\\[discount], \\\[basis]) | settlement: bond settlement date. maturity: bond maturity date. investment: amount invested (regardless of bond face value). discount: discount rate of the bond. basis: day-count method. | | `RRI` | Returns the interest rate required for an investment to grow to a target value over a number of periods. | RRI(\\\[nper], \\\[pv], \\\[fv]) | nper: number of growth periods. pv: present value. fv: target future value. | | `SLN` | Returns the depreciation of an asset for one period using the straight-line method. | SLN(\\\[cost], \\\[salvage], \\\[life]) | cost: initial asset cost. salvage: value at end of useful life. life: useful life in periods. | | `SYD` | Returns the depreciation of an asset for a specified period using the sum-of-years' digits method. | SYD(\\\[cost], \\\[salvage], \\\[life], \\\[period]) | cost: initial asset cost. salvage: value at end of useful life. life: useful life in periods. period: the depreciation period. | | `VDB` | Returns the depreciation of an asset for a specified period (or partial period). | VDB(\\\[cost], \\\[salvage], \\\[life], \\\[start\_period], \\\[end\_period], \\\[factor], \\\[no\_switch]) | cost: initial asset cost. salvage: value at end of useful life (residual). life: useful life in periods. start\_period: depreciation start period. end\_period: depreciation end period. factor: declining-balance rate (default 2 — double-declining). no\_switch: whether to switch to straight-line when depreciation exceeds the declining-balance value. | | `XIRR` | Returns the internal rate of return for a series of cash flows that may not be periodic. | XIRR(\\\[cash\_flows], \\\[dates], \\\[rate\_guess]) | cash\_flows: array or range of investment-related amounts. dates: array or range of dates corresponding to the cash flows. rate\_guess: an estimate of the IRR. | | `XNPV` | Returns the net present value of cash flows that may not be periodic, given a discount rate. | XNPV(\\\[discount\_rate], \\\[cash\_flows], \\\[dates]) | discount\_rate: per-period discount rate. cash\_flows: range of investment-related amounts. dates: range of dates corresponding to the cash flows. | | `YIELDDISC` | Returns the annual yield of a discounted (no-coupon) bond from its price. | YIELDDISC(\\\[settlement], \\\[maturity], \\\[price], \\\[redemption], \\\[basis]) | settlement: bond settlement date. maturity: bond maturity date. price: per $100 face value. redemption: redemption value per $100 face value. basis: day-count method. | # **Specials** | Function | Description | Example | Arguments | | -------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `FILTER` | Returns a filtered version of the source range — only rows / columns matching the specified condition. Note: some Excel versions don't support this function. Use with caution if exporting to local file. | FILTER(\\\[range], \\\[condition], \\\[value]) | range: data to filter. condition: a row or column matching the first row or column of `range` — booleans or values that coerce to booleans. value: returned when no row matches. | | `UNIQUE` | Returns the unique rows / columns from a source range, removing duplicates. Rows / columns are returned in their first-appearance order. Note: some Excel versions don't support this function. Use with caution if exporting to local file. | UNIQUE(\\\[source\_range], \\\[by\_column], \\\[exactly\_once]) | source\_range: range or array to deduplicate. by\_column: optional, TRUE / FALSE. Default FALSE — deduplicates by row. exactly\_once: optional, TRUE / FALSE. Default FALSE — keeps any unique value (one or many occurrences). |