Functions Excel Formulas Description
CONCATENATE =CONCATENATE(text1,text2,…) Joins several text items into one text item. Easier to use ‘&’ instead of the function usually.
FLOOR =FLOOR(number,significance) Rounds a number down, toward zero
=BINOMDIST(number_s,trials,prob
BINOMDIST ability_s,cumulative) Returns the individual term binomial distribution probability
CHIDIST =CHIDIST(x,deg_freedom) Returns the one-tailed probability of the chi-squared distribution
=CHITEST(actual_range,expected_r
CHIINV ange) Returns the test for independence
=CONFIDENCE(alpha,standard_dev,
CONFIDENCE size) Returns the confidence interval for a population mean
FTEST =FTEST(array1,array2)
=LOGINV(probability,mean,standar
LOGINV d_dev) Returns the inverse of the lognormal cumulative distribution
=LOGNORMDIST(x,mean,standard_
LOGNORMDIST dev) Returns the cumulative lognormal distribution
MODE ==MODE(number1,number2,…) Returns the most common value in a data set
=NORMDIST(x,mean,standard_dev,
NORMDIST cumulative) Returns the normal cumulative distribution
=NORMINV(probability,mean,stand
NORMINV ard_dev) Returns the inverse of the normal cumulative distribution
NORMSDIST =NORMSDIST(z) Returns the standard normal cumulative distribution
NORMSINV =NORMSINV(probability) Returns the inverse of the standard normal cumulative distribution
PERCENTILE =PERCENTILE(array,k) Returns the k-th percentile of values in a range
=PERCENTRANK(array,x,significanc
PERCENTRANK e) Returns the percentage rank of a value in a data set
POISSON =POISSON(x,mean,cumulative) Returns the Poisson distribution
QUARTILE =QUARTILE(array,quart) Returns the quartile of a data set
RANK =RANK(number,ref,order) Returns the rank of a number in a list of numbers
STDEV =STDEV(number1,number2,…) Estimates standard deviation based on a sample
STDEVP =STDEVP(number1,number2,…) Calculates standard deviation based on the entire population
TDIST =TDIST(x,deg_freedom,tails) Returns the Student’s t-distribution
TINV =TINV(probability,deg_freedom) Returns the inverse of the Student’s t-distribution
VAR =VAR(number1,number2,…) Estimates variance based on a sample
VARP =VARP(number1,number2,…) Calculates variance based on the entire population
=FINV(probability,deg_freedom1,d
FINV eg_freedom2) Returns the inverse of the F probability distribution
=FORECAST(x,known_y’s,known_x’
FORECAST s) Returns a value along a linear trend
BETADIST =BETADIST(x,alpha,beta,A,B) Returns the beta cumulative distribution function
=BETAINV(probability,alpha,beta,A,
BETAINV B) Returns the inverse of the cumulative distribution function for a specified beta distribution
COVAR =COVAR(array1,array2) Returns covariance, the average of the products of paired deviations
=CRITBINOM(trials,probability_s,al Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion
CRITBINOM pha) value
EXPONDIST =EXPONDIST(x,lambda,cumulative) Returns the exponential distribution
POISSON =POISSON(x,mean,cumulative) Returns the Poisson distribution
=FDIST(x,deg_freedom1,deg_freed
FDIST om2) Returns the F probability distribution
=GAMMADIST(x,alpha,beta,cumula
GAMMADIST tive) Returns the gamma distribution
=GAMMAINV(probability,alpha,bet
GAMMAINV a) Returns the inverse of the gamma cumulative distribution
=HYPGEOMDIST(sample_s,number
_sample,population_s,number_po
HYPGEOMDIST p) Returns the hypergeometric distribution
=NEGBINOMDIST(number_f,numbe
NEGBINOMDIST r_s,probability_s) Returns the negative binomial distribution
TTEST =TTEST(array1,array2,tails,type) Returns the probability associated with a Student’s t-test
=WEIBULL(x,alpha,beta,cumulative
WEIBULL ) Calculates variance based on the entire population, including numbers, text, and logical values
ZTEST =ZTEST(array,x,sigma) Returns the one-tailed probability-value of a z-test
Functions Excel Formulas Description
Returns a key performance indicator (KPI) name, property, and measure, and displays the name and property in
=CUBEKPIMEMBER(connection,kpi the cell. A KPI is a quantifiable measurement, such as monthly gross profit or quarterly employee turnover, used
CUBEKPIMEMBER _name,kpi_property,caption) to monitor an organization’s performance.
=CUBEMEMBER(connection,memb
CUBEMEMBER er_expression,caption) RReturns a member or tuple in a cube hierarchy. Use to validate that the member or tuple exists in the cube.
CUBEMEMBERPROPER =CUBEMEMBERPROPERTY(connect Returns the value of a member property in the cube. Use to validate that a member name exists within the
TY ion,member_expression,property) cube and to return the specified property for this member.
=CUBERANKEDMEMBER(connectio Returns the nth, or ranked, member in a set. Use to return one or more elements in a set, such as the top sales
CUBERANKEDMEMBER n,set_expression,rank,caption) performer or top 10 students.
=CUBESET(connection,set_expressi Defines a calculated set of members or tuples by sending a set expression to the cube on the server, which
CUBESET on,caption,sort_order,sort_by) creates the set, and then returns that set to Microsoft Office Excel.
CUBESETCOUNT =CUBESETCOUNT(set) Returns the number of items in a set.
=CUBEVALUE(connection,member_
CUBEVALUE expression1,…) Returns an aggregated value from a cube
Functions Excel Formulas Description
DGET =DGET(database,field,criteria) Extracts from a database a single record that matches the specified criteria
DSUM =DSUM(database,field,criteria) Adds the numbers in the field column of records in the database that match the criteria
DAVERAGE =DAVERAGE(database,field,criteria) Returns the average of selected database entries
DCOUNT =DCOUNT(database,field,criteria) Counts the cells that contain numbers in a database
DCOUNTA =DCOUNTA(database,field,criteria) Counts nonblank cells in a database
DMAX =DMAX(database,field,criteria) Returns the maximum value from selected database entries
DMIN =DMIN(database,field,criteria) Returns the minimum value from selected database entries
=DPRODUCT(database,field,criteria
DPRODUCT ) Multiplies the values in a particular field of records that match the criteria in a database
DSTDEV =DSTDEV(database,field,criteria) Estimates the standard deviation based on a sample of selected database entries
DSTDEVP =DSTDEVP(database,field,criteria) Calculates the standard deviation based on the entire population of selected database entries
DVAR =DVAR(database,field,criteria) Estimates variance based on a sample from selected database entries
DVARP =DVARP(database,field,criteria) Calculates variance based on the entire population of selected database entries
Functions Excel Formulas Description
DATE =DATE(year,month,day) Returns the serial number of a particular date
DATEVALUE =DATEVALUE(date_text) Converts a date in the form of text to a serial number
DAY =DAY(serial_number) Converts a serial number to a day of the month
HOUR =HOUR(serial_number) Converts a serial number to an hour
MINUTE =MINUTE(serial_number) Converts a serial number to a minute
MONTH ==MONTH(serial_number) Converts a serial number to a month
NOW =NOW() Returns the serial number of the current date and time
SECOND =SECOND(serial_number) Converts a serial number to a second
TIME =TIME(hour,minute,second) Returns the serial number of a particular time
TIMEVALUE =TIMEVALUE(time_text) Converts a time in the form of text to a serial number
TODAY =TODAY() Returns the serial number of today’s date
YEAR =YEAR(serial_number) Converts a serial number to a year
=DAYS360(start_date,end_date,me
DAYS360 thod) Calculates the number of days between two dates based on a 360-day year
EDATE =EDATE(start_date,months) Returns the serial number of the date that is the indicated number of months before or after the start date
EOMONTH =EOMONTH(start_date,months) Returns the serial number of the last day of the month before or after a specified number of months
=NETWORKDAYS(start_date,end_d
NETWORKDAYS ate,[holidays]) Returns the number of whole workdays between two dates
=NETWORKDAYS.INTL(start_date,e Returns the number of whole workdays between two dates using parameters to indicate which and how many
NETWORKDAYS.INTL nd_date,[weekend],[holidays]) days are weekend days
=WEEKDAY(serial_number,[return_
WEEKDAY type]) Converts a serial number to a day of the week
=WEEKNUM(serial_number,[return
WEEKNUM _type]) Converts a serial number to a number representing where the week falls numerically with a year
=WORKDAY(start_date, days,
WORKDAY [holidays]) Returns the serial number of the date before or after a specified number of workdays
=WORKDAY.INTL(start_date,days,w Returns the serial number of the date before or after a specified number of workdays using parameters to
WORKDAY.INTL eekend,holidays) indicate which and how many days are weekend days
=YEARFRAC(start_date,end_date,b
YEARFRAC asis) Returns the year fraction representing the number of whole days between start_date and end_date
Functions Excel Formulas Description
=CONVERT(number,from_unit,to_u
CONVERT nit) Converts a number from one measurement system to another
DELTA =DELTA(number1,number2) Tests whether two values are equal
ERF =ERF(lower_limit,upper_limit) Returns the error function
ERFC =ERFC(x) Returns the complementary error function
GESTEP =GESTEP(number,step) Tests whether a number is greater than a threshold value
ERF.PRECISE =ERF.PRECISE(X) Returns the error function
ERFC.PRECISE =ERFC.PRECISE(X) Returns the complementary ERF function integrated between x and infinity
BESSELI =BESSELI(x,n) Returns the modified Bessel function In(x)
BESSELJ =BESSELJ(x,n) Returns the Bessel function Jn(x)
BESSELK =BESSELK(x,n) Returns the modified Bessel function Kn(x)
BESSELY =BESSELY(x,n) Returns the Bessel function Yn(x)
BIN2DEC =BIN2DEC(number) Converts a binary number to decimal
BIN2HEX =BIN2HEX(number,places) Converts a binary number to hexadecimal
DEC2OCT =DEC2OCT(number,places) Converts a decimal number to octal
HEX2BIN =HEX2BIN(number,places) Converts a hexadecimal number to binary
HEX2DEC =HEX2DEC(number) Converts a hexadecimal number to decimal
HEX2OCT =HEX2OCT(number,places) Converts a hexadecimal number to octal
IMABS =IMABS(inumber) Returns the absolute value (modulus) of a complex number
IMAGINARY =IMAGINARY(inumber) Returns the imaginary coefficient of a complex number
IMARGUMENT =IMARGUMENT(inumber) Returns the argument theta, an angle expressed in radians
IMCONJUGATE =IMCONJUGATE(inumber) Returns the complex conjugate of a complex number
IMCOS =IMCOS(inumber) Returns the cosine of a complex number
IMDIV =IMDIV(inumber1,inumber2) Returns the quotient of two complex numbers
IMEXP =IMEXP(inumber) Returns the exponential of a complex number
IMLN =IMLN(inumber) Returns the natural logarithm of a complex number
IMLOG10 =IMLOG10(inumber) Returns the base-10 logarithm of a complex number
IMLOG2 =IMLOG2(inumber) Returns the base-2 logarithm of a complex number
IMPOWER =IMPOWER(inumber,number) Returns a complex number raised to an integer power
=IMPRODUCT(inumber1,inumber2,
IMPRODUCT …) Returns the product of complex numbers
IMREAL =IMREAL(inumber) Returns the real coefficient of a complex number
IMSIN =IMSIN(inumber) Returns the sine of a complex number
IMSQRT =IMSQRT(inumber) Returns the square root of a complex number
IMSUB =IMSUB(inumber1,inumber2) Returns the difference between two complex numbers
IMSUM =IMSUM(inumber1,inumber2,…) Returns the sum of complex numbers
OCT2BIN =OCT2BIN(number,places) Converts an octal number to binary
OCT2DEC =OCT2DEC(number) Converts an octal number to decimal
OCT2HEX =OCT2HEX(number,places) Converts an octal number to hexadecimal
Functions Excel Formulas Description
=AMORDEGRC(cost,date_purchase
d,first_period,salvage,period,rate,b
AMORDEGRC asis) Returns the depreciation for each accounting period by using a depreciation coefficient
=AMORLINC(cost,date_purchased,f
irst_period,salvage,period,rate,basi
AMORLINC s) Returns the depreciation for each accounting period
=DOLLARDE(fractional_dollar,fracti
DOLLARDE on) Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number
=DOLLARFR(decimal_dollar,fraction
DOLLARFR ) Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction
SLN =SLN(cost,salvage,life) Returns the straight-line depreciation of an asset for one period
SYD =SYD(cost,salvage,life,per) Returns the sum-of-years’ digits depreciation of an asset for a specified period
=DB(cost,salvage,life,period,month
DB ) Returns the depreciation of an asset for a specified period by using the fixed-declining balance method
=DDB(cost,salvage,life,period,facto Returns the depreciation of an asset for a specified period by using the double-declining balance method or
DDB r) some other method that you specify
EFFECT =EFFECT(nominal_rate,npery) Returns the effective annual interest rate
FV =FV(rate,nper,pmt,pv,type) Returns the future value of an investment
IPMT =IPMT(rate,per,nper,pv,fv,type) Returns the interest payment for an investment for a given period
IRR =IRR(values,guess) Returns the internal rate of return for a series of cash flows
=MIRR(values,finance_rate,reinvest
MIRR _rate) Returns the internal rate of return where positive and negative cash flows are financed at different rates
NOMINAL =NOMINAL(effect_rate,npery) Returns the annual nominal interest rate
NPER =NPER(rate,pmt,pv,fv,type) Returns the number of periods for an investment
NPV =NPV(rate,value1,value2,…) Returns the net present value of an investment based on a series of periodic cash flows and a discount rate
PV =PV(rate,nper,pmt,fv,type) Returns the present value of an investment
RATE =RATE(nper,pmt,pv,fv,type,guess) Returns the interest rate per period of an annuity
=YIELD(settlement,maturity,rate,pr
YIELD ,redemption,frequency,basis) Returns the yield on a security that pays periodic interest
=ACCRINT(issue,first_interest,settle
ment,rate,par,frequency,basis,calc
ACCRINT _method) Returns the accrued interest for a security that pays periodic interest
=ACCRINTM(issue,settlement,rate,
ACCRINTM par,basis) Returns the accrued interest for a security that pays interest at maturity
=COUPDAYBS(settlement,maturity,
COUPDAYBS frequency,basis) Returns the number of days from the beginning of the coupon period to the settlement date
=COUPDAYS(settlement,maturity,fr
COUPDAYS equency,basis) Returns the number of days in the coupon period that contains the settlement date
=COUPDAYSNC(settlement,maturit
COUPDAYSNC y,frequency,basis) Returns the number of days from the settlement date to the next coupon date
=COUPNCD(settlement,maturity,fr
COUPNCD equency,basis) Returns the next coupon date after the settlement date
=COUPNUM(settlement,maturity,fr
COUPNUM equency,basis) Returns the number of coupons payable between the settlement date and maturity date
=COUPPCD(settlement,maturity,fre
COUPPCD quency,basis) Returns the previous coupon date before the settlement date
=CUMIPMT(rate,nper,pv,start_peri
CUMIPMT od,end_period,type) Returns the cumulative interest paid between two periods
=CUMPRINC(rate,nper,pv,start_per
CUMPRINC iod,end_period,type) Returns the cumulative principal paid on a loan between two periods
=DISC(settlement,maturity,pr,rede
DISC mption,basis) Returns the discount rate for a security
=DURATION(settlement,maturity,c
DURATION oupon,yld,frequency,basis) Returns the annual duration of a security with periodic interest payments
FVSCHEDULE =FVSCHEDULE(principal,schedule) Returns the future value of an initial principal after applying a series of compound interest rates
=INTRATE(settlement,maturity,inve
INTRATE stment,redemption,basis) Returns the interest rate for a fully invested security
ISPMT =ISPMT(rate,per,nper,pv) Calculates the interest paid during a specific period of an investment
=MDURATION(settlement,maturity
MDURATION ,coupon,yld,frequency,basis) Returns the Macauley modified duration for a security with an assumed par value of $100
=ODDFPRICE(settlement,maturity,i
ssue,first_coupon,rate,yld,redempt
ODDFPRICE ion,frequency,basis) Returns the price per $100 face value of a security with an odd first period
=ODDFYIELD(settlement,maturity,i
ssue,first_coupon,rate,pr,redempti
ODDFYIELD on,frequency,basis) Returns the yield of a security with an odd first period
=ODDLPRICE(settlement,maturity,l
ast_interest,rate,yld,redemption,fr
ODDLPRICE equency,basis) Returns the price per $100 face value of a security with an odd last period
=ODDLYIELD(settlement,maturity,l
ast_interest,rate,pr,redemption,fre
ODDLYIELD quency,basis) Returns the yield of a security with an odd last period
PMT =PMT(rate,nper,pv,fv,type) Returns the periodic payment for an annuity
PPMT =PPMT(rate,per,nper,pv,fv,type) Returns the payment on the principal for an investment for a given period
=PRICE(settlement,maturity,rate,yl
PRICE d,redemption,frequency,basis) Returns the price per $100 face value of a security that pays periodic interest
=PRICEDISC(settlement,maturity,di
PRICEDISC scount,redemption,basis) Returns the price per $100 face value of a discounted security
=PRICEMAT(settlement,maturity,is
PRICEMAT sue,rate,yld,basis) Returns the price per $100 face value of a security that pays interest at maturity
=RECEIVED(settlement,maturity,inv
RECEIVED estment,discount,basis) Returns the amount received at maturity for a fully invested security
=TBILLEQ(settlement,maturity,disc
TBILLEQ ount) Returns the bond-equivalent yield for a Treasury bill
=TBILLPRICE(settlement,maturity,di
TBILLPRICE scount) Returns the price per $100 face value for a Treasury bill
=TBILLYIELD(settlement,maturity,p
TBILLYIELD r) Returns the yield for a Treasury bill
=VDB(cost,salvage,life,start_period
VDB ,end_period,factor,no_switch) Returns the depreciation of an asset for a specified or partial period by using a declining balance method
XIRR =XIRR(values,dates,guess) Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic
XNPV =XNPV(rate,values,dates) Returns the net present value for a schedule of cash flows that is not necessarily periodic
=YIELDDISC(settlement,maturity,pr
YIELDDISC ,redemption,basis) Returns the annual yield for a discounted security; for example, a Treasury bill
=YIELDMAT(settlement,maturity,iss
YIELDMAT ue,rate,pr,basis) Returns the annual yield of a security that pays interest at maturity
Functions Excel Formulas Description
CELL =CELL(info_type, [reference]) Returns information about the formatting, location, or contents of a cell
ISBLANK =ISBLANK(value) Returns TRUE if the value is blank
ISERROR =ISERROR(value) Returns TRUE if the value is any error value
ISNONTEXT =ISNONTEXT(value) Returns TRUE if the value is not text
ISNUMBER =ISNUMBER(value) Returns TRUE if the value is a number
ISTEXT =ISTEXT(value) Returns TRUE if the value is text
ERROR.TYPE =ERROR.TYPE(error_val) Returns a number corresponding to an error type
INFO =INFO(type_text) Returns information about the current operating environment
ISERR =ISERR(value) Returns TRUE if the value is any error value except #N/A
ISEVEN =ISEVEN(number) Returns TRUE if the number is even
ISLOGICAL =ISLOGICAL(value) Returns TRUE if the value is a logical value
ISNA =ISNA(value) Returns TRUE if the value is the #N/A error value
ISODD =ISODD(number) Returns TRUE if the number is odd
ISREF =ISREF(value) Returns TRUE if the value is a reference
N =N(value) Returns a value converted to a number
NA =NA() Returns the error value #N/A
TYPE =TYPE(value) Returns a number indicating the data type of a value
Functions Excel Formulas Description
AND =AND(logical1,logical2,…) Returns TRUE if all of its arguments are TRUE
FALSE =FALSE Returns the logical value FALSE
=IF(logical_test, [value_if_true],
IF [value_if_false]) Specifies a logical test to perform
IFERROR =IFERROR(value, value_if_error) Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
NOT =NOT(logical) Reverses the logic of its argument
OR =OR(logical1,logical2,…) Returns TRUE if any argument is TRUE
TRUE =TRUE Returns the logical value TRUE
=LOOKUP(lookup_value, array)– 2
LOOKUP types Looks up values in a vector or array
Functions Excel Formulas Description
=ADDRESS(row_num,
column_num, [abs_num], [a1],
ADDRESS [sheet_text]) Returns a reference as text to a single cell in a worksheet
COLUMN =COLUMN([reference]) Returns the column number of a reference
COLUMNS =COLUMNS(array) Returns the number of columns in a reference
=HLOOKUP(lookup_value,table_arr
ay,row_index_num,[range_lookup]
HLOOKUP ) Looks in the top row of an array and returns the value of the indicated cell
=INDEX(array,row_num,[column_n
INDEX um])– 2 types Uses an index to choose a value from a reference or array
INDIRECT =INDIRECT(ref_text,a1) Returns a reference indicated by a text value
=MATCH(lookup_value,lookup_arr
MATCH ay,match_type) Looks up values in a reference or array
=OFFSET(reference,rows,cols,heigh
OFFSET t,width) Returns a reference offset from a given reference
ROW =ROW([reference]) Returns the row number of a reference
ROWS =ROWS(array) Returns the number of rows in a reference
=VLOOKUP(lookup_value,table_arr
VLOOKUP ay,col_index_num,[range_lookup]) Looks in the first column of an array and moves across the row to return the value of a cell
=CHOOSE(index_num,value1,value
CHOOSE 2,…) Chooses a value from a list of values
=GETPIVOTDATA(data_field,pivot_t
GETPIVOTDATA able,field,item,…) Returns data stored in a PivotTable report
=HYPERLINK(link_location,friendly_
HYPERLINK name) Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet
TRANSPOSE =TRANSPOSE(array) Returns the transpose of an array
AREAS =AREAS(reference) Returns the number of areas in a reference
Retrieves real-time data from a program that supports COM automation (Automation: A way to work with an
=RTD(progID,server,topic1,topic2, application’s objects from another application or development tool. Formerly called OLE Automation,
RTD …) Automation is an industry-standard and a feature of the Component Object Model (COM).)
Functions Excel Formulas Description
ABS =ABS(number) Returns the absolute value of a number
PRODUCT =PRODUCT(number1,number2,…) Multiplies its arguments
RAND =RAND() Returns a random number between 0 and 1
RANDBETWEEN =RANDBETWEEN(bottom,top) Returns a random number between the numbers you specify
ROUND =ROUND(number,num_digits) Rounds a number to a specified number of digits
=ROUNDDOWN(number,num_digit
ROUNDDOWN s) Rounds a number down, toward zero
ROUNDUP =ROUNDUP(number,num_digits) Rounds a number up, away from zero
SUBTOTAL =SUBTOTAL(function_num,ref1,…) Returns a subtotal in a list or database
SUM =SUM(number1,number2,…) Adds its arguments
=SUMIF(range,criteria,[sum_range]
SUMIF ) Adds the cells specified by a given criteria
=SUMIFS(sum_range,criteria_range
SUMIFS ,criteria,…) Adds the cells in a range that meet multiple criteria
=SUMPRODUCT(array1,array2,[arra
SUMPRODUCT y3],…) Returns the sum of the products of corresponding array components
CEILING =CEILING(number,significance) Rounds a number to the nearest integer or to the nearest multiple of significance
=CEILING.PRECISE(number,significa Rounds a number the nearest integer or to the nearest multiple of significance. Regardless of the sign of the
CEILING.PRECISE nce) number, the number is rounded up.
EVEN =EVEN(number) Rounds a number up to the nearest even integer
EXP =EXP(number) Returns e raised to the power of a given number
FACT =FACT(number) Returns the factorial of a number
=FLOOR.PRECISE(number,significan Rounds a number the nearest integer or to the nearest multiple of significance. Regardless of the sign of the
FLOOR.PRECISE ce) number, the number is rounded up.
GCD =GCD(number1,number2,…) Returns the greatest common divisor
INT =INT(number) Rounds a number down to the nearest integer
ISO.CEILING =ISO.CEILING(number,significance) Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance
LCM =LCM(number1,number2,…) Returns the least common multiple
MOD =MOD(number,divisor) Returns the remainder from division
MROUND =MROUND(number,multiple) Returns a number rounded to the desired multiple
ODD =ODD(number) Rounds a number up to the nearest odd integer
PI =PI() Returns the value of pi
POWER =POWER(number,power) Returns the result of a number raised to a power
=QUOTIENT(numerator,denominat
QUOTIENT or) Returns the integer portion of a division
SERIESSUM =SERIESSUM(x,n,m,coefficients) Returns the sum of a power series based on the formula
SIGN =SIGN(number) Returns the sign of a number
SQRT =SQRT(number) Returns a positive square root
SUMSQ =SUMSQ(number1,number2,…) Returns the sum of the squares of the arguments
TRUNC =TRUNC(number,num_digits) Truncates a number to an integer
=AGGREGATE(function_num,optio
AGGREGATE ns,array,k) Returns an aggregate in a list or database
=COMBIN(number,number_chosen
COMBIN ) Returns the number of combinations for a given number of objects
COS =COS(number) Returns the cosine of a number
COSH =COSH(number) Returns the hyperbolic cosine of a number
FACTDOUBLE =FACTDOUBLE(number) Returns the double factorial of a number
LN =LN(number) Returns the natural logarithm of a number
LOG =LOG(number,base) Returns the logarithm of a number to a specified base
LOG10 =LOG10(number) Returns the base-10 logarithm of a number
=MULTINOMIAL(number1,number
MULTINOMIAL 2,…) Returns the multinomial of a set of numbers
SIN =SIN(number) Returns the sine of the given angle
SINH =SINH(number) Returns the hyperbolic sine of a number
SUMX2MY2 =SUMX2MY2(array_x,array_y) Returns the sum of the difference of squares of corresponding values in two arrays
SUMX2PY2 =SUMX2PY2(array_x,array_y) Returns the sum of the sum of squares of corresponding values in two arrays
SUMXMY2 =SUMXMY2(array_x,array_y) Returns the sum of squares of differences of corresponding values in two arrays
TAN =TAN(number) Returns the tangent of a number
TANH =TANH(number) Returns the hyperbolic tangent of a number
ACOS =ACOS(number) Returns the arccosine of a number
ACOSH =ACOSH(number) Returns the inverse hyperbolic cosine of a number
ASIN =ASIN(number) Returns the arcsine of a number
ASINH =ASINH(number) Returns the inverse hyperbolic sine of a number
ATAN =ATAN(number) Returns the arctangent of a number
ATAN2 =ATAN2(x_num,y_num) Returns the arctangent from x- and y-coordinates
ATANH =ATANH(number) Returns the inverse hyperbolic tangent of a number
DEGREES =DEGREES(angle) Converts radians to degrees
MDETERM =MDETERM(array) Returns the matrix determinant of an array
MINVERSE =MINVERSE(array) Returns the matrix inverse of an array
MMULT =MMULT(array1,array2) Returns the matrix product of two arrays
RADIANS =RADIANS(angle) Converts degrees to radians
ROMAN =ROMAN(number,form) Converts an arabic numeral to roman, as text
SQRTPI =SQRTPI(number) Returns the square root of (number * pi)
Functions Excel Formulas Description
=FIND(find_text,within_text,start_
FIND num) Finds one text value within another (case-sensitive)
LEFT =LEFT(text,num_chars) Returns the leftmost characters from a text value
LEN =LEN(text) Returns the number of characters in a text string
MID =MID(text,start_num,num_chars) Returns a specific number of characters from a text string starting at the position you specify
=REPLACE(old_text,start_num,num
REPLACE _chars,new_text) Replaces characters within text
RIGHT =RIGHT(text,num_chars) Returns the rightmost characters from a text value
=SEARCH(find_text,within_text,star
SEARCH t_num) Finds one text value within another (not case-sensitive)
Functions Excel Formulas Description
AVERAGE =AVERAGE(number1,number2,…) Returns the average of its arguments
=AVERAGEIF(range,criteria,[averag
AVERAGEIF e_range]) Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria
COUNT =COUNT(value1,value2,…) Counts how many numbers are in the list of arguments
COUNTA =COUNTA(value1,value2,…) Counts how many values are in the list of arguments
COUNTBLANK =COUNTBLANK(range) Counts the number of blank cells within a range
COUNTIF =COUNTIF(range,criteria) Counts the number of cells within a range that meet the given criteria
=COUNTIFS(criteria_range,criteria,
COUNTIFS …) Counts the number of cells within a range that meet multiple criteria
MAX =MAX(number1,number2,…) Returns the maximum value in a list of arguments
MEDIAN =MEDIAN(number1,number2,…) Returns the median of the given numbers
MIN =MIN(number1,number2,…) Returns the minimum value in a list of arguments
TEXT =TEXT(value,format_text) Formats a number and converts it to text
AVERAGEA =AVERAGEA(value1,value2,…) Returns the average of its arguments, including numbers, text, and logical values
=AVERAGEIFS(average_range,criter
AVERAGEIFS ia_range,criteria,…) Returns the average (arithmetic mean) of all cells that meet multiple criteria
GEOMEAN =GEOMEAN(number1,number2,…) Returns the geometric mean
INTERCEPT =INTERCEPT(known_y’s,known_x’s) Returns the intercept of the linear regression line
LARGE =LARGE(array,k) Returns the k-th largest value in a data set
=LINEST(known_y’s,known_x’s,con
LINEST st,stats) Returns the parameters of a linear trend
=LOGEST(known_y’s,known_x’s,co
LOGEST nst,stats) Returns the parameters of an exponential trend
MAXA =MAXA(value1,value2,…) Returns the maximum value in a list of arguments, including numbers, text, and logical values
MINA =MINA(value1,value2,…) Returns the smallest value in a list of arguments, including numbers, text, and logical values
=MODE.MULT(number1,number2,
MODE.MULT …) Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data
=MODE.SNGL(number1,number2,…
MODE.SNGL ) Returns the most common value in a data set
=PROB(x_range,prob_range,lower_
PROB limit,upper_limit) Returns the probability that values in a range are between two limits
RANK.AVG =RANK.AVG(number,ref,order) Returns the rank of a number in a list of numbers
RANK.EQ =RANK.EQ(number,ref,order) Returns the rank of a number in a list of numbers
SKEW =SKEW(number1,number2,…) Returns the skewness of a distribution
SLOPE =SLOPE(known_y’s,known_x’s) Returns the slope of the linear regression line
SMALL =SMALL(array,k) Returns the k-th smallest value in a data set
=STANDARDIZE(x,mean,standard_d
STANDARDIZE ev) Returns a normalized value
=TREND(known_y’s,known_x’s,new
TREND _x’s,const) Returns values along a linear trend
NORM.S.INV =NORM.S.INV(probability) Returns the inverse of the standard normal cumulative distribution
AVEDEV =AVEDEV(number1,number2,…) Returns the average of the absolute deviations of data points from their mean
=BETA.DIST(x,alpha,beta,cumulativ
BETA.DIST e,A,B) Returns the beta cumulative distribution function
=BETA.INV(probability,alpha,beta,A
BETA.INV ,B) Returns the inverse of the cumulative distribution function for a specified beta distribution
=BINOM.DIST(number_s,trials,prob
BINOM.DIST ability_s,cumulative) Returns the individual term binomial distribution probability
=BINOM.INV(trials,probability_s,alp Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion
BINOM.INV ha) value
=CHISQ.DIST(x,deg_freedom,cumul
CHISQ.DIST ative) Returns the cumulative beta probability density function
CHISQ.DIST.RT =CHISQ.DIST.RT(x,deg_freedom) Returns the one-tailed probability of the chi-squared distribution
=CHISQ.INV(probability,deg_freedo
CHISQ.INV m) Returns the cumulative beta probability density function
=CHISQ.INV.RT(probability,deg_fre
CHISQ.INV.RT edom) Returns the inverse of the one-tailed probability of the chi-squared distribution
=CHISQ.TEST(actual_range,expecte
CHISQ.TEST d_range) Returns the test for independence
=CONFIDENCE.NORM(alpha,standa
CONFIDENCE.NORM rd_dev,size) Returns the confidence interval for a population mean
=CONFIDENCE.T(alpha,standard_de
CONFIDENCE.T v,size) Returns the confidence interval for a population mean, using a Student’s t distribution
CORREL =CORREL(array1,array2) Returns the correlation coefficient between two data sets
COVARIANCE.P =COVARIANCE.P(array1,array2) Returns covariance, the average of the products of paired deviations
COVARIANCE.S =COVARIANCE.S(array1,array2) Returns the sample covariance, the average of the products deviations for each data point pair in two data sets
DEVSQ =DEVSQ(number1,number2,…) Returns the sum of squares of deviations
EXPON.DIST =EXPON.DIST(x,lambda,cumulative) Returns the exponential distribution
=F.DIST(x,deg_freedom1,deg_freed
F.DIST om2,cumulative) Returns the F probability distribution
=F.DIST.RT(x,deg_freedom1,deg_fr
F.DIST.RT eedom2) Returns the F probability distribution
=F.INV(probability,deg_freedom1,d
F.INV eg_freedom2) Returns the inverse of the F probability distribution
=F.INV.RT(probability,deg_freedom
F.INV.RT 1,deg_freedom2) Returns the inverse of the F probability distribution
F.TEST =F.TEST(array1,array2) Returns the result of an F-test
FISHER =FISHER(x) Returns the Fisher transformation
FISHERINV =FISHERINV(y) Returns the inverse of the Fisher transformation
=FREQUENCY(data_array,bins_arra
FREQUENCY y) Returns a frequency distribution as a vertical array
=GAMMA.DIST(x,alpha,beta,cumul
GAMMA.DIST ative) Returns the gamma distribution
=GAMMA.INV(probability,alpha,bet
GAMMA.INV a) Returns the inverse of the gamma cumulative distribution
GAMMALN =GAMMALN(x) Returns the natural logarithm of the gamma function, G(x)
GAMMALN.PRECISE =GAMMALN.PRECISE(x) Returns the natural logarithm of the gamma function, G(x)
=GROWTH(known_y’s,known_x’s,n
GROWTH ew_x’s,const) Returns values along an exponential trend
HARMEAN =HARMEAN(number1,number2,…) Returns the harmonic mean
=HYPGEOM.DIST(sample_s,number
_sample,population_s,number_po
HYPGEOM.DIST p,cumulative) Returns the hypergeometric distribution
KURT =KURT(number1,number2,…) Returns the kurtosis of a data set
=LOGNORM.DIST(x,mean,standard
LOGNORM.DIST _dev,cumulative) Returns the cumulative lognormal distribution
=LOGNORM.INV(probability,mean,
LOGNORM.INV standard_dev) Returns the inverse of the lognormal cumulative distribution
=NEGBINOM.DIST(number_f,numb
NEGBINOM.DIST er_s,probability_s,cumulative) Returns the negative binomial distribution
=NORM.DIST(x,mean,standard_dev
NORM.DIST ,cumulative) Returns the normal cumulative distribution
=NORM.INV(probability,mean,stan
NORM.INV dard_dev) Returns the inverse of the normal cumulative distribution
NORM.S.DIST =NORM.S.DIST(z,cumulative) Returns the standard normal cumulative distribution
PEARSON =PEARSON(array1,array2) Returns the Pearson product moment correlation coefficient
PERCENTILE.EXC =PERCENTILE.EXC(array,k) Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive
PERCENTILE.INC =PERCENTILE.INC(array,k) Returns the k-th percentile of values in a range
=PERCENTRANK.EXC(array,x,signific
PERCENTRANK.EXC ance) Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set
=PERCENTRANK.INC(array,x,signific
PERCENTRANK.INC ance) Returns the percentage rank of a value in a data set
=PERMUT(number,number_chosen
PERMUT ) Returns the number of permutations for a given number of objects
=POISSON.DIST(x,mean,cumulative
POISSON.DIST ) Returns the Poisson distribution
QUARTILE.EXC =QUARTILE.EXC(array,quart) Returns the quartile of the data set, based on percentile values from 0..1, exclusive
QUARTILE.INC =QUARTILE.INC(array,quart) Returns the quartile of a data set
RSQ =RSQ(known_y’s,known_x’s) Returns the square of the Pearson product moment correlation coefficient
STDEV.P =STDEV.P(number1,number2,…) Calculates standard deviation based on the entire population
STDEV.S =STDEV.S(number1,number2,…) Estimates standard deviation based on a sample
STDEVA =STDEVA(value1,value2,…) Estimates standard deviation based on a sample, including numbers, text, and logical values
STDEVPA =STDEVPA(value1,value2,…) Calculates standard deviation based on the entire population, including numbers, text, and logical values
STEYX =STEYX(known_y’s,known_x’s) Returns the standard error of the predicted y-value for each x in the regression
=T.DIST(x,deg_freedom,cumulative
T.DIST ) Returns the Percentage Points (probability) for the Student t-distribution
T.DIST.2T =T.DIST.2T(x,deg_freedom) Returns the Percentage Points (probability) for the Student t-distribution
T.DIST.RT =T.DIST.RT(x,deg_freedom) Returns the Student’s t-distribution
T.INV =T.INV(probability,deg_freedom) Returns the t-value of the Student’s t-distribution as a function of the probability and the degrees of freedom
=T.INV.2T(probability,deg_freedom
T.INV.2T ) Returns the inverse of the Student’s t-distribution
T.TEST =T.TEST(array1,array2,tails,type) Returns the probability associated with a Student’s t-test
TRIMMEAN =TRIMMEAN(array,percent) Returns the mean of the interior of a data set
VAR.P =VAR.P(number1,number2,…) Calculates variance based on the entire population
VAR.S =VAR.S(number1,number2,…) Estimates variance based on a sample
VARA =VARA(value1,value2,…) Estimates variance based on a sample, including numbers, text, and logical values
VARPA =VARPA(value1,value2,…) Calculates variance based on the entire population, including numbers, text, and logical values
=WEIBULL.DIST(x,alpha,beta,cumul
WEIBULL.DIST ative) Returns the Weibull distribution
Z.TEST =Z.TEST(array,x,sigma) Returns the one-tailed probability-value of a z-test
Functions Excel Formulas Description
EXACT =EXACT(text1,text2) Checks to see if two text values are identical
LOWER =LOWER(text) Converts text to lowercase
PROPER =PROPER(text) Capitalizes the first letter in each word of a text value
TRIM =TRIM(text) Removes spaces from text
UPPER =UPPER(text) Converts text to uppercase
CHAR =CHAR(number) Returns the character specified by the code number
CLEAN =CLEAN(text) Removes all nonprintable characters from text
CODE =CODE(text) Returns a numeric code for the first character in a text string
DOLLAR =DOLLAR(number,decimals) Converts a number to text, using the $ (dollar) currency format
=FIXED(number,decimals,no_com
FIXED mas) Formats a number as text with a fixed number of decimals
PHONETIC =PHONETIC(reference) Extracts the phonetic (furigana) characters from a text string
REPT =REPT(text,number_times) Repeats text a given number of times
=SUBSTITUTE(text,old_text,new_te
SUBSTITUTE xt,instance_num) Substitutes new text for old text in a text string
T =T(value) Converts its arguments to text
VALUE =VALUE(text) Converts a text argument to a number
Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte)
ASC =ASC(text) characters
BAHTTEXT =BAHTTEXT(number) Converts a number to text, using the ß (baht) currency format