公式参考

Formula:
Result:
Click an Example call below to populate and execute in the input above.

DATE

FunctionDefinitionExample callParametersExpected result
DATECreate 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)
DATEVALUEConverts a date in text format to a serial number.DATEVALUE('8/22/2011')text string representing a dateMon Aug 22 2011 00:00:00 GMT-0700 (PDT)
DAYReturns the day of the specified date.DAY('15-Apr-11')date value or date text string15
DAYSCalculates the number of days between two dates.DAYS('3/15/11', '2/1/11')end date, start date42
DAYS360Calculates the number of days between two dates based on a 360-day year.DAYS360('1-Jan-11', '31-Dec-11')start date, end date360
EDATEReturns 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)
EOMONTHReturns 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)
HOURReturns the hour part of a time value.HOUR('7/18/2011 7:45:00 AM')time value or time text string7
MINUTEReturns the minute part of a time value.MINUTE('2/1/2011 12:45:00 PM')time value or time text string45
ISOWEEKNUMReturns the ISO week number of the year for a given date.ISOWEEKNUM('3/9/2012')date value or date text string10
MONTHReturns the month part of a date value.MONTH('15-Apr-11')date value or date text string4
NETWORKDAYSCalculates 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 array109
NETWORKDAYSINTLCalculates 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 array23
NOWReturns the current date and time.NOW()no parametersThu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time)
SECONDReturns the second part of a time value.SECOND('2/1/2011 4:48:18 PM')time value or time text string18
TIMECreates a time from the given hour, minute, and second.TIME(16, 48, 10)hour (0-23), minute (0-59), second (0-59)0.7001157407407408
TIMEVALUEConverts a time in text format to a time serial number.TIMEVALUE('22-Aug-2011 6:35 AM')text string representing a time0.2743055555555556
TODAYReturns the current date.TODAY()no parametersThu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time)
WEEKDAYReturns the day of the week as a number.WEEKDAY('2/14/2008', 3)date value or date text string, return type (1-3)3
YEARReturns the year part of a date value.YEAR('7/5/2008')date value or date text string2008
WEEKNUMReturns 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
WORKDAYReturns 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 arrayMon May 04 2009 00:00:00 GMT-0700 (PDT)
WORKDAYINTLReturns 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 patternSun Feb 05 2012 00:00:00 GMT-0800 (PST)
YEARFRACCalculates 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.5780821917808219

FINANCIAL

FunctionDefinitionExample callParametersExpected result
ACCRINTCalculate 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, basis350
CUMIPMTCalculate 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.77251395708
CUMPRINCCalculate 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.0863271085149
DBCalculate 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, month159500
DDBCalculate 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, factor250000
DOLLARDEConverts a dollar price expressed as a fraction into a decimal number.DOLLARDE(1.1, 16)fractional_dollar, fraction1.625
DOLLARFRConverts a dollar price expressed as a decimal number into a fractional dollar.DOLLARFR(1.625, 16)decimal_dollar, fraction1.1
EFFECTCalculate the effective annual interest rate.EFFECT(0.1, 4)nominal_rate, npery0.10381289062499977
FVCalculate the future value of an investment.FV(0.1/12, 10, -100, -1000, 0)rate, nper, pmt, pv, type2124.874409194097
FVSCHEDULECalculate the future value of an initial principal after applying a series of compound interest rates.FVSCHEDULE(100, [0.09,0.1,0.11])principal, schedule133.08900000000003
IPMTCalculate 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, type928.8235718400465
IRRCalculate the internal rate of return for a series of cash flows.IRR([-75000,12000,15000,18000,21000,24000], 0.075)values, guess0.05715142887178447
ISPMTCalculate the interest paid during a specific period of a loan.ISPMT(0.1/12, 6, 2*12, 100000)rate, per, nper, pv-625
MIRRCalculate 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_rate0.07971710360838036
NOMINALCalculate the nominal annual interest rate.NOMINAL(0.1, 4)effect_rate, npery0.09645475633778045
NPERCalculate 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, type63.39385422740764
NPVCalculate 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.3503176012546
PDURATIONCalculate the number of periods required for an investment to reach a specified value.PDURATION(0.1, 1000, 2000)rate, pv, fv7.272540897341714
PMTCalculate 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.08563793503
PPMTCalculate 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.909209775076
PVCalculate the present value of an investment.PV(0.1/12, 2*12, 1000, 10000, 0)rate, nper, pmt, fv, type-29864.950264779152
RATECalculate the interest rate per period of an annuity.RATE(2*12, -1000, -10000, 100000, 0, 0.1)nper, pmt, pv, fv, type, guess0.06517891177181533

