Function Glossary
- 23 Sep 2024
- 10 Minutes to read
- Print
- DarkLight
Function Glossary
- Updated on 23 Sep 2024
- 10 Minutes to read
- Print
- DarkLight
Article summary
Did you find this summary helpful?
Thank you for your feedback
Overview
This document lists all of the available functions in Formulas.
Array
| Function Name | Description | Formula Syntax | Example |
|---|---|---|---|
| AGGREGATE | Performs a specified calculation (e.g., sum, product, average, etc.) for a list or database, with the option to ignore hidden rows and error values | AGGREGATE(function_num, options, ref1, [ref2], ...) | Formula: aggregate(makearray(1,2,3,4), 0, (acc, x) => acc + x)) Result: 10 |
| Count | Counts the number of items in a collection | Count(collection) | Formula: count(makearray(1, 2, 3)) Result: 3 |
| Find | Returns the first item in a list of objects that meets the criteria | Find(array, condition) | Formula: find(makearray(\"banana\", \"apple\", \"grape\"), x => x == \"apple\") Result: \"apple\" |
| findall | Searches for all occurrences of a specified pattern in a string and returns them as an array. | findall(string, pattern) | Formula: findall(makearray(\"banana\", \"apple\", \"apple\"), x => x == \"apple\") Result: [\"apple\", \"apple\"] |
| IndexOf | Returns the index of the first occurrence of a substring within a string. | INDEXOF(string, substring) | Formula: indexof(makearray(1,2,3) ,1) Result: 0 |
| Join | Takes two lists and returns a concatenated list. | Join(array, array) | Formula: join(makearray(1,2),makearray(3,4)) Result: [1, 2, 3, 4] |
| makearray | Creates an array from a specified list of elements or a range. | makearray(element1, element2, ..., elementN) | Formula: makearray(1,2,3) Result: [1,2,3] |
Conditional
| Function Name | Description | Formula Syntax | Example |
|---|---|---|---|
| If | Returns one value if a condition is true and another value if it is false. | IF(condition, value_if_true, value_if_false) | Formula: if(true,1,2) Result: 1 |
Conversion
| Function Name | Description | Formula Syntax | Example |
|---|---|---|---|
| todouble | Converts a given value to a double-precision floating-point number. | todouble(value) | Formula: todouble(\"2.5\") Result: 2.5 |
| toint | Converts a given value to an integer. | toint(value) | Formula: toint(\"2\") Result: 2 |
Date
| Function Name | Description | Formula Syntax | Example |
|---|---|---|---|
| AddDays | Adds a specified number of days to a date. | AddDays(date, days) | Formula: adddays(now(), 5) Result: 2024-08-19T00:00:00 |
| DateAdd | Adds a specified number of intervals to a date. | DATEADD(date, interval, amount) | Formula: dateadd(now(), "d", 7) Result: 2024-09-25 17:23:23.3441619 |
| DateDiff | Returns the difference between two dates. | DATEDIFF(start_date, end_date, interval) | Formula: datediff("2024-08-01", "2024-08-14", "d") |
| Day | Returns the day component of a date. | DAY(date) | Formula: day(now()) Result: current day of the month |
| Month | Returns the month component of a date. | MONTH(date) | Formula: month(now()) Result: current month |
| Now | Returns the current date and time. | NOW() | Formula: now() Result: current dateTime |
| Today | Returns the current date. | TODAY() | Formula: today() Result: current date |
| Year | Returns the year component of a date. | YEAR(date) | Formula: year(now()) Result: current year |
Financial
| Function Name | Description | Formula Syntax | Example |
|---|---|---|---|
| AMORDEGRC | Calculates the depreciation for each accounting period based on a varying rate. The depreciation is higher at the beginning and decreases over time. | AMORDEGRC( cost, date_purchased, first_period, salvage, period, rate, [basis] ) | Formula: amordegrc(10000, date1, date2, 1000, 1, 0.2, 0) |
| AMORLINC | Calculates the depreciation for each accounting period based on a constant rate. The depreciation amount is the same for each period. | AMORLINC( cost, date_purchased, first_period, salvage, period, rate, [basis] ) | Formula: amorlinc(10000, now(), adddays(now(), 365), 1000, 1, 0.2, 0) Result: 2000 |
| CumIpmt | Calculates the cumulative interest paid between two periods of a loan based on constant payments and a constant interest rate. | CUMIPMT( rate, nper, pv, start_period, end_period, type ) | Formula: cumipmt(0.05,12,1000,1,12,0) Result: -353.90492 |
| CumPrinc | Calculates the cumulative principal paid between two periods of a loan based on constant payments and a constant interest rate. | CUMPRINC( rate, nper, pv, start_period, end_period, type ) | Formula: cumprinc(0.05,12,1000,1,12,0) Result: -999.999999 |
| DB | Calculates the depreciation of an asset for a specified period using the double-declining balance method. | DB( cost, salvage, life, period, [month] ) | Formula: db(1000, 100, 5, 1,12) Result: 369 |
| DDB | Calculates the depreciation of an asset for a specified period using the double-declining balance method, considering the factor. | DDB( cost, salvage, life, period, [factor] ) | Formula: ddb(1000, 100, 5, 1, 12) Result: 900 |
| Duration | Calculates the Macaulay duration of a bond. | DURATION(settlement, maturity, coupon, yield, frequency, basis | Formula: duration(now(), adddays(now(), 1825), 0.05, 0.04, 2, 0) Result: 4.4961258667484545 |
| FV | Calculates the future value of an investment based on periodic payments and a constant interest rate. | FV(rate, nper, pmt, pv) | Formula: fv(0.05, 10, -200, 1000) Result: 886.68 |
| IPMT | Calculates the interest portion of a loan payment for a given period. | IPMT(rate, period, nper, pv, fv) | Formula: ipmt(0.05, 1, 10, -1000, 0) Result: 50 |
| IRR | Calculates the internal rate of return for a series of cash flows. | IRR([array]) | Formula: irr(makearray(-1000, 300, 300, 300, 300)) Result: 0.0771384729520835 |
| NPER | Calculates the number of payment periods for an investment based on periodic payments and a constant interest rate. | IRR([array]) | Formula: irr(makearray(-1000, 300, 300, 300, 300)) Result: 0.0771384729520835 |
| NPV | Calculates the net present value of a series of cash flows. | NPV(rate, [array]) | Formula: npv(0.1,1000, 500, 100) Result: 1397.4455296769345 |
| PMT | Calculates the periodic payment amount for an investment based on constant payments and a constant interest rate. | PMT(rate, nper, pv, fv) | Formula: pmt(0.05, 10, -1000, 0) Result: 129.50 |
| PPMT | Calculates the principal portion of a loan payment for a given period. | PPMT(rate, period, nper, pv, fv) | Formula: ppmt(0.08,10,10,200000,0) Result: -27598.05 |
| PV | Calculates the present value of an investment based on future cash flows and a discount rate. | PV(rate, nper, pmt, fv) | Formula: pv(0.05, 10, -1000, 0) Result: 7721.73 |
| RATE | Calculates the interest rate per period for an investment based on periodic payments, present value, and future value. | RATE(nper, pmt, pv, fv) | Formula: rate(4*12,-200,8000,1,0) Result: 0.00769 |
| SLN | Calculates the straight-line depreciation of an asset for a single period. | SLN( cost, salvage, life ) | Formula: sln(10000, 1000, 5) Result: 1800 |
| SYD | Calculates the sum-of-the-years' digits depreciation of an asset for a specified period. | SYD(cost, salvage, life, per) | Formula: syd(30000, 7500, 10, 1) Result: 4090.9090 |
| VDB | Calculates the depreciation of an asset for a specified period using the variable-declining balance method. | VDB( cost, salvage, life, start_period, end_period, [factor], [no_switch] ) | Formula: vdb(2400, 300, 10, 0, 1, 2) Result: 480 |
| Yield | Calculates the yield of a bond. | YIELD(settlement, maturity, rate, pr, redemption, frequency, basis) | Formula: vdb(2400, 300, 10, 0, 1, 2) Result: 480 |
Hasing
| Function Name | Description | Formula Syntax | Example |
|---|---|---|---|
| hash_basis_points | Generates a hash for the given basis points value. | hash_basis_points(value) | Formula: hash_basis_points(1,2) Result: -0.947 |
| hash_str_basis_points | Generates a hash for the given string basis points value | hash_str_basis_points(value) | Formula: hash_str_basis_points(\"10\",\"20\") Result: -0.3307 |
Logical
| Function Name | Description | Formula Syntax | Example |
|---|---|---|---|
| And | Returns true if all arguments are true, false otherwise. | AND(argument1, argument2, ...) | Formula: and(makearray(true, true, false)) Result: false |
| IfNull | Returns one value if a specified value is null and another value if it is not null. | IFNULL(value, value_if_null) | Formula: ifnull(null, \"No Value\") Result: No Value |
| IsNull | Returns true if a specified value is null, false otherwise. | ISNULL(value) | Formula: isnull(null) Result: true |
| Not | Returns the negation of a logical value. | NOT(logical_value) | Formula: not(true) Result: false |
| Or | Returns true if any argument is true, false otherwise. | OR(argument1, argument2, ...) | Formula: or(makearray(true,false,true)) Result: true |
Math
| Function Name | Description | Formula Syntax | Example |
|---|---|---|---|
| ABS | Returns the absolute value (i.e., the modulus) of a supplied number. | ABS(number) | Formula: abs(-5) Result: 5 |
| ACOS | Returns the Arccosine of a number. | ACOS(number) | Formula: ACOS(-1) |
| ACOSH | Returns the inverse hyperbolic cosine of a number. | ACOSH(number) | Formula: ACOSH(1) Result: 0 |
| ACOT | Returns the arccotangent of a number. | ACOT(number) | Formula: ACOT(1) Result: 0.78539816 |
| ACOTH | Returns the inverse hyperbolic cosine of a number. | ACOTH(number) | Formula: ACOTH(6) Result: 0.16823612 |
| ASIN | Returns the Arcsine of a number. | ASIN(number) | Formula: ASIN(1) Result: 1.57079633 |
| ASINH | Returns the Inverse Hyperbolic Sine of a number. | ASINH(number) | Formula: ASINH(0) Result: 0 |
| ATAN | Returns the Arctangent of a given number. | ATAN(number) | Formula: ATAN(1) Result: 0.7853981633974483 |
| ATANH | Returns the Inverse Hyperbolic Tangent of a given number. | ATANH(number) | Formula: ATANH(0) Result: 0 |
| CBRT | Returns the cube root of a number. | CBRT(number) | Formula: cbrt(8) Result: 2 |
| Ceiling | Rounds a number up to the nearest integer. | Ceiling(number) | Formula: ceiling(3.2) Result: 4 |
| Clamp | Restricts a value to be within a specified range. | Clamp(value, min, max) | Formula: clamp(5, 0, 10) Result: 5 |
| Coalesce | Returns the first non-null value in a list of values. | Coalesce(value1, value2, ...) | Formula: coalesce(null, 5, 10) Result: 5 |
| Coalesce_all | Returns the first non-null value in a list of values. | Coalesce_all(value1, value2, ...) | Formula: coalesce_all(null, 10, 20) Result: 10 |
| Correl | The CORREL function returns the correlation coefficient of two cell ranges. Use the correlation coefficient to determine the relationship between two properties. For example, you can examine the relationship between a location's average temperature and the use of air conditioners. | CORREL(array1, array2) | Formula: correl([1,2,3], [4,5,6]) Result: 1 |
| COS | Returns the Cosine of a given angle. | COS(angle) | Formula: cos(0) Result: 1 |
| COSH | Returns the hyperbolic cosine of a number. | COSH(number) | Formula: cosh(0) Result: 1 |
| COT | Returns the cotangent of an angle. | COT(angle) | Formula: cot(1) Result: 0.6420926159343308 |
| COTH | Returns the hyperbolic cotangent of an angle. | COTH(angle) | Formula: coth(1) Result: 1.3130352854993312 |
| CSC | Returns the cosecant of an angle. | CSC(angle) | Formula: csc(1) Result: 1.1883951057781212 |
| CSCH | Returns the hyperbolic cosecant of an angle. | CSCH(angle) | Formula: csch(1) Result: 0.8509181282393216 |
| DEGREES | Converts Radians to Degrees. | DEGREES(angle) | Formula: degrees(1) Result: 57.29577951308232 |
| Even | Rounds a number away from zero (i.e., rounds a positive number up and a negative number down), to the next even number. | Even(number) | Formula: even(5.5) Result: 6 |
| EXP | Returns Euler's number raised to the power of a specified number. | EXP(number) | Formula: exp(1) Result: 2.718281828459045 |
| Expm1 | Returns Euler's number raised to the power of a specified number minus one. | Expm1(number) | Formula: expm1(1) Result: 1.718281828459045 |
| Floor | Rounds a number down to the nearest integer. | Floor(number) | Formula: floor(3.8) Result: 3 |
| GCD | Returns the Greatest Common Divisor of two or more supplied numbers. | GCD(number1, number2, ...) | Formula: gcd(20, 30) Result: 10 |
| IsInfinite | Checks if a given number is infinite, returning true or false. | IsInfinite(number) | Formula: isinfinite(1) Result: true |
| IsNan | Returns true if the value is NaN (not a number), false otherwise. | IsNan(value) | Formula: isnan(5) Result: false |
| IsNegative | Returns true if the value is negative, false otherwise. | IsNegative(value) | Formula: isnegative(-5) Result: true |
| IsNormal | Returns true if the value is a normal floating-point value, false otherwise. | IsNormal(value) | Formula: isnormal(1.5) Result: true |
| IsPositive | Returns true if the value is positive, false otherwise. | IsPositive(value) | Formula: ispositive(5) Result: true |
| IsPow2 | Returns true if the value is a power of 2, false otherwise. | IsPow2(value) | Formula: ispow2(16) Result: true |
| IsReal | Returns true if the value is a real number, false otherwise. | IsReal(value) | Formula: isreal(5) Result: true |
| IsSubNormal | Returns true if the value is a subnormal floating-point value, false otherwise. | IsSubNormal(value) | Formula: issubnormal(0.1) Result: false |
| LCM | Returns the Least Common Multiple of two or more supplied numbers. | LCM(number1, number2, ...) | Formula: lcm(20, 30) Result: 60 |
| Log | Returns the natural logarithm of a number. | Log(number) | Formula: log(10) Result: 2.302585092994046 |
| Log10 | Returns the base-10 logarithm of a number. | Log10(number) | Formula: log10(100) Result: 2 |
| Log2 | Returns the base-2 logarithm of a number. | Log2(number) | Formula: log2(8) Result: 3 |
| LogB | Returns the logarithm of a number with respect to a specified base. | LogB(base, number) | Formula: logB(2, 8) Result: 3 |
| Logistic | Returns the logistic function value at a given input. | Logistic(x) | Formula: logistic(0) Result: 0.5 |
| Max | Returns the maximum of two or more numbers. | Max(number1, number2, ...) | Formula: max(5, 3, 7) Result: 7 |
| Mean | Calculates the mean (average) of a set of values. | MEAN(value1, value2, ...) | Formula: mean(10, 20, 30, 40) Result: 25 |
| MEDIAN | The median is the middle value in a sorted list of numbers. If there is an odd number of observations, it's the middle value. If there is an even number, it is the average of the two middle values. | If n is odd: MEDIAN(x1, x2, ..., xn) = x((n + 1)/2) If n is even: MEDIAN(x1, x2, ..., xn) = (x(n/2) + x((n/2) + 1)) / 2 | Formula: median(10, 20, 30) Result: 20 |
| Min | Returns the minimum of two or more numbers. | Min(number1, number2, ...) | Formula: min(5, 3, 7) Result: 3 |
| MOD | Returns the remainder from a division between two supplied numbers | MOD(number, divisor) | Formula: mod(10, 3) Result: 1 |
| Mode | Calculates the mode (most frequent value) of a set of values | MODE(value1, value2, ...) | Formula: mode(1, 2, 3, 3) Result: 3 |
| Negate | Returns the negation of a number. | Negate(number) | Formula: negate(5) Result: -5 |
| Odd | Rounds a number away from zero (i.e., rounds a positive number up and a negative number down), to the next odd number. | Odd(number) | Formula: odd(5.5) Result: 5 |
| pow | Returns the result of a given number raised to a supplied power. | POW(number, power) | Formula: pow(2, 3) Result: 8 |
| PRODUCT | This function multiplies all the numbers in a range of cells. | PRODUCT(x1, x2, x3, ..., xn) = x1 * x2 * x3 * ... * xn | Formula: product(2, 3, 4, 5) Result: 120 |
| QUOTIENT | Returns the integer portion of a division between two supplied numbers. | QUOTIENT(numerator, denominator) | Formula: quotient(10, 3) Result: 3 |
| RADIANS | Converts Degrees to Radians | RADIANS(angle) | Formula: radians(180) Result: 3.141592653589793 |
| Random | Generates a random number below the provided max number. | random(max) | Formula: random(20) Result: 18 |
| Round | Rounds a number up or down, to a given number of digits. | Round(number, num_digits) | Formula: round(5.555, 2) Result: 5.56 |
| RoundDec | Rounds a number to a specified number of decimal places. | rounddec(value,[places]) | |
| RoundDown | Rounds a number towards zero (i.e., rounds a positive number down and a negative number up), to a given number of digits. | RoundDown(number, num_digits) | Formula: rounddown(5.555, 2) Result: 5.55 |
| RoundUp | Rounds a number away from zero (i.e., rounds a positive number up and a negative number down), to a given number of digits. | RoundUp(number, num_digits) | Formula: roundup(5.555, 2) Result: 5.56 |
| SEC | Returns the secant of an angle. | SEC(angle) | Formula: sec(1) Result: 1.8505157176809255 |
| SECH | Returns the hyperbolic secant of an angle. | SECH(angle) | Formula: sech(0) Result: 0 |
| SIGN | Returns the sign (+1, -1, or 0) of a supplied number | SIGN(number) | Formula: sign(-5) Result: -1 |
| SIN | Returns the Sine of a given angle. | SIN(angle) | Formula: sin(1) Result: 0.8414709848078965 |
| SINH | Returns the Hyperbolic Sine of a number. | SINH(number) | Formula: sinh(0) Result: 0 |
| Slope | Returns the slope of the linear regression line through data points in known_y's and known_x's. The slope is the vertical distance divided by the horizontal distance between any two points on the line, which is the rate of change along the regression line. | SLOPE(List,List) | Formula: slope([1,3], [9,6]) Result: -1.5 |
| SQRT | Returns the positive square root of a given number. | SQRT(number) | Formula: sqrt(16) Result: 4 |
| SQRTPI | Returns the square root of a supplied number multiplied by pi. | SQRTPI(number) | Formula: sqrtpi(4) Result: 3.5449077018110318 |
| Sum | Returns the sum of a supplied list of numbers. | SUM(number1, number2, ...) | Formula: sum(1, 2, 3) Result: 6 |
| SumIf | Adds the cells in a supplied range that satisfy a given criteria. | SUMIF(range, criteria, [sum_range]) | Formula: sumif(makearray(1, 2, 3), x => x > 2) Result: 3 |
| SUMPRODUCT | This function multiplies the corresponding components in the given arrays (or ranges) and returns the sum of those products. | SUMPRODUCT(array1, array2) = Σ(array1[i] * array2[i]) Where: - Σ denotes the sum over all elements i - array1[i] and array2[i] are corresponding elements in the arrays or ranges. | Formula: sumproduct(makearray(1, 3), makearray(9, 6)) Result: 27 |
| TAN | Returns the Tangent of a given angle. | TAN(angle) | Formula: tan(1) Result: 1.5574077246549023 |
| TANH | Returns the Hyperbolic Tangent of a given number. | TANH(number) | Formula: tanh(0) Result: 1 |
| Trunc | Truncates a number towards zero (i.e., rounds a positive number down and a negative number up), to the next integer. | Trunc(number) | Formula: trunc(5.555) Result: 5 |
Statistical
| Function Name | Description | Formula Syntax | Example |
|---|---|---|---|
| AVEDEV | Average Deviation: It measures the average of the absolute deviations of data points from their mean. It is calculated by summing the absolute differences between each data point and the mean, then dividing by the number of data points. | AVEDEV(x1, x2, ..., xn) = (Σ|xi - μ|) / n Where: - Σ denotes the sum from i = 1 to n - |xi - μ| is the absolute deviation of each value from the mean - μ is the mean of the dataset - n is the number of values in the dataset | Formula: avedev(2,4) Result: 1 |
| Percentile | Calculates the value at a specified percentile in a dataset. | PERCENTILE(data, percentile) | Formula: percentile([2,4], 0.5) Result: 3 |
| Quartile | Calculates the value at a specified quartile in a dataset. | QUARTILE(data, quartile) | Formula: quartile([2,4], 3) Result: 3.5 |
| stdevp | Calculates the standard deviation based on the entire population. | stdevp(array) | Formula: stdevp(2,4) Result: 1 |
| stdevs | Calculates the standard deviation based on a sample. | stdevs(array) | Formula: stdevs(2,4) Result: 1.4142135623730951 |
| Variancep | Computes the variance based on the entire population. | Variancep(array) | Formula: variancep(2,4) Result: 1 |
| variances | Computes the variance based on a sample. | variances(array) | Formula: variances(2,4) Result: 2 |
String
| Function Name | Description | Formula Syntax | Example |
|---|---|---|---|
| CONTCAT | Concatenates two or more strings. | CONCAT(string1, string2, ...) | Formula: concat("Hello", " ", "World") Result: Hello World |
| contains | Checks if a specified substring exists within a string, returning true or false. | contains(string, substring, caseinsensitive) | Formula: contains("Hello", "H", true) Result: true |
| indexofsubstring | Returns the index of the first occurrence of a specified substring within a string. | indexofsubstring(string, substring) | Formula: indexofsubstring(\"Hello\", \"D\") Result: -1 Formula: indexofsubstring(\"Hello\", \"H\") Result: 0 |
| JoinStrings | Concatenates all elements of a collection into a single string, using a specified separator. | Join(collection, separator) | Formula: joinstrings(["Hello","World"], ".") Result: "Hello.World" |
| Length | Returns the length of a string. | LENGTH(string) | Formula: length("Hello World") Result: 11 |
| Lower | Converts a string to lowercase. | LOWER(string) | Formula: lower("HELLO") Result: hello |
| Replace | Replaces all occurrences of a substring within a string with another substring. | REPLACE(string, old_substring, new_substring) | Formula: replace(\"Hello World\", \"World\", \"Universe\") Result: "Hello Universe" |
| Substring | Returns a substring from a string. | SUBSTRING(string, start_index, length) | Formula: substring("Hello World", 6, 5) Result: "World" |
| Trim | Removes leading and trailing whitespace from a string. | TRIM(string) | Formula: trim(" Hello World ") Result: "Hello World" |
| Upper | Converts a string to uppercase. | UPPER(string) | Formula: upper("hello") Result: "HELLO" |
Was this article helpful?
