Date
Function | Definition | Example call | Parameters | Expected result |
DATE | Create a date by given year, month, day. | DATE(2008, 7, 8) | year (integer), month (1-12), date (1-31) | Tue Jul 08 2008 00:00:00 GMT-0700 (PDT) |
DATEVALUE | Converts a date in text format to a serial number. | DATEVALUE('8/22/2011') | text string representing a date | Mon Aug 22 2011 00:00:00 GMT-0700 (PDT) |
DAY | Returns the day of the specified date. | DAY('15-Apr-11') | date value or date text string | 15 |
DAYS | Calculates the number of days between two dates. | DAYS('3/15/11', '2/1/11') | end date, start date | 42 |
DAYS360 | Calculates the number of days between two dates based on a 360-day year. | DAYS360('1-Jan-11', '31-Dec-11') | start date, end date | 360 |
EDATE | Returns the date that is the indicated number of months before or after the start date. | EDATE('1/15/11', -1) | start date, months (positive for future, negative for past) | Wed Dec 15 2010 00:00:00 GMT-0800 (PST) |
EOMONTH | Returns the last day of the month that is the indicated number of months before or after the start date. | EOMONTH('1/1/11', -3) | start date, months (positive for future, negative for past) | Sun Oct 31 2010 00:00:00 GMT-0700 (PDT) |
HOUR | Returns the hour part of a time value. | HOUR('7/18/2011 7:45:00 AM') | time value or time text string | 7 |
MINUTE | Returns the minute part of a time value. | MINUTE('2/1/2011 12:45:00 PM') | time value or time text string | 45 |
ISOWEEKNUM | Returns the ISO week number of the year for a given date. | ISOWEEKNUM('3/9/2012') | date value or date text string | 10 |
MONTH | Returns the month part of a date value. | MONTH('15-Apr-11') | date value or date text string | 4 |
NETWORKDAYS | Calculates the total number of working days between two dates, excluding weekends and specified holidays. | NETWORKDAYS('10/1/2012', '3/1/2013', ['11/22/2012']) | start date, end date, optional holidays array | 109 |
NETWORKDAYSINTL | Calculates the total number of working days between two dates, allowing for custom weekends, and excluding specified holidays. | NETWORKDAYSINTL('1/1/2006', '2/1/2006', 7, ['1/2/2006']) | start date, end date, weekend pattern, optional holidays array | 23 |
NOW | Returns the current date and time. | NOW() | no parameters | Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time) |
SECOND | Returns the second part of a time value. | SECOND('2/1/2011 4:48:18 PM') | time value or time text string | 18 |
TIME | Creates a time from the given hour, minute, and second. | TIME(16, 48, 10) | hour (0-23), minute (0-59), second (0-59) | 0.700115741 |
TIMEVALUE | Converts a time in text format to a time serial number. | TIMEVALUE('22-Aug-2011 6:35 AM') | text string representing a time | 0.274305556 |
TODAY | Returns the current date. | TODAY() | no parameters | Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time) |
WEEKDAY | Returns the day of the week as a number. | WEEKDAY('2/14/2008', 3) | date value or date text string, return type (1-3) | 3 |
YEAR | Returns the year part of a date value. | YEAR('7/5/2008') | date value or date text string | 2008 |
WEEKNUM | Returns the week number of the year for a given date. | WEEKNUM('3/9/2012', 2) | date value or date text string, optional week start day (1=Sunday, 2=Monday) | 11 |
WORKDAY | Returns the date that is the indicated number of workdays before or after the start date, excluding weekends and specified holidays. | WORKDAY('10/1/2008', 151, ['11/26/2008', '12/4/2008']) | start date, workdays, optional holidays array | Mon May 04 2009 00:00:00 GMT-0700 (PDT) |
WORKDAYINTL | Returns the date that is the indicated number of workdays before or after the start date, allowing for custom weekends, and excluding specified holidays. | WORKDAYINTL('1/1/2012', 30, 17) | start date, workdays, weekend pattern | Sun Feb 05 2012 00:00:00 GMT-0800 (PST) |
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) | start date, end date, optional basis (day count basis) | 0.578082192 |
Financial
Function | Definition | Example call | Parameters | Expected result |
ACCRINT | Calculate 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) | start_date, first_interest, settlement, rate, par, frequency, basis | 350 |
CUMIPMT | Calculate the cumulative interest paid on a loan between start and end periods. | CUMIPMT(0.1/12, 30*12, 100000, 13, 24, 0) | rate, nper, pv, start_period, end_period, type | -9916.772514 |
CUMPRINC | Calculate the cumulative principal paid on a loan between start and end periods. | CUMPRINC(0.1/12, 30*12, 100000, 13, 24, 0) | rate, nper, pv, start_period, end_period, type | -614.0863271 |
DB | Calculate the depreciation of an asset for a specified period using the fixed-declining balance method. | DB(1000000, 100000, 6, 1, 6) | cost, salvage, life, period, month | 159500 |
DDB | Calculate the depreciation of an asset for a specified period using the double-declining balance method or another specified method. | DDB(1000000, 100000, 6, 1, 1.5) | cost, salvage, life, period, factor | 250000 |
DOLLARDE | Converts a dollar price expressed as a fraction into a decimal number. | DOLLARDE(1.1, 16) | fractional_dollar, fraction | 1.625 |
DOLLARFR | Converts a dollar price expressed as a decimal number into a fractional dollar. | DOLLARFR(1.625, 16) | decimal_dollar, fraction | 1.1 |
EFFECT | Calculate the effective annual interest rate. | EFFECT(0.1, 4) | nominal_rate, npery | 0.103812891 |
FV | Calculate the future value of an investment. | FV(0.1/12, 10, -100, -1000, 0) | rate, nper, pmt, pv, type | 2124.874409 |
FVSCHEDULE | Calculate the future value of an initial principal after applying a series of compound interest rates. | FVSCHEDULE(100, [0.09,0.1,0.11]) | principal, schedule | 133.089 |
IPMT | Calculate 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) | rate, per, nper, pv, fv, type | 928.8235718 |
IRR | Calculate the internal rate of return for a series of cash flows. | IRR([-75000,12000,15000,18000,21000,24000], 0.075) | values, guess | 0.057151429 |
ISPMT | Calculate the interest paid during a specific period of a loan. | ISPMT(0.1/12, 6, 2*12, 100000) | rate, per, nper, pv | -625 |
MIRR | Calculate the modified internal rate of return for a series of periodic cash flows. | MIRR([-75000,12000,15000,18000,21000,24000], 0.1, 0.12) | values, finance_rate, reinvest_rate | 0.079717104 |
NOMINAL | Calculate the nominal annual interest rate. | NOMINAL(0.1, 4) | effect_rate, npery | 0.096454756 |
NPER | Calculate 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) | rate, pmt, pv, fv, type | 63.39385423 |
NPV | Calculate the net present value of an investment based on a series of future cash flows and a discount rate for each period. | NPV(0.1, -10000, 2000, 4000, 8000) | rate, value1, value2, ... | 1031.350318 |
PDURATION | Calculate the number of periods required for an investment to reach a specified value. | PDURATION(0.1, 1000, 2000) | rate, pv, fv | 7.272540897 |
PMT | Calculate the payment for a loan based on constant payments and a constant interest rate. | PMT(0.1/12, 2*12, 1000, 10000, 0) | rate, nper, pv, fv, type | -42426.08564 |
PPMT | Calculate the principal payment 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) | rate, per, nper, pv, fv, type | -43354.90921 |
PV | Calculate the present value of an investment. | PV(0.1/12, 2*12, 1000, 10000, 0) | rate, nper, pmt, fv, type | -29864.95026 |
RATE | Calculate the interest rate per period of an annuity. | RATE(2*12, -1000, -10000, 100000, 0, 0.1) | nper, pmt, pv, fv, type, guess | 0.065178912 |
Engineering
Function | Definition | Example call | Parameters | Expected result |
BIN2DEC | Convert a binary number to decimal. | BIN2DEC(101010) | binary_number | 42 |
BIN2HEX | Convert a binary number to hexadecimal. | BIN2HEX(101010) | binary_number | 2a |
BIN2OCT | Convert a binary number to octal. | BIN2OCT(101010) | binary_number | 52 |
BITAND | Perform a bitwise AND operation on two numbers. | BITAND(42, 24) | integer, integer | 8 |
BITLSHIFT | Shift a number left by the specified amount of bits. | BITLSHIFT(42, 24) | integer, shift_amount | 704643072 |
BITOR | Perform a bitwise OR operation on two numbers. | BITOR(42, 24) | integer, integer | 58 |
BITRSHIFT | Shift a number right by the specified amount of bits. | BITRSHIFT(42, 2) | integer, shift_amount | 10 |
BITXOR | Perform a bitwise XOR operation on two numbers. | BITXOR(42, 24) | integer, integer | 50 |
COMPLEX | Create a complex number. | COMPLEX(3, 4) | real_part, imaginary_part | 3+4i |
CONVERT | Convert a value from one measurement system to another. | CONVERT(64, 'kibyte', 'bit') | value, from_unit, to_unit | 524288 |
DEC2BIN | Convert a decimal number to binary. | DEC2BIN(42) | decimal_number | 101010 |
DEC2HEX | Convert a decimal number to hexadecimal. | DEC2HEX(42) | decimal_number | 2a |
DEC2OCT | Convert a decimal number to octal. | DEC2OCT(42) | decimal_number | 52 |
DELTA | Test whether two values are equal. | DELTA(42, 42) | value, value | 1 |
ERF | Calculate the error function. | ERF(1) | upper_limit | 0.842700793 |
ERFC | Calculate the complementary error function. | ERFC(1) | lower_limit | 0.157299207 |
GESTEP | Test if one number is greater than or equal to another. | GESTEP(42, 24) | value, threshold | 1 |
HEX2BIN | Convert a hexadecimal number to binary. | HEX2BIN('2a') | hexadecimal_number | 101010 |
HEX2DEC | Convert a hexadecimal number to decimal. | HEX2DEC('2a') | hexadecimal_number | 42 |
HEX2OCT | Convert a hexadecimal number to octal. | HEX2OCT('2a') | hexadecimal_number | 52 |
IMABS | Calculate the absolute value (modulus) of a complex number. | IMABS('3+4i') | complex_number | 5 |
IMAGINARY | Return the imaginary part of a complex number. | IMAGINARY('3+4i') | complex_number | 4 |
IMARGUMENT | Calculate the argument of a complex number. | IMARGUMENT('3+4i') | complex_number | 0.927295218 |
IMCONJUGATE | Calculate the conjugate of a complex number. | IMCONJUGATE('3+4i') | complex_number | 3-4i |
IMCOS | Calculate the cosine of a complex number. | IMCOS('1+i') | complex_number | 0.8337300251311491-0.9888977057628651i |
IMCOSH | Calculate the hyperbolic cosine of a complex number. | IMCOSH('1+i') | complex_number | 0.8337300251311491+0.9888977057628651i |
IMCOT | Calculate the cotangent of a complex number. | IMCOT('1+i') | complex_number | 0.21762156185440265-0.8680141428959249i |
IMCSC | Calculate the cosecant of a complex number. | IMCSC('1+i') | complex_number | 0.6215180171704283-0.3039310016284264i |
IMCSCH | Calculate the hyperbolic cosecant of a complex number. | IMCSCH('1+i') | complex_number | 0.3039310016284264-0.6215180171704283i |
IMDIV | Calculate the division of two complex numbers. | IMDIV('1+2i', '3+4i') | dividend, divisor | 0.44+0.08i |
IMEXP | Calculate the exponential of a complex number. | IMEXP('1+i') | complex_number | 1.4686939399158851+2.2873552871788423i |
IMLN | Calculate the natural logarithm of a complex number. | IMLN('1+i') | complex_number | 0.3465735902799727+0.7853981633974483i |
IMLOG10 | Calculate the base-10 logarithm of a complex number. | IMLOG10('1+i') | complex_number | 0.1505149978319906+0.3410940884604603i |
IMLOG2 | Calculate the base-2 logarithm of a complex number. | IMLOG2('1+i') | complex_number | 0.5000000000000001+1.1330900354567985i |
IMPOWER | Calculate the power of a complex number. | IMPOWER('1+i', 2) | complex_number, power | 1.2246063538223775e-16+2.0000000000000004i |
IMPRODUCT | Calculate the product of multiple complex numbers. | IMPRODUCT('1+2i', '3+4i', '5+6i') | array_of_complex_numbers | -85+20i |
IMREAL | Return the real part of a complex number. | IMREAL('3+4i') | complex_number | 3 |
IMSEC | Calculate the secant of a complex number. | IMSEC('1+i') | complex_number | 0.4983370305551868+0.591083841721045i |
IMSECH | Calculate the hyperbolic secant of a complex number. | IMSECH('1+i') | complex_number | 0.4983370305551868-0.591083841721045i |
IMSIN | Calculate the sine of a complex number. | IMSIN('1+i') | complex_number | 1.2984575814159773+0.6349639147847361i |
IMSINH | Calculate the hyperbolic sine of a complex number. | IMSINH('1+i') | complex_number | 0.6349639147847361+1.2984575814159773i |
IMSQRT | Calculate the square root of a complex number. | IMSQRT('1+i') | complex_number | 1.0986841134678098+0.45508986056222733i |
IMSUB | Calculate the subtraction of two complex numbers. | IMSUB('3+4i', '1+2i') | minuend, subtrahend | 2+2i |
IMSUM | Calculate the sum of multiple complex numbers. | IMSUM('1+2i', '3+4i', '5+6i') | array_of_complex_numbers | 9+12i |
IMTAN | Calculate the tangent of a complex number. | IMTAN('1+i') | complex_number | 0.2717525853195117+1.0839233273386946i |
OCT2BIN | Convert an octal number to binary. | OCT2BIN('52') | octal_number | 101010 |
OCT2DEC | Convert an octal number to decimal. | OCT2DEC('52') | octal_number | 42 |
OCT2HEX | Convert an octal number to hexadecimal. | OCT2HEX('52') | octal_number | 2a |
Logical
Function | Definition | Example call | Parameters | Expected result |
AND | Returns true if all arguments are true, otherwise returns false. | AND(true, false, true) | One or more logical values (boolean). The function returns true only if all arguments are true. |
|
FALSE | Returns the logical value false. | FALSE() | No parameters. |
|
IF | Returns one value if a condition is true and another value if it is false. | IF(true, 'Hello!', 'Goodbye!') | Condition, value if true, value if false. | Hello! |
IFS | Checks multiple conditions and returns the value of the first true condition. | IFS(false, 'Hello!', true, 'Goodbye!') | Multiple pairs of conditions and corresponding values, in pairs. | Goodbye! |
NOT | Reverses the logic of its argument, true becomes false, and false becomes true. | NOT(true) | A single logical value (boolean). |
|
OR | Returns true if any argument is true, otherwise returns false. | OR(true, false, true) | One or more logical values (boolean), returns true if at least one argument is true. |
|
SWITCH | Evaluates an expression and returns a matching result; if no match is found, returns a default value. | SWITCH(7, 9, 'Nine', 7, 'Seven') | Expression, match value 1, result value 1, ..., [default value]. | Seven |
TRUE | Returns the logical value true. | TRUE() | No parameters. |
|
XOR | Returns true if an odd number of arguments are true, otherwise returns false. | XOR(true, false, true) | One or more logical values (boolean), returns true if an odd number of arguments are true. |
|
Math
Function | Definition | Example call | Parameters | Expected result |
ABS | Returns the absolute value of a number. | ABS(-4) | Number. | 4 |
ACOS | Calculates the arccosine (in radians). | ACOS(-0.5) | A number between -1 and 1. | 2.094395102 |
ACOSH | Calculates the inverse hyperbolic cosine. | ACOSH(10) | A number greater than or equal to 1. | 2.993222846 |
ACOT | Calculates the arccotangent (in radians). | ACOT(2) | Any number. | 0.463647609 |
ACOTH | Calculates the inverse hyperbolic cotangent. | ACOTH(6) | A number with an absolute value greater than 1. | 0.168236118 |
AGGREGATE | Performs aggregate operations, ignoring errors or hidden rows. | AGGREGATE(9, 4, [-5,15], [32,'Hello World!']) | Function number, option, array1, ..., arrayN. | 10,32 |
ARABIC | Converts a Roman numeral to an Arabic numeral. | ARABIC('MCMXII') | Roman numeral string. | 1912 |
ASIN | Calculates the arcsine (in radians). | ASIN(-0.5) | A number between -1 and 1. | -0.523598776 |
ASINH | Calculates the inverse hyperbolic sine. | ASINH(-2.5) | Any number. | -1.647231146 |
ATAN | Calculates the arctangent (in radians). | ATAN(1) | Any number. | 0.785398163 |
ATAN2 | Calculates the arctangent (in radians) from coordinates. | ATAN2(-1, -1) | Y coordinate, X coordinate. | -2.35619449 |
ATANH | Calculates the inverse hyperbolic tangent. | ATANH(-0.1) | A number between -1 and 1. | -0.100335348 |
BASE | Converts a number into a text representation at a given base. | BASE(15, 2, 10) | Number, base, [minimum length]. | 1111 |
CEILING | Rounds a number up to the nearest multiple. | CEILING(-5.5, 2, -1) | Number, multiple, [mode]. | -6 |
CEILINGMATH | Rounds a number up using specified multiple and direction. | CEILINGMATH(-5.5, 2, -1) | Number, [multiple], [mode]. | -6 |
CEILINGPRECISE | Rounds a number up to the nearest multiple, regardless of sign. | CEILINGPRECISE(-4.1, -2) | Number, [multiple]. | -4 |
COMBIN | Calculates the number of combinations. | COMBIN(8, 2) | Total number, chosen number. | 28 |
COMBINA | Calculates the number of combinations with repetitions. | COMBINA(4, 3) | Total number, chosen number. | 20 |
COS | Calculates the cosine (in radians). | COS(1) | Angle (in radians). | 0.540302306 |
COSH | Calculates the hyperbolic cosine. | COSH(1) | Any number. | 1.543080635 |
COT | Calculates the cotangent (in radians). | COT(30) | Angle (in radians). | -0.156119952 |
COTH | Calculates the hyperbolic cotangent. | COTH(2) | Any number. | 1.037314721 |
CSC | Calculates the cosecant (in radians). | CSC(15) | Angle (in radians). | 1.537780562 |
CSCH | Calculates the hyperbolic cosecant. | CSCH(1.5) | Any number. | 0.469642441 |
DECIMAL | Converts a text representation of a number to decimal. | DECIMAL('FF', 16) | Text, base. | 255 |
ERF | Calculates the error function. | ERF(1) | Upper limit. | 0.842700793 |
ERFC | Calculates the complementary error function. | ERFC(1) | Lower limit. | 0.157299207 |
EVEN | Rounds a number up to the nearest even integer. | EVEN(-1) | Number. | -2 |
EXP | Calculates e raised to the power of a given number. | EXP(1) | Exponent. | 2.718281828 |
FACT | Calculates the factorial. | FACT(5) | Non-negative integer. | 120 |
FACTDOUBLE | Calculates the double factorial. | FACTDOUBLE(7) | Non-negative integer. | 105 |
FLOOR | Rounds a number down to the nearest multiple. | FLOOR(-3.1) | Number, multiple. | -4 |
FLOORMATH | Rounds a number down using specified multiple and direction. | FLOORMATH(-4.1, -2, -1) | Number, [multiple], [mode]. | -4 |
FLOORPRECISE | Rounds a number down to the nearest multiple, regardless of sign. | FLOORPRECISE(-3.1, -2) | Number, [multiple]. | -4 |
GCD | Calculates the greatest common divisor. | GCD(24, 36, 48) | Two or more integers. | 12 |
INT | Rounds a number down to the nearest integer. | INT(-8.9) | Number. | -9 |
ISEVEN | Tests whether a number is even. | ISEVEN(-2.5) | Number. |
|
ISOCEILING | Rounds a number up to the nearest multiple, following ISO standards. | ISOCEILING(-4.1, -2) | Number, [multiple]. | -4 |
ISODD | Tests whether a number is odd. | ISODD(-2.5) | Number. |
|
LCM | Calculates the least common multiple. | LCM(24, 36, 48) | Two or more integers. | 144 |
LN | Calculates the natural logarithm. | LN(86) | Positive value. | 4.454347296 |
LOG | Calculates the logarithm for a specified base. | LOG(8, 2) | Number, base. | 3 |
LOG10 | Calculates the logarithm with base 10. | LOG10(100000) | Positive value. | 5 |
MOD | Calculates the remainder of two numbers divided. | MOD(3, -2) | Dividend, divisor. | -1 |
MROUND | Rounds a number to the nearest multiple. | MROUND(-10, -3) | Number, multiple. | -9 |
MULTINOMIAL | Calculates the multinomial coefficient. | MULTINOMIAL(2, 3, 4) | Two or more non-negative integers. | 1260 |
ODD | Rounds a number up to the nearest odd integer. | ODD(-1.5) | Number. | -3 |
POWER | Calculates a number raised to a power. | POWER(5, 2) | Base, exponent. | 25 |
PRODUCT | Calculates the product of multiple numbers. | PRODUCT(5, 15, 30) | One or more numbers. | 2250 |
QUOTIENT | Calculates the integer portion of a division, ignoring the remainder. | QUOTIENT(-10, 3) | Dividend, divisor. | -3 |
RADIANS | Converts degrees to radians. | RADIANS(180) | Angle in degrees. | 3.141592654 |
RAND | Generates a random real number between 0 and 1. | RAND() | No parameters. | [Random real number between 0 and 1] |
RANDBETWEEN | Generates a random integer within a specified range. | RANDBETWEEN(-1, 1) | Bottom, top. | [Random integer between bottom and top] |
ROUND | Rounds a number to a specified number of digits. | ROUND(626.3, -3) | Number, num_digits. | 1000 |
ROUNDDOWN | Rounds a number down to a specified number of digits. | ROUNDDOWN(-3.14159, 2) | Number, num_digits. | -3.14 |
ROUNDUP | Rounds a number up to a specified number of digits. | ROUNDUP(-3.14159, 2) | Number, num_digits. | -3.15 |
SEC | Calculates the secant (in radians). | SEC(45) | Angle in radians. | 1.903594407 |
SECH | Calculates the hyperbolic secant. | SECH(45) | Any number. | 5.72504E-20 |
SIGN | Returns the sign of a number. | SIGN(-0.00001) | Number. | -1 |
SIN | Calculates the sine (in radians). | SIN(1) | Angle in radians. | 0.841470985 |
SINH | Calculates the hyperbolic sine. | SINH(1) | Any number. | 1.175201194 |
SQRT | Calculates the square root. | SQRT(16) | Non-negative number. | 4 |
SQRTPI | Calculates the square root of a number multiplied by pi. | SQRTPI(2) | Non-negative number. | 2.506628275 |
SUBTOTAL | Calculates a subtotal in a list or database, ignoring hidden rows. | SUBTOTAL(9, [-5,15], [32,'Hello World!']) | Function_num, array1, ..., arrayN. | 10,32 |
SUM | Calculates the sum of numbers, ignoring text. | SUM(-5, 15, 32, 'Hello World!') | One or more numbers. | 42 |
SUMIF | Sums values based on a condition. | SUMIF([2,4,8,16], '>5') | Array, criteria. | 24 |
SUMIFS | Sums values based on multiple conditions. | SUMIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4') | Sum_array, criteria_range1, criteria1, ..., criteria_rangeN, criteriaN. | 12 |
SUMPRODUCT | Calculates the sum of the products of corresponding elements in arrays. | SUMPRODUCT([[1,2],[3,4]], [[1,0],[0,1]]) | Two or more arrays. | 5 |
SUMSQ | Calculates the sum of squares of numbers. | SUMSQ(3, 4) | One or more numbers. | 25 |
SUMX2MY2 | Calculates the sum of the difference of squares of corresponding elements in two arrays. | SUMX2MY2([1,2], [3,4]) | Array1, array2. | -20 |
SUMX2PY2 | Calculates the sum of the sum of squares of corresponding elements in two arrays. | SUMX2PY2([1,2], [3,4]) | Array1, array2. | 30 |
SUMXMY2 | Calculates the sum of squares of differences of corresponding elements in two arrays. | SUMXMY2([1,2], [3,4]) | Array1, array2. | 8 |
TAN | Calculates the tangent (in radians). | TAN(1) | Angle in radians. | 1.557407725 |
TANH | Calculates the hyperbolic tangent. | TANH(-2) | Any number. | -0.96402758 |
TRUNC | Truncates a number without rounding. | TRUNC(-8.9) | Number, [num_digits]. | -8 |
Statistical
Function | Definition | Example call | Parameters | Expected result |
AVEDEV | Calculates the average of the absolute deviations. | AVEDEV([2,4], [8,16]) | Arguments are arrays of numbers representing data points. | 4.5 |
AVERAGE | Calculates the arithmetic mean. | AVERAGE([2,4], [8,16]) | Arguments are arrays of numbers representing data points to average. | 7.5 |
AVERAGEA | Calculates the average including text and logical values. | AVERAGEA([2,4], [8,16]) | Arguments are arrays of numbers, text, or logical values; all non-empty values are calculated. | 7.5 |
AVERAGEIF | Calculates the average based on a single condition. | AVERAGEIF([2,4,8,16], '>5', [1, 2, 3, 4]) | First argument is an array of numbers, second is a condition, third optional array for averaging. | 3.5 |
AVERAGEIFS | Calculates the average based on multiple conditions. | AVERAGEIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4') | First argument is an array of numbers, followed by pairs of condition arrays and condition expressions. | 6 |
BETADIST | Calculates the cumulative beta probability density function. | BETADIST(2, 8, 10, true, 1, 3) | Parameters are value, α, β, cumulative flag, A (optional), and B (optional). | 0.685470581 |
BETAINV | Calculates the inverse of the cumulative beta probability density function. | BETAINV(0.6854705810117458, 8, 10, 1, 3) | Parameters are probability, α, β, A (optional), and B (optional). | 2 |
BINOMDIST | Calculates the binomial distribution probability. | BINOMDIST(6, 10, 0.5, false) | Parameters are trials, successes, success probability, and cumulative flag. | 0.205078125 |
CORREL | Calculates the correlation coefficient between two datasets. | CORREL([3,2,4,5,6], [9,7,12,15,17]) | Arguments are two arrays of numbers representing two datasets. | 0.997054486 |
COUNT | Counts the number of numeric cells. | COUNT([1,2], [3,4]) | Arguments are arrays or ranges of numbers. | 4 |
COUNTA | Counts the number of non-empty cells. | COUNTA([1, null, 3, 'a', '', 'c']) | Arguments are arrays or ranges of any type. | 4 |
COUNTBLANK | Counts the number of blank cells. | COUNTBLANK([1, null, 3, 'a', '', 'c']) | Arguments are arrays or ranges of any type. | 2 |
COUNTIF | Counts the number of cells based on a condition. | COUNTIF(['Caen', 'Melbourne', 'Palo Alto', 'Singapore'], 'a') | Arguments are an array of numbers or text and a condition. | 3 |
COUNTIFS | Counts the number of cells based on multiple conditions. | COUNTIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4') | Arguments are pairs of condition arrays and condition expressions. | 2 |
COVARIANCEP | Calculates the population covariance. | COVARIANCEP([3,2,4,5,6], [9,7,12,15,17]) | Arguments are two arrays of numbers representing two datasets. | 5.2 |
COVARIANCES | Calculates the sample covariance. | COVARIANCES([2,4,8], [5,11,12]) | Arguments are two arrays of numbers representing two datasets. | 9.666666667 |
DEVSQ | Calculates the sum of squares of deviations. | DEVSQ([2,4,8,16]) | Arguments are arrays of numbers representing data points. | 115 |
EXPONDIST | Calculates the exponential distribution. | EXPONDIST(0.2, 10, true) | Parameters are value, λ, and cumulative flag. | 0.864664717 |
FDIST | Calculates the F probability distribution. | FDIST(15.2069, 6, 4, false) | Parameters are value, degrees of freedom 1, degrees of freedom 2, and cumulative flag. | 0.001223792 |
FINV | Calculates the inverse of the F probability distribution. | FINV(0.01, 6, 4) | Parameters are probability, degrees of freedom 1, and degrees of freedom 2. | 0.109309914 |
FISHER | Calculates the Fisher transformation. | FISHER(0.75) | Parameter is a number representing the correlation coefficient. | 0.972955075 |
FISHERINV | Calculates the inverse of the Fisher transformation. | FISHERINV(0.9729550745276566) | Parameter is a number representing the result of the Fisher transformation. | 0.75 |
FORECAST | Predicts a y-value for a new x-value using known x and y values. | FORECAST(30, [6,7,9,15,21], [20,28,31,38,40]) | new x-value, array of known y-values, array of known x-values | 10.60725309 |
FREQUENCY | Calculates the frequency distribution. | FREQUENCY([79,85,78,85,50,81,95,88,97], [70,79,89]) | array of data, array of bin boundaries | 1,2,4,2 |
GAMMA | Calculates the gamma function value. | GAMMA(2.5) | a positive number | 1.329340392 |
GAMMALN | Calculates the natural logarithm of the gamma function. | GAMMALN(10) | a positive number | 12.80182748 |
GAUSS | Calculates the probability in the standard normal distribution. | GAUSS(2) | a number representing the z-score | 0.477249868 |
GEOMEAN | Calculates the geometric mean. | GEOMEAN([2,4], [8,16]) | array of numbers representing data points | 5.656854249 |
GROWTH | Predicts exponential growth based on known data. | GROWTH([2,4,8,16], [1,2,3,4], [5]) | array of known y-values, array of known x-values, array of new x-values | 32 |
HARMEAN | Calculates the harmonic mean. | HARMEAN([2,4], [8,16]) | array of numbers representing data points | 4.266666667 |
HYPGEOMDIST | Calculates the hypergeometric distribution. | HYPGEOMDIST(1, 4, 8, 20, false) | number of successes in sample, sample size, number of successes in population, population size, cumulative flag | 0.363261094 |
INTERCEPT | Calculates the intercept of the linear regression. | INTERCEPT([2,3,9,1,8], [6,5,11,7,5]) | array of known y-values, array of known x-values | 0.048387097 |
KURT | Calculates the kurtosis. | KURT([3,4,5,2,3,4,5,6,4,7]) | array of numbers representing data points | -0.151799637 |
LARGE | Returns the k-th largest value. | LARGE([3,5,3,5,4,4,2,4,6,7], 3) | array of numbers, k value | 5 |
LINEST | Performs linear regression analysis. | LINEST([1,9,5,7], [0,4,2,3], true, true) | array of known y-values, array of known x-values, whether to return additional statistics, whether to return more statistics | 2,1 |
LOGNORMDIST | Calculates the lognormal distribution. | LOGNORMDIST(4, 3.5, 1.2, true) | value, mean, standard deviation, cumulative flag | 0.039083556 |
LOGNORMINV | Calculates the inverse of the lognormal distribution. | LOGNORMINV(0.0390835557068005, 3.5, 1.2, true) | probability, mean, standard deviation, cumulative flag | 4 |
MAX | Returns the maximum value. | MAX([0.1,0.2], [0.4,0.8], [true, false]) | array of numbers | 0.8 |
MAXA | Returns the maximum value including text and logical values. | MAXA([0.1,0.2], [0.4,0.8], [true, false]) | array of numbers, text, or logical values | 1 |
MEDIAN | Returns the median. | MEDIAN([1,2,3], [4,5,6]) | array of numbers | 3.5 |
MIN | Returns the minimum value. | MIN([0.1,0.2], [0.4,0.8], [true, false]) | array of numbers | 0.1 |
MINA | Returns the minimum value including text and logical values. | MINA([0.1,0.2], [0.4,0.8], [true, false]) | array of numbers, text, or logical values | 0 |
MODEMULT | Returns an array of the most frequent values. | MODEMULT([1,2,3,4,3,2,1,2,3]) | array of numbers | 2,3 |
MODESNGL | Returns the single most frequent value. | MODESNGL([1,2,3,4,3,2,1,2,3]) | array of numbers | 2 |
NORMDIST | Calculates the normal distribution. | NORMDIST(42, 40, 1.5, true) | value, mean, standard deviation, cumulative flag | 0.90878878 |
NORMINV | Calculates the inverse of the normal distribution. | NORMINV(0.9087887802741321, 40, 1.5) | probability, mean, standard deviation | 42 |
NORMSDIST | Calculates the standard normal distribution. | NORMSDIST(1, true) | a number representing the z-score | 0.841344746 |
NORMSINV | Calculates the inverse of the standard normal distribution. | NORMSINV(0.8413447460685429) | a probability value | 1 |
PEARSON | Calculates the Pearson product-moment correlation coefficient. | PEARSON([9,7,5,3,1], [10,6,1,5,3]) | two arrays of numbers representing two datasets | 0.699378606 |
PERCENTILEEXC | Calculates the percentile exclusive. | PERCENTILEEXC([1,2,3,4], 0.3) | array of numbers, k value | 1.5 |
PERCENTILEINC | Calculates the percentile inclusive. | PERCENTILEINC([1,2,3,4], 0.3) | array of numbers, k value | 1.9 |
PERCENTRANKEXC | Calculates the percentage rank exclusive. | PERCENTRANKEXC([1,2,3,4], 2, 2) | array of numbers, x value, significance (optional) | 0.4 |
PERCENTRANKINC | Calculates the percentage rank inclusive. | PERCENTRANKINC([1,2,3,4], 2, 2) | array of numbers, x value, significance (optional) | 0.33 |
PERMUT | Calculates the number of permutations. | PERMUT(100, 3) | total n, chosen k | 970200 |
PERMUTATIONA | Calculates the number of permutations with repetition. | PERMUTATIONA(4, 3) | total n, chosen k | 64 |
PHI | Calculates the density function for the standard normal distribution. | PHI(0.75) | a number representing the z-score | 0.301137432 |
POISSONDIST | Calculates the Poisson distribution. | POISSONDIST(2, 5, true) | number of events, mean, cumulative flag | 0.124652019 |
PROB | Calculates the sum of probabilities. | PROB([1,2,3,4], [0.1,0.2,0.2,0.1], 2, 3) | array of numbers, array of probabilities, lower limit, upper limit | 0.4 |
QUARTILEEXC | Calculates the quartile exclusive. | QUARTILEEXC([1,2,3,4], 1) | array of numbers, quart value | 1.25 |
QUARTILEINC | Calculates the quartile inclusive. | QUARTILEINC([1,2,3,4], 1) | array of numbers, quart value | 1.75 |
RANKAVG | Calculates the average rank. | RANKAVG(4, [2,4,4,8,8,16], false) | value, array of numbers, order (ascending/descending) | 4.5 |
RANKEQ | Calculates the rank equal to a specified value. | RANKEQ(4, [2,4,4,8,8,16], false) | value, array of numbers, order (ascending/descending) | 4 |
RSQ | Calculates the coefficient of determination. | RSQ([9,7,5,3,1], [10,6,1,5,3]) | two arrays of numbers representing two datasets | 0.489130435 |
SKEW | Calculates the skewness. | SKEW([3,4,5,2,3,4,5,6,4,7]) | array of numbers representing data points | 0.359543071 |
SKEWP | Calculates the skewness based on a population. | SKEWP([3,4,5,2,3,4,5,6,4,7]) | array of numbers representing data points | 0.303193339 |
SLOPE | Calculates the slope of the linear regression. | SLOPE([1,9,5,7], [0,4,2,3]) | array of known y-values, array of known x-values | 2 |
SMALL | Returns the k-th smallest value. | SMALL([3,5,3,5,4,4,2,4,6,7], 3) | array of numbers, k value | 3 |
STANDARDIZE | Standardizes a value to a z-score. | STANDARDIZE(42, 40, 1.5) | value, mean, standard deviation | 1.333333333 |
STDEVA | Calculates the standard deviation including text and logical values. | STDEVA([2,4], [8,16], [true, false]) | array of numbers, text, or logical values | 6.013872851 |
STDEVP | Calculates the standard deviation of a population. | STDEVP([2,4], [8,16], [true, false]) | array of numbers | 5.361902647 |
STDEVPA | Calculates the standard deviation of a population including text and logical values. | STDEVPA([2,4], [8,16], [true, false]) | array of numbers, text, or logical values | 5.489889697 |
STDEVS | Calculates the sample standard deviation. | STDEVS([2,4], [8,16], [true, false]) | array of numbers | 6.191391874 |
STEYX | Calculates the standard error of the predicted value. | STEYX([2,3,9,1,8,7,5], [6,5,11,7,5,4,4]) | array of known y-values, array of known x-values | 3.30571895 |
TINV | Calculates the inverse of the t-distribution. | TINV(0.9946953263673741, 1) | probability, degrees of freedom | 60 |
TRIMMEAN | Calculates the mean of the interior of a data set. | TRIMMEAN([4,5,6,7,2,3,4,5,1,2,3], 0.2) | array of numbers, fraction to trim | 3.777777778 |
VARA | Calculates variance including text and logical values. | VARA([2,4], [8,16], [true, false]) | array of numbers, text, or logical values | 36.16666667 |
VARP | Calculates variance based on an entire population. | VARP([2,4], [8,16], [true, false]) | array of numbers | 28.75 |
VARPA | Calculates variance based on an entire population including text and logical values. | VARPA([2,4], [8,16], [true, false]) | array of numbers, text, or logical values | 30.13888889 |
VARS | Calculates sample variance. | VARS([2,4], [8,16], [true, false]) | array of numbers | 38.33333333 |
WEIBULLDIST | Calculates the Weibull distribution. | WEIBULLDIST(105, 20, 100, true) | value, alpha, beta, cumulative flag | 0.92958139 |
ZTEST | Calculates the one-tailed probability of a z-test. | ZTEST([3,6,7,8,6,5,4,2,1,9], 4) | array of numbers, hypothesized mean | 0.090574197 |
Text
Function | Definition | Example call | Parameters | Expected result |
CHAR | Converts a number code to its corresponding character. | CHAR(65) | number representing the character code | A |
CLEAN | Removes all non-printable characters from text. | CLEAN('Monthly report') | text string to clean | Monthly report |
CODE | Returns the numeric code of the first character in a text string. | CODE('A') | text string containing a single character | 65 |
CONCATENATE | Joins multiple text strings into one string. | CONCATENATE('Andreas', ' ', 'Hauser') | one or more text strings to concatenate | Andreas Hauser |
EXACT | Checks if two text strings are identical, case-sensitive. | EXACT('Word', 'word') | two text strings to compare |
|
FIND | Finds the position of a substring within text, starting at a specified position. | FIND('M', 'Miriam McGovern', 3) | text to find, source text, optional start position | 8 |
LEFT | Extracts a specified number of characters from the left side of a string. | LEFT('Sale Price', 4) | text string and number of characters to extract | Sale |
LEN | Returns the number of characters in a text string. | LEN('Phoenix, AZ') | text string to count | 11 |
LOWER | Converts all characters to lowercase. | LOWER('E. E. Cummings') | text string to convert | e. e. cummings |
MID | Extracts a specified number of characters from the middle of a string. | MID('Fluid Flow', 7, 20) | text string, start position, number of characters to extract | Flow |
NUMBERVALUE | Converts text to a number based on specified delimiters. | NUMBERVALUE('2.500,27', ',', '.') | text string, decimal separator, group separator | 2500.27 |
PROPER | Capitalizes the first letter of each word. | PROPER('this is a TITLE') | text string to format | This Is A Title |
REPLACE | Replaces part of old text with new text. | REPLACE('abcdefghijk', 6, 5, '*') | original text, start position, number of characters to replace, new text | abcde*k |
REPT | Repeats text a specified number of times. | REPT('*-', 3) | text string and number of repetitions | *-*-*- |
RIGHT | Extracts a specified number of characters from the right side of a string. | RIGHT('Sale Price', 5) | text string and number of characters to extract | Price |
ROMAN | Converts an Arabic numeral to Roman numerals. | ROMAN(499) | Arabic numeral to convert | CDXCIX |
SEARCH | Finds the position of a substring in text, not case-sensitive. | SEARCH('margin', 'Profit Margin') | text to find, source text | 8 |
SUBSTITUTE | Replaces specific instances of old text with new text. | SUBSTITUTE('Quarter 1, 2011', '1', '2', 3) | original text, old text, new text, optional instance to replace | Quarter 1, 2012 |
T | Returns the text if the argument is text; otherwise returns an empty string. | T('Rainfall') | any data type | Rainfall |
TRIM | Removes spaces before and after text, preserves internal multiple spaces as one. | TRIM(' First Quarter Earnings ') | text string to trim | First Quarter Earnings |
TEXTJOIN | Joins multiple text items into one string using a specified delimiter. | TEXTJOIN(' ', true, 'The', '', 'sun', 'will', 'come', 'up', 'tomorrow.') | delimiter, ignore empty flag, text items to join | The sun will come up tomorrow. |
UNICHAR | Returns the character corresponding to the given Unicode number. | UNICHAR(66) | Unicode character code | B |
UNICODE | Returns the Unicode number of the first character in a text string. | UNICODE('B') | text string containing a single character | 66 |
UPPER | Converts all characters to uppercase. | UPPER('total') | text string to convert | TOTAL |