ENGINEERING

FunctionDefinitionExample callParametersExpected result
BIN2DECConvert a binary number to decimal.BIN2DEC(101010)binary_number42
BIN2HEXConvert a binary number to hexadecimal.BIN2HEX(101010)binary_number2a
BIN2OCTConvert a binary number to octal.BIN2OCT(101010)binary_number52
BITANDPerform a bitwise AND operation on two numbers.BITAND(42, 24)integer, integer8
BITLSHIFTShift a number left by the specified amount of bits.BITLSHIFT(42, 24)integer, shift_amount704643072
BITORPerform a bitwise OR operation on two numbers.BITOR(42, 24)integer, integer58
BITRSHIFTShift a number right by the specified amount of bits.BITRSHIFT(42, 2)integer, shift_amount10
BITXORPerform a bitwise XOR operation on two numbers.BITXOR(42, 24)integer, integer50
COMPLEXCreate a complex number.COMPLEX(3, 4)real_part, imaginary_part3+4i
CONVERTConvert a value from one measurement system to another.CONVERT(64, 'kibyte', 'bit')value, from_unit, to_unit524288
DEC2BINConvert a decimal number to binary.DEC2BIN(42)decimal_number101010
DEC2HEXConvert a decimal number to hexadecimal.DEC2HEX(42)decimal_number2a
DEC2OCTConvert a decimal number to octal.DEC2OCT(42)decimal_number52
DELTATest whether two values are equal.DELTA(42, 42)value, value1
ERFCalculate the error function.ERF(1)upper_limit0.8427007929497149
ERFCCalculate the complementary error function.ERFC(1)lower_limit0.1572992070502851
GESTEPTest if one number is greater than or equal to another.GESTEP(42, 24)value, threshold1
HEX2BINConvert a hexadecimal number to binary.HEX2BIN('2a')hexadecimal_number101010
HEX2DECConvert a hexadecimal number to decimal.HEX2DEC('2a')hexadecimal_number42
HEX2OCTConvert a hexadecimal number to octal.HEX2OCT('2a')hexadecimal_number52
IMABSCalculate the absolute value (modulus) of a complex number.IMABS('3+4i')complex_number5
IMAGINARYReturn the imaginary part of a complex number.IMAGINARY('3+4i')complex_number4
IMARGUMENTCalculate the argument of a complex number.IMARGUMENT('3+4i')complex_number0.9272952180016122
IMCONJUGATECalculate the conjugate of a complex number.IMCONJUGATE('3+4i')complex_number3-4i
IMCOSCalculate the cosine of a complex number.IMCOS('1+i')complex_number0.8337300251311491-0.9888977057628651i
IMCOSHCalculate the hyperbolic cosine of a complex number.IMCOSH('1+i')complex_number0.8337300251311491+0.9888977057628651i
IMCOTCalculate the cotangent of a complex number.IMCOT('1+i')complex_number0.21762156185440265-0.8680141428959249i
IMCSCCalculate the cosecant of a complex number.IMCSC('1+i')complex_number0.6215180171704283-0.3039310016284264i
IMCSCHCalculate the hyperbolic cosecant of a complex number.IMCSCH('1+i')complex_number0.3039310016284264-0.6215180171704283i
IMDIVCalculate the division of two complex numbers.IMDIV('1+2i', '3+4i')dividend, divisor0.44+0.08i
IMEXPCalculate the exponential of a complex number.IMEXP('1+i')complex_number1.4686939399158851+2.2873552871788423i
IMLNCalculate the natural logarithm of a complex number.IMLN('1+i')complex_number0.3465735902799727+0.7853981633974483i
IMLOG10Calculate the base-10 logarithm of a complex number.IMLOG10('1+i')complex_number0.1505149978319906+0.3410940884604603i
IMLOG2Calculate the base-2 logarithm of a complex number.IMLOG2('1+i')complex_number0.5000000000000001+1.1330900354567985i
IMPOWERCalculate the power of a complex number.IMPOWER('1+i', 2)complex_number, power1.2246063538223775e-16+2.0000000000000004i
IMPRODUCTCalculate the product of multiple complex numbers.IMPRODUCT('1+2i', '3+4i', '5+6i')array_of_complex_numbers-85+20i
IMREALReturn the real part of a complex number.IMREAL('3+4i')complex_number3
IMSECCalculate the secant of a complex number.IMSEC('1+i')complex_number0.4983370305551868+0.591083841721045i
IMSECHCalculate the hyperbolic secant of a complex number.IMSECH('1+i')complex_number0.4983370305551868-0.591083841721045i
IMSINCalculate the sine of a complex number.IMSIN('1+i')complex_number1.2984575814159773+0.6349639147847361i
IMSINHCalculate the hyperbolic sine of a complex number.IMSINH('1+i')complex_number0.6349639147847361+1.2984575814159773i
IMSQRTCalculate the square root of a complex number.IMSQRT('1+i')complex_number1.0986841134678098+0.45508986056222733i
IMSUBCalculate the subtraction of two complex numbers.IMSUB('3+4i', '1+2i')minuend, subtrahend2+2i
IMSUMCalculate the sum of multiple complex numbers.IMSUM('1+2i', '3+4i', '5+6i')array_of_complex_numbers9+12i
IMTANCalculate the tangent of a complex number.IMTAN('1+i')complex_number0.2717525853195117+1.0839233273386946i
OCT2BINConvert an octal number to binary.OCT2BIN('52')octal_number101010
OCT2DECConvert an octal number to decimal.OCT2DEC('52')octal_number42
OCT2HEXConvert an octal number to hexadecimal.OCT2HEX('52')octal_number2a

