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