Date Functions
| Title | Call | Result |
|---|---|---|
| DATE | DATE(2008, 7, 8) | Tue Jul 08 2008 00:00:00 GMT-0700 (PDT) |
| DATEVALUE | DATEVALUE('8/22/2011') | Mon Aug 22 2011 00:00:00 GMT-0700 (PDT) |
| DAY | DAY('15-Apr-11') | 15 |
| DAYS | DAYS('3/15/11', '2/1/11') | 42 |
| DAYS360 | DAYS360('1-Jan-11', '31-Dec-11') | 360 |
| EDATE | EDATE('1/15/11', -1) | Wed Dec 15 2010 00:00:00 GMT-0800 (PST) |
| EOMONTH | EOMONTH('1/1/11', -3) | Sun Oct 31 2010 00:00:00 GMT-0700 (PDT) |
| HOUR | HOUR('7/18/2011 7:45:00 AM') | 7 |
| MINUTE | MINUTE('2/1/2011 12:45:00 PM') | 45 |
| ISOWEEKNUM | ISOWEEKNUM('3/9/2012') | 10 |
| MONTH | MONTH('15-Apr-11') | 4 |
| NETWORKDAYS | NETWORKDAYS('10/1/2012', '3/1/2013', ['11/22/2012']) | 109 |
| NETWORKDAYSINTL | NETWORKDAYSINTL('1/1/2006', '2/1/2006', 7, ['1/2/2006']) | 23 |
| NOW | NOW() | Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time) |
| SECOND | SECOND('2/1/2011 4:48:18 PM') | 18 |
| TIME | TIME(16, 48, 10) | 0.7001157407407408 |
| TIMEVALUE | TIMEVALUE('22-Aug-2011 6:35 AM') | 0.2743055555555556 |
| TODAY | TODAY() | Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time) |
| WEEKDAY | WEEKDAY('2/14/2008', 3) | 3 |
| YEAR | YEAR('7/5/2008') | 2008 |
| WEEKNUM | WEEKNUM('3/9/2012', 2) | 11 |
| WORKDAY | WORKDAY('10/1/2008', 151, ['11/26/2008', '12/4/2008']) | Mon May 04 2009 00:00:00 GMT-0700 (PDT) |
| WORKDAYINTL | WORKDAYINTL('1/1/2012', 30, 17) | Sun Feb 05 2012 00:00:00 GMT-0800 (PST) |
| YEARFRAC | YEARFRAC('1/1/2012', '7/30/2012', 3) | 0.5780821917808219 |
Financial Functions
| Title | Call | Result |
|---|---|---|
| ACCRINT | ACCRINT('01/01/2011', '02/01/2011', '07/01/2014', 0.1, 1000, 1, 0) | 350 |
| CUMIPMT | CUMIPMT(0.1/12, 30*12, 100000, 13, 24, 0) | -9916.77251395708 |
| CUMPRINC | CUMPRINC(0.1/12, 30*12, 100000, 13, 24, 0) | -614.0863271085149 |
| DB | DB(1000000, 100000, 6, 1, 6) | 159500 |
| DDB | DDB(1000000, 100000, 6, 1, 1.5) | 250000 |
| DOLLARDE | DOLLARDE(1.1, 16) | 1.625 |
| DOLLARFR | DOLLARFR(1.625, 16) | 1.1 |
| EFFECT | EFFECT(0.1, 4) | 0.10381289062499977 |
| FV | FV(0.1/12, 10, -100, -1000, 0) | 2124.874409194097 |
| FVSCHEDULE | FVSCHEDULE(100, [0.09,0.1,0.11]) | 133.08900000000003 |
| IPMT | IPMT(0.1/12, 6, 2*12, 100000, 1000000, 0) | 928.8235718400465 |
| IRR | IRR([-75000,12000,15000,18000,21000,24000], 0.075) | 0.05715142887178447 |
| ISPMT | ISPMT(0.1/12, 6, 2*12, 100000) | -625 |
| MIRR | MIRR([-75000,12000,15000,18000,21000,24000], 0.1, 0.12) | 0.07971710360838036 |
| NOMINAL | NOMINAL(0.1, 4) | 0.09645475633778045 |
| NPER | NPER(0.1/12, -100, -1000, 10000, 0) | 63.39385422740764 |
| NPV | NPV(0.1, -10000, 2000, 4000, 8000) | 1031.3503176012546 |
| PDURATION | PDURATION(0.1, 1000, 2000) | 7.272540897341714 |
| PMT | PMT(0.1/12, 2*12, 100000, 1000000, 0) | -42426.08563793503 |
| PPMT | PPMT(0.1/12, 6, 2*12, 100000, 1000000, 0) | -43354.909209775076 |
| PV | PV(0.1/12, 2*12, 1000, 10000, 0) | -29864.950264779152 |
| RATE | RATE(2*12, -1000, -10000, 100000, 0, 0.1) | 0.06517891177181533 |
Engineering Functions
| Title | Call | Result |
|---|---|---|
| BIN2DEC | BIN2DEC(101010) | 42 |
| BIN2HEX | BIN2HEX(101010) | 2a |
| BIN2OCT | BIN2OCT(101010) | 52 |
| BITAND | BITAND(42, 24) | 8 |
| BITLSHIFT | BITLSHIFT(42, 24) | 704643072 |
| BITOR | BITOR(42, 24) | 58 |
| BITRSHIFT | BITRSHIFT(42, 2) | 10 |
| BITXOR | BITXOR(42, 24) | 50 |
| COMPLEX | COMPLEX(3, 4) | 3+4i |
| CONVERT | CONVERT(64, 'kibyte', 'bit') | 524288 |
| DEC2BIN | DEC2BIN(42) | 101010 |
| DEC2HEX | DEC2HEX(42) | 2a |
| DEC2OCT | DEC2OCT(42) | 52 |
| DELTA | DELTA(42, 42) | 1 |
| ERF | ERF(1) | 0.8427007929497149 |
| ERFC | ERFC(1) | 0.1572992070502851 |
| GESTEP | GESTEP(42, 24) | 1 |
| HEX2BIN | HEX2BIN('2a') | 101010 |
| HEX2DEC | HEX2DEC('2a') | 42 |
| HEX2OCT | HEX2OCT('2a') | 52 |
| IMABS | IMABS('3+4i') | 5 |
| IMAGINARY | IMAGINARY('3+4i') | 4 |
| IMARGUMENT | IMARGUMENT('3+4i') | 0.9272952180016122 |
| IMCONJUGATE | IMCONJUGATE('3+4i') | 3-4i |
| IMCOS | IMCOS('1+i') | 0.8337300251311491-0.9888977057628651i |
| IMCOSH | IMCOSH('1+i') | 0.8337300251311491+0.9888977057628651i |
| IMCOT | IMCOT('1+i') | 0.21762156185440265-0.8680141428959249i |
| IMCSC | IMCSC('1+i') | 0.6215180171704283-0.3039310016284264i |
| IMCSCH | IMCSCH('1+i') | 0.3039310016284264-0.6215180171704283i |
| IMDIV | IMDIV('1+2i', '3+4i') | 0.44+0.08i |
| IMEXP | IMEXP('1+i') | 1.4686939399158851+2.2873552871788423i |
| IMLN | IMLN('1+i') | 0.3465735902799727+0.7853981633974483i |
| IMLOG10 | IMLOG10('1+i') | 0.1505149978319906+0.3410940884604603i |
| IMLOG2 | IMLOG2('1+i') | 0.5000000000000001+1.1330900354567985i |
| IMPOWER | IMPOWER('1+i', 2) | 1.2246063538223775e-16+2.0000000000000004i |
| IMPRODUCT | IMPRODUCT('1+2i', '3+4i', '5+6i') | -85+20i |
| IMREAL | IMREAL('3+4i') | 3 |
| IMSEC | IMSEC('1+i') | 0.4983370305551868+0.591083841721045i |
| IMSECH | IMSECH('1+i') | 0.4983370305551868-0.591083841721045i |
| IMSIN | IMSIN('1+i') | 1.2984575814159773+0.6349639147847361i |
| IMSINH | IMSINH('1+i') | 0.6349639147847361+1.2984575814159773i |
| IMSQRT | IMSQRT('1+i') | 1.0986841134678098+0.45508986056222733i |
| IMSUB | IMSUB('3+4i', '1+2i') | 2+2i |
| IMSUM | IMSUM('1+2i', '3+4i', '5+6i') | 9+12i |
| IMTAN | IMTAN('1+i') | 0.2717525853195117+1.0839233273386946i |
| OCT2BIN | OCT2BIN('52') | 101010 |
| OCT2DEC | OCT2DEC('52') | 42 |
| OCT2HEX | OCT2HEX('52') | 2a |
Logical Functions
| Title | Call | Result |
|---|---|---|
| AND | AND(true, false, true) | false |
| FALSE | FALSE() | false |
| IF | IF(true, 'Hello!', 'Goodbye!') | Hello! |
| IFS | IFS(false, 'Hello!', true, 'Goodbye!') | Goodbye! |
| IFERROR | IFERROR('#DIV/0!', 'Error') | Error |
| IFNA | IFNA('#N/A', 'Error') | Error |
| NOT | NOT(true) | false |
| OR | OR(true, false, true) | true |
| SWITCH | SWITCH(7, 9, 'Nine', 7, 'Seven') | Seven |
| TRUE | TRUE() | true |
| XOR | XOR(true, false, true) | false |
Math Functions
| Title | Call | Result |
|---|---|---|
| ABS | ABS(-4) | 4 |
| ACOS | ACOS(-0.5) | 2.0943951023931957 |
| ACOSH | ACOSH(10) | 2.993222846126381 |
| ACOT | ACOT(2) | 0.46364760900080615 |
| ACOTH | ACOTH(6) | 0.16823611831060645 |
| AGGREGATE | AGGREGATE(9, 4, [-5,15], [32,'Hello World!']) | 10,32 |
| ARABIC | ARABIC('MCMXII') | 1912 |
| ASIN | ASIN(-0.5) | -0.5235987755982988 |
| ASINH | ASINH(-2.5) | -1.6472311463710965 |
| ATAN | ATAN(1) | 0.7853981633974483 |
| ATAN2 | ATAN2(-1, -1) | -2.356194490192345 |
| ATANH | ATANH(-0.1) | -0.10033534773107562 |
| BASE | BASE(15, 2, 10) | 0000001111 |
| CEILING | CEILING(-5.5, 2, -1) | -6 |
| CEILINGMATH | CEILINGMATH(-5.5, 2, -1) | -6 |
| CEILINGPRECISE | CEILINGPRECISE(-4.1, -2) | -4 |
| COMBIN | COMBIN(8, 2) | 28 |
| COMBINA | COMBINA(4, 3) | 20 |
| COS | COS(1) | 0.5403023058681398 |
| COSH | COSH(1) | 1.5430806348152437 |
| COT | COT(30) | -0.15611995216165922 |
| COTH | COTH(2) | 1.0373147207275482 |
| CSC | CSC(15) | 1.5377805615408537 |
| CSCH | CSCH(1.5) | 0.46964244059522464 |
| DECIMAL | DECIMAL('FF', 16) | 255 |
| ERF | ERF(1) | 0.8427007929497149 |
| ERFC | ERFC(1) | 0.1572992070502851 |
| EVEN | EVEN(-1) | -2 |
| EXP | EXP(1) | 2.718281828459045 |
| FACT | FACT(5) | 120 |
| FACTDOUBLE | FACTDOUBLE(7) | 105 |
| FLOOR | FLOOR(-3.1) | -4 |
| FLOORMATH | FLOORMATH(-4.1, -2, -1) | -4 |
| FLOORPRECISE | FLOORPRECISE(-3.1, -2) | -4 |
| GCD | GCD(24, 36, 48) | 12 |
| INT | INT(-8.9) | -9 |
| ISEVEN | ISEVEN(-2.5) | true |
| ISOCEILING | ISOCEILING(-4.1, -2) | -4 |
| ISODD | ISODD(-2.5) | false |
| LCM | LCM(24, 36, 48) | 144 |
| LN | LN(86) | 4.454347296253507 |
| LOG | LOG(8, 2) | 3 |
| LOG10 | LOG10(100000) | 5 |
| MOD | MOD(3, -2) | -1 |
| MROUND | MROUND(-10, -3) | -9 |
| MULTINOMIAL | MULTINOMIAL(2, 3, 4) | 1260 |
| ODD | ODD(-1.5) | -3 |
| POWER | POWER(5, 2) | 25 |
| PRODUCT | PRODUCT(5, 15, 30) | 2250 |
| QUOTIENT | QUOTIENT(-10, 3) | -3 |
| RADIANS | RADIANS(180) | 3.141592653589793 |
| RAND | RAND() | [Random real number greater between 0 and 1] |
| RANDBETWEEN | RANDBETWEEN(-1, 1) | [Random integer between bottom and top] |
| ROUND | ROUND(626.3, -3) | 1000 |
| ROUNDDOWN | ROUNDDOWN(-3.14159, 2) | -3.14 |
| ROUNDUP | ROUNDUP(-3.14159, 2) | -3.15 |
| SEC | SEC(45) | 1.9035944074044246 |
| SECH | SECH(45) | 5.725037161098787e-20 |
| SIGN | SIGN(-0.00001) | -1 |
| SIN | SIN(1) | 0.8414709848078965 |
| SINH | SINH(1) | 1.1752011936438014 |
| SQRT | SQRT(16) | 4 |
| SQRTPI | SQRTPI(2) | 2.5066282746310002 |
| SUBTOTAL | SUBTOTAL(9, [-5,15], [32,'Hello World!']) | 10,32 |
| SUM | SUM(-5, 15, 32, 'Hello World!') | 42 |
| SUMIF | SUMIF([2,4,8,16], '>5') | 24 |
| SUMIFS | SUMIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4') | 12 |
| SUMPRODUCT | SUMPRODUCT([[1,2],[3,4]], [[1,0],[0,1]]) | 5 |
| SUMSQ | SUMSQ(3, 4) | 25 |
| SUMX2MY2 | SUMX2MY2([1,2], [3,4]) | -20 |
| SUMX2PY2 | SUMX2PY2([1,2], [3,4]) | 30 |
| SUMXMY2 | SUMXMY2([1,2], [3,4]) | 8 |
| TAN | TAN(1) | 1.5574077246549023 |
| TANH | TANH(-2) | -0.9640275800758168 |
| TRUNC | TRUNC(-8.9) | -8 |
Statistical Functions
| Title | Call | Result |
|---|---|---|
| AVEDEV | AVEDEV([2,4], [8,16]) | 4.5 |
| AVERAGE | AVERAGE([2,4], [8,16]) | 7.5 |
| AVERAGEA | AVERAGEA([2,4], [8,16]) | 7.5 |
| AVERAGEIF | AVERAGEIF([2,4,8,16], '>5', [1, 2, 3, 4]) | 3.5 |
| AVERAGEIFS | AVERAGEIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4') | 6 |
| BETADIST | BETADIST(2, 8, 10, true, 1, 3) | 0.6854705810117458 |
| BETAINV | BETAINV(0.6854705810117458, 8, 10, 1, 3) | 1.9999999999999998 |
| BINOMDIST | BINOMDIST(6, 10, 0.5, false) | 0.205078125 |
| CORREL | CORREL([3,2,4,5,6], [9,7,12,15,17]) | 0.9970544855015815 |
| COUNT | COUNT([1,2], [3,4]) | 4 |
| COUNTA | COUNTA([1, null, 3, 'a', '', 'c']) | 4 |
| COUNTBLANK | COUNTBLANK([1, null, 3, 'a', '', 'c']) | 2 |
| COUNTIF | COUNTIF(['Caen', 'Melbourne', 'Palo Alto', 'Singapore'], 'a') | 3 |
| COUNTIFS | COUNTIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4') | 2 |
| COUNTUNIQUE | COUNTUNIQUE([1,1,2,2,3,3]) | 3 |
| COVARIANCEP | COVARIANCEP([3,2,4,5,6], [9,7,12,15,17]) | 5.2 |
| COVARIANCES | COVARIANCES([2,4,8], [5,11,12]) | 9.666666666666668 |
| DEVSQ | DEVSQ([2,4,8,16]) | 115 |
| EXPONDIST | EXPONDIST(0.2, 10, true) | 0.8646647167633873 |
| FDIST | FDIST(15.2069, 6, 4, false) | 0.0012237917087831735 |
| FINV | FINV(0.01, 6, 4) | 0.10930991412457851 |
| FISHER | FISHER(0.75) | 0.9729550745276566 |
| FISHERINV | FISHERINV(0.9729550745276566) | 0.75 |
| FORECAST | FORECAST(30, [6,7,9,15,21], [20,28,31,38,40]) | 10.607253086419755 |
| FREQUENCY | FREQUENCY([79,85,78,85,50,81,95,88,97], [70,79,89]) | 1,2,4,2 |
| GAMMA | GAMMA(2.5) | 1.3293403919101043 |
| GAMMALN | GAMMALN(10) | 12.801827480081961 |
| GAUSS | GAUSS(2) | 0.4772498680518208 |
| GEOMEAN | GEOMEAN([2,4], [8,16]) | 5.656854249492381 |
| GROWTH | GROWTH([2,4,8,16], [1,2,3,4], [5]) | 32.00000000000003 |
| HARMEAN | HARMEAN([2,4], [8,16]) | 4.266666666666667 |
| HYPGEOMDIST | HYPGEOMDIST(1, 4, 8, 20, false) | 0.3632610939112487 |
| INTERCEPT | INTERCEPT([2,3,9,1,8], [6,5,11,7,5]) | 0.04838709677419217 |
| KURT | KURT([3,4,5,2,3,4,5,6,4,7]) | -0.15179963720841627 |
| LARGE | LARGE([3,5,3,5,4,4,2,4,6,7], 3) | 5 |
| LINEST | LINEST([1,9,5,7], [0,4,2,3], true, true) | 2,1 |
| LOGNORMDIST | LOGNORMDIST(4, 3.5, 1.2, true) | 0.0390835557068005 |
| LOGNORMINV | LOGNORMINV(0.0390835557068005, 3.5, 1.2, true) | 4.000000000000001 |
| MAX | MAX([0.1,0.2], [0.4,0.8], [true, false]) | 0.8 |
| MAXA | MAXA([0.1,0.2], [0.4,0.8], [true, false]) | 1 |
| MEDIAN | MEDIAN([1,2,3], [4,5,6]) | 3.5 |
| MIN | MIN([0.1,0.2], [0.4,0.8], [true, false]) | 0.1 |
| MINA | MINA([0.1,0.2], [0.4,0.8], [true, false]) | 0 |
| MODEMULT | MODEMULT([1,2,3,4,3,2,1,2,3]) | 2,3 |
| MODESNGL | MODESNGL([1,2,3,4,3,2,1,2,3]) | 2 |
| NORMDIST | NORMDIST(42, 40, 1.5, true) | 0.9087887802741321 |
| NORMINV | NORMINV(0.9087887802741321, 40, 1.5) | 42 |
| NORMSDIST | NORMSDIST(1, true) | 0.8413447460685429 |
| NORMSINV | NORMSINV(0.8413447460685429) | 1.0000000000000002 |
| PEARSON | PEARSON([9,7,5,3,1], [10,6,1,5,3]) | 0.6993786061802354 |
| PERCENTILEEXC | PERCENTILEEXC([1,2,3,4], 0.3) | 1.5 |
| PERCENTILEINC | PERCENTILEINC([1,2,3,4], 0.3) | 1.9 |
| PERCENTRANKEXC | PERCENTRANKEXC([1,2,3,4], 2, 2) | 0.4 |
| PERCENTRANKINC | PERCENTRANKINC([1,2,3,4], 2, 2) | 0.33 |
| PERMUT | PERMUT(100, 3) | 970200 |
| PERMUTATIONA | PERMUTATIONA(4, 3) | 64 |
| PHI | PHI(0.75) | 0.30113743215480443 |
| POISSONDIST | POISSONDIST(2, 5, true) | 0.12465201948308113 |
| PROB | PROB([1,2,3,4], [0.1,0.2,0.2,0.1], 2, 3) | 0.4 |
| QUARTILEEXC | QUARTILEEXC([1,2,3,4], 1) | 1.25 |
| QUARTILEINC | QUARTILEINC([1,2,3,4], 1) | 1.75 |
| RANKAVG | RANKAVG(4, [2,4,4,8,8,16], false) | 4.5 |
| RANKEQ | RANKEQ(4, [2,4,4,8,8,16], false) | 4 |
| RSQ | RSQ([9,7,5,3,1], [10,6,1,5,3]) | 0.4891304347826088 |
| SKEW | SKEW([3,4,5,2,3,4,5,6,4,7]) | 0.3595430714067974 |
| SKEWP | SKEWP([3,4,5,2,3,4,5,6,4,7]) | 0.303193339354144 |
| SLOPE | SLOPE([1,9,5,7], [0,4,2,3]) | 2 |
| SMALL | SMALL([3,5,3,5,4,4,2,4,6,7], 3) | 3 |
| STANDARDIZE | STANDARDIZE(42, 40, 1.5) | 1.3333333333333333 |
| STDEVA | STDEVA([2,4], [8,16], [true, false]) | 6.013872850889572 |
| STDEVP | STDEVP([2,4], [8,16], [true, false]) | 5.361902647381804 |
| STDEVPA | STDEVPA([2,4], [8,16], [true, false]) | 5.489889697333535 |
| STDEVS | STDEVS([2,4], [8,16], [true, false]) | 6.191391873668904 |
| STEYX | STEYX([2,3,9,1,8,7,5], [6,5,11,7,5,4,4]) | 3.305718950210041 |
| TDIST | TDIST(60, 1, true) | 0.9946953263673741 |
| TINV | TINV(0.9946953263673741, 1) | 59.99999999996535 |
| TRIMMEAN | TRIMMEAN([4,5,6,7,2,3,4,5,1,2,3], 0.2) | 3.7777777777777777 |
| VARA | VARA([2,4], [8,16], [true, false]) | 36.16666666666667 |
| VARP | VARP([2,4], [8,16], [true, false]) | 28.75 |
| VARPA | VARPA([2,4], [8,16], [true, false]) | 30.13888888888889 |
| VARS | VARS([2,4], [8,16], [true, false]) | 38.333333333333336 |
| WEIBULLDIST | WEIBULLDIST(105, 20, 100, true) | 0.9295813900692769 |
| ZTEST | ZTEST([3,6,7,8,6,5,4,2,1,9], 4) | 0.09057419685136381 |
Text Functions
| Title | Call | Result |
|---|---|---|
| CHAR | CHAR(65) | A |
| CLEAN | CLEAN('Monthly report') | Monthly report |
| CODE | CODE('A') | 65 |
| CONCATENATE | CONCATENATE('Andreas', ' ', 'Hauser') | Andreas Hauser |
| EXACT | EXACT('Word', 'word') | false |
| FIND | FIND('M', 'Miriam McGovern', 3) | 8 |
| LEFT | LEFT('Sale Price', 4) | Sale |
| LEN | LEN('Phoenix, AZ') | 11 |
| LOWER | LOWER('E. E. Cummings') | e. e. cummings |
| MID | MID('Fluid Flow', 7, 20) | Flow |
| NUMBERVALUE | NUMBERVALUE('2.500,27', ',', '.') | 2500.27 |
| PROPER | PROPER('this is a TITLE') | This Is A Title |
| REGEXEXTRACT | REGEXEXTRACT('Palo Alto', 'Alto') | Alto |
| REGEXMATCH | REGEXMATCH('Palo Alto', 'Alto') | true |
| REGEXREPLACE | REGEXREPLACE('Sutoiku', 'utoiku', 'TOIC') | STOIC |
| REPLACE | REPLACE('abcdefghijk', 6, 5, '*') | abcde*k |
| REPT | REPT('*-', 3) | *-*-*- |
| RIGHT | RIGHT('Sale Price', 5) | Price |
| ROMAN | ROMAN(499) | CDXCIX |
| SEARCH | SEARCH('margin', 'Profit Margin') | 8 |
| SPLIT | SPLIT('A,B,C', ',') | A,B,C |
| SUBSTITUTE | SUBSTITUTE('Quarter 1, 2011', '1', '2', 3) | Quarter 1, 2012 |
| T | T('Rainfall') | Rainfall |
| TRIM | TRIM(' First Quarter Earnings ') | First Quarter Earnings |
| UNICHAR | UNICHAR(66) | B |
| UNICODE | UNICODE('B') | 66 |
| UPPER | UPPER('total') | TOTAL |