LOGICAL

FunctionDefinitionExample callParametersExpected result
ANDReturns 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.
FALSEReturns the logical value false.FALSE()No parameters.
IFReturns 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!
IFSChecks 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!
NOTReverses the logic of its argument, true becomes false, and false becomes true.NOT(true)A single logical value (boolean).
ORReturns 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.
SWITCHEvaluates 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
TRUEReturns the logical value true.TRUE()No parameters.
XORReturns 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

FunctionDefinitionExample callParametersExpected result
ABSReturns the absolute value of a number.ABS(-4)Number.4
ACOSCalculates the arccosine (in radians).ACOS(-0.5)A number between -1 and 1.2.0943951023931957
ACOSHCalculates the inverse hyperbolic cosine.ACOSH(10)A number greater than or equal to 1.2.993222846126381
ACOTCalculates the arccotangent (in radians).ACOT(2)Any number.0.46364760900080615
ACOTHCalculates the inverse hyperbolic cotangent.ACOTH(6)A number with an absolute value greater than 1.0.16823611831060645
AGGREGATEPerforms aggregate operations, ignoring errors or hidden rows.AGGREGATE(9, 4, [-5,15], [32,'Hello World!'])Function number, option, array1, ..., arrayN.10,32
ARABICConverts a Roman numeral to an Arabic numeral.ARABIC('MCMXII')Roman numeral string.1912
ASINCalculates the arcsine (in radians).ASIN(-0.5)A number between -1 and 1.-0.5235987755982988
ASINHCalculates the inverse hyperbolic sine.ASINH(-2.5)Any number.-1.6472311463710965
ATANCalculates the arctangent (in radians).ATAN(1)Any number.0.7853981633974483
ATAN2Calculates the arctangent (in radians) from coordinates.ATAN2(-1, -1)Y coordinate, X coordinate.-2.356194490192345
ATANHCalculates the inverse hyperbolic tangent.ATANH(-0.1)A number between -1 and 1.-0.10033534773107562
BASEConverts a number into a text representation at a given base.BASE(15, 2, 10)Number, base, [minimum length].0000001111
CEILINGRounds a number up to the nearest multiple.CEILING(-5.5, 2, -1)Number, multiple, [mode].-6
CEILINGMATHRounds a number up using specified multiple and direction.CEILINGMATH(-5.5, 2, -1)Number, [multiple], [mode].-6
CEILINGPRECISERounds a number up to the nearest multiple, regardless of sign.CEILINGPRECISE(-4.1, -2)Number, [multiple].-4
COMBINCalculates the number of combinations.COMBIN(8, 2)Total number, chosen number.28
COMBINACalculates the number of combinations with repetitions.COMBINA(4, 3)Total number, chosen number.20
COSCalculates the cosine (in radians).COS(1)Angle (in radians).0.5403023058681398
COSHCalculates the hyperbolic cosine.COSH(1)Any number.1.5430806348152437
COTCalculates the cotangent (in radians).COT(30)Angle (in radians).-0.15611995216165922
COTHCalculates the hyperbolic cotangent.COTH(2)Any number.1.0373147207275482
CSCCalculates the cosecant (in radians).CSC(15)Angle (in radians).1.5377805615408537
CSCHCalculates the hyperbolic cosecant.CSCH(1.5)Any number.0.46964244059522464
DECIMALConverts a text representation of a number to decimal.DECIMAL('FF', 16)Text, base.255
ERFCalculates the error function.ERF(1)Upper limit.0.8427007929497149
ERFCCalculates the complementary error function.ERFC(1)Lower limit.0.1572992070502851
EVENRounds a number up to the nearest even integer.EVEN(-1)Number.-2
EXPCalculates e raised to the power of a given number.EXP(1)Exponent.2.718281828459045
FACTCalculates the factorial.FACT(5)Non-negative integer.120
FACTDOUBLECalculates the double factorial.FACTDOUBLE(7)Non-negative integer.105
FLOORRounds a number down to the nearest multiple.FLOOR(-3.1)Number, multiple.-4
FLOORMATHRounds a number down using specified multiple and direction.FLOORMATH(-4.1, -2, -1)Number, [multiple], [mode].-4
FLOORPRECISERounds a number down to the nearest multiple, regardless of sign.FLOORPRECISE(-3.1, -2)Number, [multiple].-4
GCDCalculates the greatest common divisor.GCD(24, 36, 48)Two or more integers.12
INTRounds a number down to the nearest integer.INT(-8.9)Number.-9
ISEVENTests whether a number is even.ISEVEN(-2.5)Number.
ISOCEILINGRounds a number up to the nearest multiple, following ISO standards.ISOCEILING(-4.1, -2)Number, [multiple].-4
ISODDTests whether a number is odd.ISODD(-2.5)Number.
LCMCalculates the least common multiple.LCM(24, 36, 48)Two or more integers.144
LNCalculates the natural logarithm.LN(86)Positive value.4.454347296253507
LOGCalculates the logarithm for a specified base.LOG(8, 2)Number, base.3
LOG10Calculates the logarithm with base 10.LOG10(100000)Positive value.5
MODCalculates the remainder of two numbers divided.MOD(3, -2)Dividend, divisor.-1
MROUNDRounds a number to the nearest multiple.MROUND(-10, -3)Number, multiple.-9
MULTINOMIALCalculates the multinomial coefficient.MULTINOMIAL(2, 3, 4)Two or more non-negative integers.1260
ODDRounds a number up to the nearest odd integer.ODD(-1.5)Number.-3
POWERCalculates a number raised to a power.POWER(5, 2)Base, exponent.25
PRODUCTCalculates the product of multiple numbers.PRODUCT(5, 15, 30)One or more numbers.2250
QUOTIENTCalculates the integer portion of a division, ignoring the remainder.QUOTIENT(-10, 3)Dividend, divisor.-3
RADIANSConverts degrees to radians.RADIANS(180)Angle in degrees.3.141592653589793
RANDGenerates a random real number between 0 and 1.RAND()No parameters.[Random real number between 0 and 1]
RANDBETWEENGenerates a random integer within a specified range.RANDBETWEEN(-1, 1)Bottom, top.[Random integer between bottom and top]
ROUNDRounds a number to a specified number of digits.ROUND(626.3, -3)Number, num_digits.1000
ROUNDDOWNRounds a number down to a specified number of digits.ROUNDDOWN(-3.14159, 2)Number, num_digits.-3.14
ROUNDUPRounds a number up to a specified number of digits.ROUNDUP(-3.14159, 2)Number, num_digits.-3.15
SECCalculates the secant (in radians).SEC(45)Angle in radians.1.9035944074044246
SECHCalculates the hyperbolic secant.SECH(45)Any number.5.725037161098787e-20
SIGNReturns the sign of a number.SIGN(-0.00001)Number.-1
SINCalculates the sine (in radians).SIN(1)Angle in radians.0.8414709848078965
SINHCalculates the hyperbolic sine.SINH(1)Any number.1.1752011936438014
SQRTCalculates the square root.SQRT(16)Non-negative number.4
SQRTPICalculates the square root of a number multiplied by pi.SQRTPI(2)Non-negative number.2.5066282746310002
SUBTOTALCalculates a subtotal in a list or database, ignoring hidden rows.SUBTOTAL(9, [-5,15], [32,'Hello World!'])Function_num, array1, ..., arrayN.10,32
SUMCalculates the sum of numbers, ignoring text.SUM(-5, 15, 32, 'Hello World!')One or more numbers.42
SUMIFSums values based on a condition.SUMIF([2,4,8,16], '>5')Array, criteria.24
SUMIFSSums 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
SUMPRODUCTCalculates the sum of the products of corresponding elements in arrays.SUMPRODUCT([[1,2],[3,4]], [[1,0],[0,1]])Two or more arrays.5
SUMSQCalculates the sum of squares of numbers.SUMSQ(3, 4)One or more numbers.25
SUMX2MY2Calculates the sum of the difference of squares of corresponding elements in two arrays.SUMX2MY2([1,2], [3,4])Array1, array2.-20
SUMX2PY2Calculates the sum of the sum of squares of corresponding elements in two arrays.SUMX2PY2([1,2], [3,4])Array1, array2.30
SUMXMY2Calculates the sum of squares of differences of corresponding elements in two arrays.SUMXMY2([1,2], [3,4])Array1, array2.8
TANCalculates the tangent (in radians).TAN(1)Angle in radians.1.5574077246549023
TANHCalculates the hyperbolic tangent.TANH(-2)Any number.-0.9640275800758168
TRUNCTruncates a number without rounding.TRUNC(-8.9)Number, [num_digits].-8

