See all of the supported formulas for your Formula Workflow action block.
Read more about how to use the Formula blockFormulas allowing you to manipulate dates and time, such as adding days to a date, calculating a date in the future, or extracting the day of the week from a date.
| Formula | Description | Example | Expected return |
|---|---|---|---|
| DATE | Creates a date from the year, month, and day arguments. | DATE(2008, 7, 8) | Tue Jul 08 2008 00:00:00 GMT-0700 (PDT) |
| DATEVALUE | Converts a date in the form of text to a serial number. | DATEVALUE('8/22/2011') | Mon Aug 22 2011 00:00:00 GMT-0700 (PDT) |
| DAY | Returns the day of the month, a number from 1 to 31. | DAY('15-Apr-11') | 15 |
| DAYS | Returns the number of days between two dates. | DAYS('3/15/11', '2/1/11') | 42 |
| DAYS360 | Calculates the number of days between two dates based on a 360-day year. | DAYS360('1-Jan-11', '31-Dec-11') | 360 |
| EDATE | Returns the date that is the specified number of months before or after the start date. | EDATE('1/15/11', -1) | Wed Dec 15 2010 00:00:00 GMT-0800 (PST) |
| EOMONTH | Returns the last day of the month before or after a specified number of months. | EOMONTH('1/1/11', -3) | Sun Oct 31 2010 00:00:00 GMT-0700 (PDT) |
| HOUR | Returns the hour of a time value. | HOUR('7/18/2011 7:45:00 AM') | 7 |
| MINUTE | Returns the minute of a time value. | MINUTE('2/1/2011 12:45:00 PM') | 45 |
| ISOWEEKNUM | Returns the ISO week number of the year for a given date. | ISOWEEKNUM('3/9/2012') | 10 |
| MONTH | Returns the month of a date value. | MONTH('15-Apr-11') | 4 |
| NETWORKDAYS | Returns the number of whole working days between two dates, excluding weekends and specified holidays. | NETWORKDAYS('10/1/2012', '3/1/2013', ['11/22/2012']) | 109 |
| NOW | Returns the current date and time. | NOW() | Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time) |
| SECOND | Returns the second of a time value. | SECOND('2/1/2011 4:48:18 PM') | 18 |
| TIME | Returns the decimal number for a particular time. | TIME(16, 48, 10) | 0.7001157407407408 |
| TIMEVALUE | Converts a time in the form of text to a serial number. | TIMEVALUE('22-Aug-2011 6:35 AM') | 0.2743055555555556 |
| TODAY | Returns the current date. | TODAY() | Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time) |
| WEEKDAY | Returns the day of the week as a number (1 for Sunday, 2 for Monday, etc.). | WEEKDAY('2/14/2008', 3) | 3 |
| YEAR | Returns the year of a date value. | YEAR('7/5/2008') | 2008 |
| WEEKNUM | Returns the week number of a date based on the specified return type. | WEEKNUM('3/9/2012', 2) | 11 |
| WORKDAY | Returns the date before or after a specified number of workdays, excluding weekends and holidays. | WORKDAY('10/1/2008', 151, ['11/26/2008', '12/4/2008']) | Mon May 04 2009 00:00:00 GMT-0700 (PDT) |
| YEARFRAC | Calculates the fraction of the year represented by the number of whole days between two dates. | YEARFRAC('1/1/2012', '7/30/2012', 3) | 0.5780821917808219 |
Financial formulas allow you to calculate financial metrics such as interest, depreciation, and more.
| Formula | Description | Example | Expected return |
|---|---|---|---|
| ACCRINT | Returns the accrued interest for a security that pays periodic interest. | ACCRINT('01/01/2011', '02/01/2011', '07/01/2014', 0.1, 1000, 1, 0) | 350 |
| CUMIPMT | Returns the cumulative interest paid on a loan between two periods. | CUMIPMT(0.1/12, 30*12, 100000, 13, 24, 0) | -9916.77251395708 |
| CUMPRINC | Returns the cumulative principal paid on a loan between two periods. | CUMPRINC(0.1/12, 30*12, 100000, 13, 24, 0) | -614.0863271085149 |
| DB | Returns the depreciation of an asset for a specified period using the fixed-declining balance method. | DB(1000000, 100000, 6, 1, 6) | 159500 |
| DDB | Returns the depreciation of an asset for a specified period using the double-declining balance method. | DDB(1000000, 100000, 6, 1, 1.5) | 250000 |
| DOLLARDE | Converts a dollar price expressed as a fraction into a dollar price expressed as a decimal number. | DOLLARDE(1.1, 16) | 1.625 |
| DOLLARFR | Converts a dollar price expressed as a decimal number into a dollar price expressed as a fraction. | DOLLARFR(1.625, 16) | 1.1 |
| EFFECT | Returns the effective annual interest rate given the nominal rate and the number of compounding periods per year. | EFFECT(0.1, 4) | 0.10381289062499977 |
| FV | Returns the future value of an investment based on periodic, constant payments and a constant interest rate. | FV(0.1/12, 10, -100, -1000, 0) | 2124.874409194097 |
| FVSCHEDULE | Returns the future value of an initial principal after applying a series of compound interest rates. | FVSCHEDULE(100, [0.09,0.1,0.11]) | 133.08900000000003 |
| IPMT | Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. | IPMT(0.1/12, 6, 2*12, 100000, 1000000, 0) | 928.8235718400465 |
| IRR | Returns the internal rate of return for a series of cash flows. | IRR([-75000,12000,15000,18000,21000,24000], 0.075) | 0.05715142887178447 |
| ISPMT | Calculates the interest paid during a specific period of a loan or investment. | ISPMT(0.1/12, 6, 2*12, 100000) | -625 |
| MIRR | Returns the modified internal rate of return for a series of cash flows, considering both cost of investment and interest received on reinvestment. | MIRR([-75000,12000,15000,18000,21000,24000], 0.1, 0.12) | 0.07971710360838036 |
| NOMINAL | Returns the nominal annual interest rate given the effective rate and number of compounding periods per year. | NOMINAL(0.1, 4) | 0.09645475633778045 |
| NPER | Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. | NPER(0.1/12, -100, -1000, 10000, 0) | 63.39385422740764 |
| NPV | Calculates the net present value of an investment based on a discount rate and a series of future payments and income. | NPV(0.1, -10000, 2000, 4000, 8000) | 1031.3503176012546 |
| PDURATION | Returns the number of periods required for an investment to reach a specified value. | PDURATION(0.1, 1000, 2000) | 7.272540897341714 |
| PMT | Calculates the payment for a loan based on constant payments and a constant interest rate. | PMT(0.1/12, 2*12, 100000, 1000000, 0) | -42426.08563793503 |
| PPMT | Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate. | PPMT(0.1/12, 6, 2*12, 100000, 1000000, 0) | -43354.909209775076 |
| PV | Returns the present value of an investment: the total amount that a series of future payments is worth now. | PV(0.1/12, 2*12, 1000, 10000, 0) | -29864.950264779152 |
| RATE | Returns the interest rate per period of an annuity. | RATE(2*12, -1000, -10000, 100000, 0, 0.1) | 0.06517891177181533 |
Engineering formulas allow you to calculate engineering metrics such as voltage, current, resistance, and more.
| Formula | Description | Example | Expected return |
|---|---|---|---|
| BIN2DEC | Converts a binary number to decimal. | BIN2DEC(101010) | 42 |
| BIN2HEX | Converts a binary number to hexadecimal. | BIN2HEX(101010) | 2a |
| BIN2OCT | Converts a binary number to octal. | BIN2OCT(101010) | 52 |
| BITAND | Returns a bitwise AND of two numbers. | BITAND(42, 24) | 8 |
| BITLSHIFT | Returns a number shifted left by a specified number of bits. | BITLSHIFT(42, 24) | 704643072 |
| BITOR | Returns a bitwise OR of two numbers. | BITOR(42, 24) | 58 |
| BITRSHIFT | Returns a number shifted right by a specified number of bits. | BITRSHIFT(42, 2) | 10 |
| BITXOR | Returns a bitwise XOR of two numbers. | BITXOR(42, 24) | 50 |
| COMPLEX | Converts real and imaginary coefficients into a complex number. | COMPLEX(3, 4) | 3+4i |
| CONVERT | Converts a number from one measurement system to another. | CONVERT(64, 'kibyte', 'bit') | 524288 |
| DEC2BIN | Converts a decimal number to binary. | DEC2BIN(42) | 101010 |
| DEC2HEX | Converts a decimal number to hexadecimal. | DEC2HEX(42) | 2a |
| DEC2OCT | Converts a decimal number to octal. | DEC2OCT(42) | 52 |
| DELTA | Tests whether two numbers are equal. | DELTA(42, 42) | 1 |
| ERF | Returns the error function integrated between 0 and a specified value. | ERF(1) | 0.8427007929497149 |
| ERFC | Returns the complementary error function integrated between a specified value and infinity. | ERFC(1) | 0.1572992070502851 |
| GESTEP | Tests whether a number is greater than a specified threshold. | GESTEP(42, 24) | 1 |
| HEX2BIN | Converts a hexadecimal number to binary. | HEX2BIN('2a') | 101010 |
| HEX2DEC | Converts a hexadecimal number to decimal. | HEX2DEC('2a') | 42 |
| HEX2OCT | Converts a hexadecimal number to octal. | HEX2OCT('2a') | 52 |
| IMABS | Returns the absolute value (modulus) of a complex number. | IMABS('3+4i') | 5 |
| IMAGINARY | Returns the imaginary coefficient of a complex number. | IMAGINARY('3+4i') | 4 |
| IMARGUMENT | Returns the argument theta, an angle expressed in radians. | IMARGUMENT('3+4i') | 0.9272952180016122 |
| IMCONJUGATE | Returns the complex conjugate of a complex number. | IMCONJUGATE('3+4i') | 3-4i |
| IMCOS | Returns the cosine of a complex number. | IMCOS('1+i') | 0.8337300251311491-0.9888977057628651i |
| IMCOSH | Returns the hyperbolic cosine of a complex number. | IMCOSH('1+i') | 0.8337300251311491+0.9888977057628651i |
| IMCOT | Returns the cotangent of a complex number. | IMCOT('1+i') | 0.21762156185440265-0.8680141428959249i |
| IMCSC | Returns the cosecant of a complex number. | IMCSC('1+i') | 0.6215180171704283-0.3039310016284264i |
| IMCSCH | Returns the hyperbolic cosecant of a complex number. | IMCSCH('1+i') | 0.3039310016284264-0.6215180171704283i |
| IMDIV | Returns the quotient of two complex numbers. | IMDIV('1+2i', '3+4i') | 0.44+0.08i |
| IMEXP | Returns the exponential of a complex number. | IMEXP('1+i') | 1.4686939399158851+2.2873552871788423i |
| IMLN | Returns the natural logarithm of a complex number. | IMLN('1+i') | 0.3465735902799727+0.7853981633974483i |
| IMLOG10 | Returns the base-10 logarithm of a complex number. | IMLOG10('1+i') | 0.1505149978319906+0.3410940884604603i |
| IMLOG2 | Returns the base-2 logarithm of a complex number. | IMLOG2('1+i') | 0.5000000000000001+1.1330900354567985i |
| IMPOWER | Returns a complex number raised to an integer power. | IMPOWER('1+i', 2) | 1.2246063538223775e-16+2.0000000000000004i |
| IMPRODUCT | Returns the product of multiple complex numbers. | IMPRODUCT('1+2i', '3+4i', '5+6i') | -85+20i |
| IMREAL | Returns the real coefficient of a complex number. | IMREAL('3+4i') | 3 |
| IMSEC | Returns the secant of a complex number. | IMSEC('1+i') | 0.4983370305551868+0.591083841721045i |
| IMSECH | Returns the hyperbolic secant of a complex number. | IMSECH('1+i') | 0.4983370305551868-0.591083841721045i |
| IMSIN | Returns the sine of a complex number. | IMSIN('1+i') | 1.2984575814159773+0.6349639147847361i |
| IMSINH | Returns the hyperbolic sine of a complex number. | IMSINH('1+i') | 0.6349639147847361+1.2984575814159773i |
| IMSQRT | Returns the square root of a complex number. | IMSQRT('1+i') | 1.0986841134678098+0.45508986056222733i |
| IMSUB | Returns the difference between two complex numbers. | IMSUB('3+4i', '1+2i') | 2+2i |
| IMSUM | Returns the sum of multiple complex numbers. | IMSUM('1+2i', '3+4i', '5+6i') | 9+12i |
| IMTAN | Returns the tangent of a complex number. | IMTAN('1+i') | 0.2717525853195117+1.0839233273386946i |
| OCT2BIN | Converts an octal number to binary. | OCT2BIN('52') | 101010 |
| OCT2DEC | Converts an octal number to decimal. | OCT2DEC('52') | 42 |
| OCT2HEX | Converts an octal number to hexadecimal. | OCT2HEX('52') | 2a |
Logical formulas allow you to perform logical operations such as AND, OR, and NOT.
| Formulas | Description | Example | Expected return |
|---|---|---|---|
| AND | Returns TRUE if all arguments evaluate to TRUE. | AND(true, false, true) | false |
| FALSE | Returns the logical value FALSE. | FALSE() | false |
| IF | Returns one value if a condition is TRUE and another value if it is FALSE. | IF(true, 'Hello!', 'Goodbye!') | Hello! |
| NOT | Reverses the logical value of its argument. | NOT(true) | false |
| OR | Returns TRUE if any argument evaluates to TRUE. | OR(true, false, true) | true |
| SWITCH | Evaluates an expression against a list of values and returns the corresponding result for the first match. | SWITCH(7, 9, 'Nine', 7, 'Seven') | Seven |
| TRUE | Returns the logical value TRUE. | TRUE() | true |
| XOR | Returns TRUE if an odd number of arguments are TRUE, and FALSE otherwise. | XOR(true, false, true) | false |
Perform mathematical operations such as addition, subtraction, multiplication, and more.
| Formulas | Description | Example | Expected return |
|---|---|---|---|
| ABS | Returns the absolute value of a number. | ABS(-4) | 4 |
| ACOS | Returns the arccosine of a number in radians. | ACOS(-0.5) | 2.0943951023931957 |
| ACOSH | Returns the inverse hyperbolic cosine of a number. | ACOSH(10) | 2.993222846126381 |
| ACOT | Returns the arccotangent of a number. | ACOT(2) | 0.46364760900080615 |
| ACOTH | Returns the inverse hyperbolic cotangent of a number. | ACOTH(6) | 0.16823611831060645 |
| AGGREGATE | Returns an aggregate in a list or database. | AGGREGATE(9, 4, [-5,15], [32,'Hello World!']) | 10,32 |
| ARABIC | Converts a Roman numeral to an Arabic numeral. | ARABIC('MCMXII') | 1912 |
| ASIN | Returns the arcsine of a number in radians. | ASIN(-0.5) | -0.5235987755982988 |
| ASINH | Returns the inverse hyperbolic sine of a number. | ASINH(-2.5) | -1.6472311463710965 |
| ATAN | Returns the arctangent of a number in radians. | ATAN(1) | 0.7853981633974483 |
| ATAN2 | Returns the arctangent of the quotient of its arguments. | ATAN2(-1, -1) | -2.356194490192345 |
| ATANH | Returns the inverse hyperbolic tangent of a number. | ATANH(-0.1) | -0.10033534773107562 |
| BASE | Converts a number into a text representation with the given radix (base). | BASE(15, 2, 10) | 0000001111 |
| CEILING | Rounds a number up to the nearest multiple of significance. | CEILING(-5.5, 2, -1) | -6 |
| CEILINGMATH | Rounds a number up, to the nearest integer or to the nearest multiple of significance. | CEILINGMATH(-5.5, 2, -1) | -6 |
| CEILINGPRECISE | Rounds a number up to the nearest multiple of significance, regardless of sign. | CEILINGPRECISE(-4.1, -2) | -4 |
| COMBIN | Returns the number of combinations for a given number of objects. | COMBIN(8, 2) | 28 |
| COMBINA | Returns the number of combinations with repetitions for a given number of objects. | COMBINA(4, 3) | 20 |
| COS | Returns the cosine of a number. | COS(1) | 0.5403023058681398 |
| COSH | Returns the hyperbolic cosine of a number. | COSH(1) | 1.5430806348152437 |
| COT | Returns the cotangent of an angle. | COT(30) | -0.15611995216165922 |
| COTH | Returns the hyperbolic cotangent of a number. | COTH(2) | 1.0373147207275482 |
| CSC | Returns the cosecant of an angle. | CSC(15) | 1.5377805615408537 |
| CSCH | Returns the hyperbolic cosecant of a number. | CSCH(1.5) | 0.46964244059522464 |
| DECIMAL | Converts a text representation of a number in a given base into a decimal number. | DECIMAL('FF', 16) | 255 |
| ERF | Returns the error function integrated between 0 and a specified value. | ERF(1) | 0.8427007929497149 |
| ERFC | Returns the complementary error function integrated between a specified value and infinity. | ERFC(1) | 0.1572992070502851 |
| EVEN | Rounds a number up to the nearest even integer. | EVEN(-1) | -2 |
| EXP | Returns e raised to the power of a given number. | EXP(1) | 2.718281828459045 |
| FACT | Returns the factorial of a number. | FACT(5) | 120 |
| FACTDOUBLE | Returns the double factorial of a number. | FACTDOUBLE(7) | 105 |
| FLOOR | Rounds a number down towards zero, to the nearest multiple of significance. | FLOOR(-3.1) | -4 |
| GCD | Returns the greatest common divisor of two or more integers. | GCD(24, 36, 48) | 12 |
| INT | Rounds a number down to the nearest integer. | INT(-8.9) | -9 |
| ISEVEN | Returns TRUE if the number is even. | ISEVEN(-2.5) | true |
| ISODD | Returns TRUE if the number is odd. | ISODD(-2.5) | false |
| LCM | Returns the least common multiple of integers. | LCM(24, 36, 48) | 144 |
| LN | Returns the natural logarithm of a number. | LN(86) | 4.454347296253507 |
| LOG | Returns the logarithm of a number to a specified base. | LOG(8, 2) | 3 |
| LOG10 | Returns the base-10 logarithm of a number. | LOG10(100000) | 5 |
| MOD | Returns the remainder after a number is divided by a divisor. | MOD(3, -2) | -1 |
| MROUND | Rounds a number to the nearest multiple of a specified value. | MROUND(-10, -3) | -9 |
| MULTINOMIAL | Returns the multinomial of a set of numbers. | MULTINOMIAL(2, 3, 4) | 1260 |
| ODD | Rounds a number up to the nearest odd integer. | ODD(-1.5) | -3 |
| POWER | Returns the result of a number raised to a power. | POWER(5, 2) | 25 |
| PRODUCT | Multiplies all the numbers given as arguments and returns the product. | PRODUCT(5, 15, 30) | 2250 |
| QUOTIENT | Returns the integer portion of a division. | QUOTIENT(-10, 3) | -3 |
| RADIANS | Converts degrees to radians. | RADIANS(180) | 3.141592653589793 |
| RAND | Returns a random real number greater than or equal to 0 and less than 1. | RAND() | [Random real number between 0 and 1] |
| RANDBETWEEN | Returns a random integer between the specified numbers. | RANDBETWEEN(-1, 1) | [Random integer between bottom and top] |
| ROUND | Rounds a number to a specified number of digits. | ROUND(626.3, -3) | 1000 |
| ROUNDDOWN | Rounds a number down towards zero. | ROUNDDOWN(-3.14159, 2) | -3.14 |
| ROUNDUP | Rounds a number up, away from zero. | ROUNDUP(-3.14159, 2) | -3.15 |
| SEC | Returns the secant of an angle. | SEC(45) | 1.9035944074044246 |
| SECH | Returns the hyperbolic secant of a number. | SECH(45) | 5.725037161098787e-20 |
| SIGN | Returns the sign of a number: 1 if positive, -1 if negative, or 0. | SIGN(-0.00001) | -1 |
| SIN | Returns the sine of an angle. | SIN(1) | 0.8414709848078965 |
| SINH | Returns the hyperbolic sine of a number. | SINH(1) | 1.1752011936438014 |
| SQRT | Returns the square root of a number. | SQRT(16) | 4 |
| SQRTPI | Returns the square root of (number * pi). | SQRTPI(2) | 2.5066282746310002 |
| SUBTOTAL | Returns a subtotal in a list or database. | SUBTOTAL(9, [-5,15], [32,'Hello World!']) | 10,32 |
| SUM | Adds all the numbers in a range of cells. | SUM(-5, 15, 32, 'Hello World!') | 42 |
| SUMIF | Adds the cells specified by a given condition or criteria. | SUMIF([2,4,8,16], '>5') | 24 |
| SUMIFS | Adds the cells in a range that meet multiple criteria. | SUMIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4') | 12 |
| SUMPRODUCT | Returns the sum of the products of corresponding ranges or arrays. | SUMPRODUCT([[1,2],[3,4]], [[1,0],[0,1]]) | 5 |
| SUMSQ | Returns the sum of the squares of the arguments. | SUMSQ(3, 4) | 25 |
| SUMX2MY2 | Returns the sum of the difference of squares of corresponding values in two arrays. | SUMX2MY2([1,2], [3,4]) | -20 |
| SUMX2PY2 | Returns the sum of the sum of squares of corresponding values in two arrays. | SUMX2PY2([1,2], [3,4]) | 30 |
| SUMXMY2 | Returns the sum of squares of differences of corresponding values in two arrays. | SUMXMY2([1,2], [3,4]) | 8 |
| TAN | Returns the tangent of an angle. | TAN(1) | 1.5574077246549023 |
| TANH | Returns the hyperbolic tangent of a number. | TANH(-2) | -0.9640275800758168 |
| TRUNC | Truncates a number to an integer by removing the fractional part of the number. | TRUNC(-8.9) | -8 |
Statistical formulas allow you to calculate statistical metrics such as averages, medians, and more.
| Formula | Description | Example | Expected return |
|---|---|---|---|
| AVEDEV | Returns the average of the absolute deviations of data points from their mean. | AVEDEV([2,4], [8,16]) | 4.5 |
| AVERAGE | Returns the average of its arguments. | AVERAGE([2,4], [8,16]) | 7.5 |
| AVERAGEA | Returns the average of its arguments, evaluating text and FALSE as 0, and TRUE as 1. | AVERAGEA([2,4], [8,16]) | 7.5 |
| AVERAGEIF | Returns the average of all cells that meet a given criteria. | AVERAGEIF([2,4,8,16], '>5', [1, 2, 3, 4]) | 3.5 |
| AVERAGEIFS | Returns the average of all cells that meet multiple criteria. | AVERAGEIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4') | 6 |
| BETADIST | Returns the beta cumulative distribution function. | BETADIST(2, 8, 10, true, 1, 3) | 0.6854705810117458 |
| BETAINV | Returns the inverse of the cumulative beta distribution. | BETAINV(0.6854705810117458, 8, 10, 1, 3) | 1.9999999999999998 |
| BINOMDIST | Returns the individual term binomial distribution probability. | BINOMDIST(6, 10, 0.5, false) | 0.205078125 |
| CORREL | Returns the correlation coefficient between two data sets. | CORREL([3,2,4,5,6], [9,7,12,15,17]) | 0.9970544855015815 |
| COUNT | Counts the number of cells that contain numbers. | COUNT([1,2], [3,4]) | 4 |
| COUNTA | Counts the number of cells that are not empty. | COUNTA([1, null, 3, 'a', '', 'c']) | 4 |
| COUNTBLANK | Counts the number of empty cells in a range. | COUNTBLANK([1, null, 3, 'a', '', 'c']) | 2 |
| COUNTIF | Counts the number of cells that meet a condition. | COUNTIF(['Caen', 'Melbourne', 'Palo Alto', 'Singapore'], 'a') | 3 |
| COUNTIFS | Counts the number of cells that meet multiple criteria. | COUNTIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4') | 2 |
| COUNTUNIQUE | Counts the number of unique values in a range. | COUNTUNIQUE([1,1,2,2,3,3]) | 3 |
| DEVSQ | Calculates the sum of squares of deviations of data points from their sample mean. | DEVSQ([2,4,8,16]) | 115 |
| EXPONDIST | Returns the exponential distribution. | EXPONDIST(0.2, 10, true) | 0.8646647167633873 |
| FDIST | Returns the F probability distribution. | FDIST(15.2069, 6, 4, false) | 0.0012237917087831735 |
| FINV | Returns the inverse of the F probability distribution. | FINV(0.01, 6, 4) | 0.10930991412457851 |
| FISHER | Returns the Fisher transformation at x. | FISHER(0.75) | 0.9729550745276566 |
| FISHERINV | Returns the inverse of the Fisher transformation. | FISHERINV(0.9729550745276566) | 0.75 |
| FORECAST | Calculates or predicts a future value based on existing values. | FORECAST(30, [6,7,9,15,21], [20,28,31,38,40]) | 10.607253086419755 |
| FREQUENCY | Calculates how often values occur within a range of values, and returns a vertical array of numbers. | FREQUENCY([79,85,78,85,50,81,95,88,97], [70,79,89]) | 1,2,4,2 |
| GAMMA | Returns the gamma function value. | GAMMA(2.5) | 1.3293403919101043 |
| GAMMALN | Returns the natural logarithm of the gamma function. | GAMMALN(10) | 12.801827480081961 |
| GAUSS | Returns the probability that a standard normal variable is less than z. | GAUSS(2) | 0.4772498680518208 |
| GEOMEAN | Returns the geometric mean of an array or range of positive data. | GEOMEAN([2,4], [8,16]) | 5.656854249492381 |
| GROWTH | Calculates predicted exponential growth by using existing data. | GROWTH([2,4,8,16], [1,2,3,4], [5]) | 32.00000000000003 |
| HARMEAN | Returns the harmonic mean of a data set. | HARMEAN([2,4], [8,16]) | 4.266666666666667 |
| HYPGEOMDIST | Returns the hypergeometric distribution. | HYPGEOMDIST(1, 4, 8, 20, false) | 0.3632610939112487 |
| INTERCEPT | Calculates the point at which a line will intersect the y-axis by using known x and y values. | INTERCEPT([2,3,9,1,8], [6,5,11,7,5]) | 0.04838709677419217 |
| KURT | Returns the kurtosis of a data set. | KURT([3,4,5,2,3,4,5,6,4,7]) | -0.15179963720841627 |
| LARGE | Returns the k-th largest value in a data set. | LARGE([3,5,3,5,4,4,2,4,6,7], 3) | 5 |
| LINEST | Returns the parameters of a linear trend. | LINEST([1,9,5,7], [0,4,2,3], true, true) | 2,1 |
| LOGNORMDIST | Returns the cumulative log-normal distribution of x. | LOGNORMDIST(4, 3.5, 1.2, true) | 0.0390835557068005 |
| LOGNORMINV | Returns the inverse of the log-normal cumulative distribution. | LOGNORMINV(0.0390835557068005, 3.5, 1.2, true) | 4.000000000000001 |
| MAX | Returns the maximum value in a set of values. | MAX([0.1,0.2], [0.4,0.8], [true, false]) | 0.8 |
| MAXA | Returns the maximum value in a set of values, including text and logical values. | MAXA([0.1,0.2], [0.4,0.8], [true, false]) | 1 |
| MEDIAN | Returns the median of the given numbers. | MEDIAN([1,2,3], [4,5,6]) | 3.5 |
| MIN | Returns the minimum value in a set of values. | MIN([0.1,0.2], [0.4,0.8], [true, false]) | 0.1 |
| MINA | Returns the minimum value in a set of values, including text and logical values. | MINA([0.1,0.2], [0.4,0.8], [true, false]) | 0 |
| MODEMULT | Returns a vertical array of the most frequently occurring, or repetitive, values in an array or range of data. | MODEMULT([1,2,3,4,3,2,1,2,3]) | 2,3 |
| MODESNGL | Returns the most frequently occurring, or repetitive, value in an array or range of data. | MODESNGL([1,2,3,4,3,2,1,2,3]) | 2 |
| NORMDIST | Returns the normal cumulative distribution for a specified mean and standard deviation. | NORMDIST(42, 40, 1.5, true) | 0.9087887802741321 |
| NORMINV | Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. | NORMINV(0.9087887802741321, 40, 1.5) | 42 |
| NORMSDIST | Returns the standard normal cumulative distribution. | NORMSDIST(1, true) | 0.8413447460685429 |
| NORMSINV | Returns the inverse of the standard normal cumulative distribution. | NORMSINV(0.8413447460685429) | 1.0000000000000002 |
| PEARSON | Returns the Pearson product moment correlation coefficient. | PEARSON([9,7,5,3,1], [10,6,1,5,3]) | 0.6993786061802354 |
| PERCENTILEEXC | Returns the k-th percentile of values in a range, excluding the endpoints. | PERCENTILEEXC([1,2,3,4], 0.3) | 1.5 |
| PERCENTILEINC | Returns the k-th percentile of values in a range, including the endpoints. | PERCENTILEINC([1,2,3,4], 0.3) | 1.9 |
| PERCENTRANKEXC | Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set. | PERCENTRANKEXC([1,2,3,4], 2, 2) | 0.4 |
| PERCENTRANKINC | Returns the rank of a value in a data set as a percentage (0..1, inclusive) of the data set. | PERCENTRANKINC([1,2,3,4], 2, 2) | 0.33 |
| PERMUT | Returns the number of permutations for a given number of objects that can be selected from the total objects. | PERMUT(100, 3) | 970200 |
| PERMUTATIONA | Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects. | PERMUTATIONA(4, 3) | 64 |
| PHI | Returns the value of the density function for a standard normal distribution. | PHI(0.75) | 0.30113743215480443 |
| POISSONDIST | Returns the Poisson distribution. | POISSONDIST(2, 5, true) | 0.12465201948308113 |
| PROB | Returns the probability that values in a range are between two limits. | PROB([1,2,3,4], [0.1,0.2,0.2,0.1], 2, 3) | 0.4 |
| QUARTILEEXC | Returns the quartile of a data set, based on percentile values (exclusive). | QUARTILEEXC([1,2,3,4], 1) | 1.25 |
| QUARTILEINC | Returns the quartile of a data set, based on percentile values (inclusive). | QUARTILEINC([1,2,3,4], 1) | 1.75 |
| RANKAVG | Returns the rank of a number in a list of numbers, the rank is the average of ranks for duplicate numbers. | RANKAVG(4, [2,4,4,8,8,16], false) | 4.5 |
| RANKEQ | Returns the rank of a number in a list of numbers. | RANKEQ(4, [2,4,4,8,8,16], false) | 4 |
| RSQ | Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's. | RSQ([9,7,5,3,1], [10,6,1,5,3]) | 0.4891304347826088 |
| SKEW | Returns the skewness of a distribution. | SKEW([3,4,5,2,3,4,5,6,4,7]) | 0.3595430714067974 |
| SKEWP | Returns the skewness of a distribution based on a population. | SKEWP([3,4,5,2,3,4,5,6,4,7]) | 0.303193339354144 |
| SLOPE | Returns the slope of the linear regression line. | SLOPE([1,9,5,7], [0,4,2,3]) | 2 |
| SMALL | Returns the k-th smallest value in a data set. | SMALL([3,5,3,5,4,4,2,4,6,7], 3) | 3 |
| STANDARDIZE | Returns a normalized value. | STANDARDIZE(42, 40, 1.5) | 1.3333333333333333 |
| STDEVA | Calculates standard deviation based on a sample, including text and logical values. | STDEVA([2,4], [8,16], [true, false]) | 6.013872850889572 |
| STDEVP | Calculates standard deviation based on an entire population. | STDEVP([2,4], [8,16], [true, false]) | 5.361902647381804 |
| STDEVPA | Calculates standard deviation based on an entire population, including text and logical values. | STDEVPA([2,4], [8,16], [true, false]) | 5.489889697333535 |
| STDEVS | Calculates standard deviation based on a sample. | STDEVS([2,4], [8,16], [true, false]) | 6.191391873668904 |
| STEYX | Returns the standard error of the predicted y-value for each x in the regression. | STEYX([2,3,9,1,8,7,5], [6,5,11,7,5,4,4]) | 3.305718950210041 |
| TDIST | Returns the Student's t-distribution. | TDIST(60, 1, true) | 0.9946953263673741 |
| TINV | Returns the inverse of the Student's t-distribution. | TINV(0.9946953263673741, 1) | 59.99999999996535 |
| TRIMMEAN | Returns the mean of the interior of a data set. | TRIMMEAN([4,5,6,7,2,3,4,5,1,2,3], 0.2) | 3.7777777777777777 |
| VARA | Calculates variance based on a sample, including text and logical values. | VARA([2,4], [8,16], [true, false]) | 36.16666666666667 |
| VARP | Calculates variance based on an entire population. | VARP([2,4], [8,16], [true, false]) | 28.75 |
| VARPA | Calculates variance based on an entire population, including text and logical values. | VARPA([2,4], [8,16], [true, false]) | 30.13888888888889 |
| VARS | Calculates variance based on a sample. | VARS([2,4], [8,16], [true, false]) | 38.333333333333336 |
| WEIBULLDIST | Returns the Weibull distribution. | WEIBULLDIST(105, 20, 100, true) | 0.9295813900692769 |
Perform text operations such as concatenation, or extracting a substring.
| Formulas | Description | Example | Expected return |
|---|---|---|---|
| CHAR | Returns the character specified by a number. | CHAR(65) | A |
| CLEAN | Removes all non-printable characters from text. | CLEAN('Monthly report') | Monthly report |
| CODE | Returns a numeric code for the first character in a text string. | CODE('A') | 65 |
| CONCATENATE | Joins several text items into one text item. | CONCATENATE('Andreas', ' ', 'Hauser') | Andreas Hauser |
| EXACT | Checks whether two text strings are exactly the same, including case. | EXACT('Word', 'word') | false |
| FIND | Finds one text value within another (case-sensitive). | FIND('M', 'Miriam McGovern', 3) | 8 |
| LEFT | Returns the leftmost characters from a text value. | LEFT('Sale Price', 4) | Sale |
| LEN | Returns the number of characters in a text string. | LEN('Phoenix, AZ') | 11 |
| LOWER | Converts text to lowercase. | LOWER('E. E. Cummings') | e. e. cummings |
| MID | Returns a specific number of characters from a text string starting at the position you specify. | MID('Fluid Flow', 7, 20) | Flow |
| PROPER | Capitalizes the first letter in each word of a text value. | PROPER('this is a TITLE') | This Is A Title |
| REGEXEXTRACT | Extracts text that matches a regular expression. | REGEXEXTRACT('Palo Alto', 'Alto') | Alto |
| REGEXMATCH | Checks whether a text matches a regular expression. | REGEXMATCH('Palo Alto', 'Alto') | true |
| REGEXREPLACE | Replaces part of a text string that matches a regular expression. | REGEXREPLACE('Sutoiku', 'utoiku', 'TOIC') | STOIC |
| REPLACE | Replaces part of a text string with a different text string. | REPLACE('abcdefghijk', 6, 5, '*') | abcde*k |
| REPT | Repeats text a given number of times. | REPT('*-', 3) | *-*-*- |
| RIGHT | Returns the rightmost characters from a text value. | RIGHT('Sale Price', 5) | Price |
| ROMAN | Converts an Arabic numeral to Roman, as text. | ROMAN(499) | CDXCIX |
| SEARCH | Finds one text value within another (not case-sensitive). | SEARCH('margin', 'Profit Margin') | 8 |
| SPLIT | Splits text around a specified delimiter. | SPLIT('A,B,C', ',') | A,B,C |
| SUBSTITUTE | Substitutes new text for old text in a text string. | SUBSTITUTE('Quarter 1, 2011', '1', '2', 3) | Quarter 1, 2012 |
| T | Returns the text referred to by value. | T('Rainfall') | Rainfall |
| TRIM | Removes extra spaces from text. | TRIM(' First Quarter Earnings ') | First Quarter Earnings |
| UNICHAR | Returns the Unicode character that is referenced by the given numeric value. | UNICHAR(66) | B |
| UNICODE | Returns the number corresponding to the first character of the text. | UNICODE('B') | 66 |
| UPPER | Converts text to uppercase. | UPPER('total') | TOTAL |