STATISTICAL

FunctionDefinitionExample callParametersExpected result
AVEDEVCalculates the average of the absolute deviations.AVEDEV([2,4], [8,16])Arguments are arrays of numbers representing data points.4.5
AVERAGECalculates the arithmetic mean.AVERAGE([2,4], [8,16])Arguments are arrays of numbers representing data points to average.7.5
AVERAGEACalculates 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
AVERAGEIFCalculates 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
AVERAGEIFSCalculates 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
BETADISTCalculates the cumulative beta probability density function.BETADIST(2, 8, 10, true, 1, 3)Parameters are value, α, β, cumulative flag, A (optional), and B (optional).0.6854705810117458
BETAINVCalculates the inverse of the cumulative beta probability density function.BETAINV(0.6854705810117458, 8, 10, 1, 3)Parameters are probability, α, β, A (optional), and B (optional).1.9999999999999998
BINOMDISTCalculates the binomial distribution probability.BINOMDIST(6, 10, 0.5, false)Parameters are trials, successes, success probability, and cumulative flag.0.205078125
CORRELCalculates 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.9970544855015815
COUNTCounts the number of numeric cells.COUNT([1,2], [3,4])Arguments are arrays or ranges of numbers.4
COUNTACounts the number of non-empty cells.COUNTA([1, null, 3, 'a', '', 'c'])Arguments are arrays or ranges of any type.4
COUNTBLANKCounts the number of blank cells.COUNTBLANK([1, null, 3, 'a', '', 'c'])Arguments are arrays or ranges of any type.2
COUNTIFCounts 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
COUNTIFSCounts 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
COUNTUNIQUECounts the number of unique values.COUNTUNIQUE([1,1,2,2,3,3])Arguments are arrays of numbers or text.3
COVARIANCEPCalculates 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
COVARIANCESCalculates the sample covariance.COVARIANCES([2,4,8], [5,11,12])Arguments are two arrays of numbers representing two datasets.9.666666666666668
DEVSQCalculates the sum of squares of deviations.DEVSQ([2,4,8,16])Arguments are arrays of numbers representing data points.115
EXPONDISTCalculates the exponential distribution.EXPONDIST(0.2, 10, true)Parameters are value, λ, and cumulative flag.0.8646647167633873
FDISTCalculates 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.0012237917087831735
FINVCalculates 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.10930991412457851
FISHERCalculates the Fisher transformation.FISHER(0.75)Parameter is a number representing the correlation coefficient.0.9729550745276566
FISHERINVCalculates the inverse of the Fisher transformation.FISHERINV(0.9729550745276566)Parameter is a number representing the result of the Fisher transformation.0.75
FORECASTPredicts 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-values10.607253086419755
FREQUENCYCalculates the frequency distribution.FREQUENCY([79,85,78,85,50,81,95,88,97], [70,79,89])array of data, array of bin boundaries1,2,4,2
GAMMACalculates the gamma function value.GAMMA(2.5)a positive number1.3293403919101043
GAMMALNCalculates the natural logarithm of the gamma function.GAMMALN(10)a positive number12.801827480081961
GAUSSCalculates the probability in the standard normal distribution.GAUSS(2)a number representing the z-score0.4772498680518208
GEOMEANCalculates the geometric mean.GEOMEAN([2,4], [8,16])array of numbers representing data points5.656854249492381
GROWTHPredicts 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-values32.00000000000003
HARMEANCalculates the harmonic mean.HARMEAN([2,4], [8,16])array of numbers representing data points4.266666666666667
HYPGEOMDISTCalculates the hypergeometric distribution.HYPGEOMDIST(1, 4, 8, 20, false)number of successes in sample, sample size, number of successes in population, population size, cumulative flag0.3632610939112487
INTERCEPTCalculates 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-values0.04838709677419217
KURTCalculates the kurtosis.KURT([3,4,5,2,3,4,5,6,4,7])array of numbers representing data points-0.15179963720841627
LARGEReturns the k-th largest value.LARGE([3,5,3,5,4,4,2,4,6,7], 3)array of numbers, k value5
LINESTPerforms 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 statistics2,1
LOGNORMDISTCalculates the lognormal distribution.LOGNORMDIST(4, 3.5, 1.2, true)value, mean, standard deviation, cumulative flag0.0390835557068005
LOGNORMINVCalculates the inverse of the lognormal distribution.LOGNORMINV(0.0390835557068005, 3.5, 1.2, true)probability, mean, standard deviation, cumulative flag4.000000000000001
MAXReturns the maximum value.MAX([0.1,0.2], [0.4,0.8], [true, false])array of numbers0.8
MAXAReturns 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 values1
MEDIANReturns the median.MEDIAN([1,2,3], [4,5,6])array of numbers3.5
MINReturns the minimum value.MIN([0.1,0.2], [0.4,0.8], [true, false])array of numbers0.1
MINAReturns 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 values0
MODEMULTReturns an array of the most frequent values.MODEMULT([1,2,3,4,3,2,1,2,3])array of numbers2,3
MODESNGLReturns the single most frequent value.MODESNGL([1,2,3,4,3,2,1,2,3])array of numbers2
NORMDISTCalculates the normal distribution.NORMDIST(42, 40, 1.5, true)value, mean, standard deviation, cumulative flag0.9087887802741321
NORMINVCalculates the inverse of the normal distribution.NORMINV(0.9087887802741321, 40, 1.5)probability, mean, standard deviation42
NORMSDISTCalculates the standard normal distribution.NORMSDIST(1, true)a number representing the z-score0.8413447460685429
NORMSINVCalculates the inverse of the standard normal distribution.NORMSINV(0.8413447460685429)a probability value1.0000000000000002
PEARSONCalculates the Pearson product-moment correlation coefficient.PEARSON([9,7,5,3,1], [10,6,1,5,3])two arrays of numbers representing two datasets0.6993786061802354
PERCENTILEEXCCalculates the percentile exclusive.PERCENTILEEXC([1,2,3,4], 0.3)array of numbers, k value1.5
PERCENTILEINCCalculates the percentile inclusive.PERCENTILEINC([1,2,3,4], 0.3)array of numbers, k value1.9
PERCENTRANKEXCCalculates the percentage rank exclusive.PERCENTRANKEXC([1,2,3,4], 2, 2)array of numbers, x value, significance (optional)0.4
PERCENTRANKINCCalculates the percentage rank inclusive.PERCENTRANKINC([1,2,3,4], 2, 2)array of numbers, x value, significance (optional)0.33
PERMUTCalculates the number of permutations.PERMUT(100, 3)total n, chosen k970200
PERMUTATIONACalculates the number of permutations with repetition.PERMUTATIONA(4, 3)total n, chosen k64
PHICalculates the density function for the standard normal distribution.PHI(0.75)a number representing the z-score0.30113743215480443
POISSONDISTCalculates the Poisson distribution.POISSONDIST(2, 5, true)number of events, mean, cumulative flag0.12465201948308113
PROBCalculates 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 limit0.4
QUARTILEEXCCalculates the quartile exclusive.QUARTILEEXC([1,2,3,4], 1)array of numbers, quart value1.25
QUARTILEINCCalculates the quartile inclusive.QUARTILEINC([1,2,3,4], 1)array of numbers, quart value1.75
RANKAVGCalculates the average rank.RANKAVG(4, [2,4,4,8,8,16], false)value, array of numbers, order (ascending/descending)4.5
RANKEQCalculates the rank equal to a specified value.RANKEQ(4, [2,4,4,8,8,16], false)value, array of numbers, order (ascending/descending)4
RSQCalculates the coefficient of determination.RSQ([9,7,5,3,1], [10,6,1,5,3])two arrays of numbers representing two datasets0.4891304347826088
SKEWCalculates the skewness.SKEW([3,4,5,2,3,4,5,6,4,7])array of numbers representing data points0.3595430714067974
SKEWPCalculates the skewness based on a population.SKEWP([3,4,5,2,3,4,5,6,4,7])array of numbers representing data points0.303193339354144
SLOPECalculates the slope of the linear regression.SLOPE([1,9,5,7], [0,4,2,3])array of known y-values, array of known x-values2
SMALLReturns the k-th smallest value.SMALL([3,5,3,5,4,4,2,4,6,7], 3)array of numbers, k value3
STANDARDIZEStandardizes a value to a z-score.STANDARDIZE(42, 40, 1.5)value, mean, standard deviation1.3333333333333333
STDEVACalculates the standard deviation including text and logical values.STDEVA([2,4], [8,16], [true, false])array of numbers, text, or logical values6.013872850889572
STDEVPCalculates the standard deviation of a population.STDEVP([2,4], [8,16], [true, false])array of numbers5.361902647381804
STDEVPACalculates the standard deviation of a population including text and logical values.STDEVPA([2,4], [8,16], [true, false])array of numbers, text, or logical values5.489889697333535
STDEVSCalculates the sample standard deviation.STDEVS([2,4], [8,16], [true, false])array of numbers6.191391873668904
STEYXCalculates 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-values3.305718950210041
TINVCalculates the inverse of the t-distribution.TINV(0.9946953263673741, 1)probability, degrees of freedom59.99999999996535
TRIMMEANCalculates 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 trim3.7777777777777777
VARACalculates variance including text and logical values.VARA([2,4], [8,16], [true, false])array of numbers, text, or logical values36.16666666666667
VARPCalculates variance based on an entire population.VARP([2,4], [8,16], [true, false])array of numbers28.75
VARPACalculates variance based on an entire population including text and logical values.VARPA([2,4], [8,16], [true, false])array of numbers, text, or logical values30.13888888888889
VARSCalculates sample variance.VARS([2,4], [8,16], [true, false])array of numbers38.333333333333336
WEIBULLDISTCalculates the Weibull distribution.WEIBULLDIST(105, 20, 100, true)value, alpha, beta, cumulative flag0.9295813900692769
ZTESTCalculates the one-tailed probability of a z-test.ZTEST([3,6,7,8,6,5,4,2,1,9], 4)array of numbers, hypothesized mean0.09057419685136381

TEXT

FunctionDefinitionExample callParametersExpected result
CHARConverts a number code to its corresponding character.CHAR(65)number representing the character codeA
CLEANRemoves all non-printable characters from text.CLEAN('Monthly report')text string to cleanMonthly report
CODEReturns the numeric code of the first character in a text string.CODE('A')text string containing a single character65
CONCATENATEJoins multiple text strings into one string.CONCATENATE('Andreas', ' ', 'Hauser')one or more text strings to concatenateAndreas Hauser
EXACTChecks if two text strings are identical, case-sensitive.EXACT('Word', 'word')two text strings to compare
FINDFinds the position of a substring within text, starting at a specified position.FIND('M', 'Miriam McGovern', 3)text to find, source text, optional start position8
LEFTExtracts a specified number of characters from the left side of a string.LEFT('Sale Price', 4)text string and number of characters to extractSale
LENReturns the number of characters in a text string.LEN('Phoenix, AZ')text string to count11
LOWERConverts all characters to lowercase.LOWER('E. E. Cummings')text string to converte. e. cummings
MIDExtracts a specified number of characters from the middle of a string.MID('Fluid Flow', 7, 20)text string, start position, number of characters to extractFlow
NUMBERVALUEConverts text to a number based on specified delimiters.NUMBERVALUE('2.500,27', ',', '.')text string, decimal separator, group separator2500.27
PROPERCapitalizes the first letter of each word.PROPER('this is a TITLE')text string to formatThis Is A Title
REPLACEReplaces part of old text with new text.REPLACE('abcdefghijk', 6, 5, '*')original text, start position, number of characters to replace, new textabcde*k
REPTRepeats text a specified number of times.REPT('*-', 3)text string and number of repetitions*-*-*-
RIGHTExtracts a specified number of characters from the right side of a string.RIGHT('Sale Price', 5)text string and number of characters to extractPrice
ROMANConverts an Arabic numeral to Roman numerals.ROMAN(499)Arabic numeral to convertCDXCIX
SEARCHFinds the position of a substring in text, not case-sensitive.SEARCH('margin', 'Profit Margin')text to find, source text8
SUBSTITUTEReplaces specific instances of old text with new text.SUBSTITUTE('Quarter 1, 2011', '1', '2', 3)original text, old text, new text, optional instance to replaceQuarter 1, 2012
TReturns the text if the argument is text; otherwise returns an empty string.T('Rainfall')any data typeRainfall
TRIMRemoves spaces before and after text, preserves internal multiple spaces as one.TRIM(' First Quarter Earnings ')text string to trimFirst Quarter Earnings
TEXTJOINJoins 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 joinThe sun will come up tomorrow.
UNICHARReturns the character corresponding to the given Unicode number.UNICHAR(66)Unicode character codeB
UNICODEReturns the Unicode number of the first character in a text string.UNICODE('B')text string containing a single character66
UPPERConverts all characters to uppercase.UPPER('total')text string to convertTOTAL