Compatibility Excel Formulas &
Functions
Functions Excel Formulas Description
m
Joins several text
items into one text
CONCATEN =CONCATENATE(text1,text2,
item. Easier to use ‘&’
co
ATE …)
instead of the
function usually.
FLOOR k. =FLOOR(number,significance)
Rounds a number
down, toward zero
oo
Returns the individual
=BINOMDIST(number_s,trials, term binomial
BINOMDIST
tb
probability_s,cumulative) distribution
probability
no
Returns the
one-tailed probability
CHIDIST =CHIDIST(x,deg_freedom)
of the chi-squared
pa
distribution
=CHITEST(actual_range,expe Returns the test for
CHIINV
cted_range) independence
Returns the
CONFIDENC =CONFIDENCE(alpha,standar
confidence interval
E d_dev,size)
for a population mean
FTEST =FTEST(array1,array2)
Returns the inverse of
=LOGINV(probability,mean,st the lognormal
m
LOGINV
andard_dev) cumulative
distribution
co
Returns the
LOGNORMD =LOGNORMDIST(x,mean,sta
cumulative lognormal
IST ndard_dev)
k. distribution
Returns the most
oo
MODE =MODE(number1,number2,…) common value in a
data set
tb
Returns the normal
=NORMDIST(x,mean,standar
NORMDIST cumulative
d_dev,cumulative)
distribution
no
Returns the inverse of
=NORMINV(probability,mean, the normal
pa
NORMINV
standard_dev) cumulative
distribution
Returns the standard
NORMSDIST =NORMSDIST(z) normal cumulative
distribution
Returns the inverse of
the standard normal
NORMSINV =NORMSINV(probability)
cumulative
distribution
Returns the k-th
PERCENTILE =PERCENTILE(array,k) percentile of values in
m
a range
Returns the
PERCENTRA =PERCENTRANK(array,x,signi
co
percentage rank of a
NK ficance)
value in a data set
POISSON
k. =POISSON(x,mean,cumulativ
e)
Returns the Poisson
distribution
oo
Returns the quartile
QUARTILE =QUARTILE(array,quart)
of a data set
tb
Returns the rank of a
RANK =RANK(number,ref,order) number in a list of
numbers
no
Estimates standard
=STDEV(number1,number2,…
STDEV deviation based on a
pa
)
sample
Calculates standard
=STDEVP(number1,number2,
STDEVP deviation based on
…)
the entire population
Returns the Student’s
TDIST =TDIST(x,deg_freedom,tails)
t-distribution
Returns the inverse of
=TINV(probability,deg_freedo
TINV the Student’s
m)
t-distribution
Estimates variance
m
VAR =VAR(number1,number2,…)
based on a sample
co
Calculates variance
VARP =VARP(number1,number2,…) based on the entire
population
FINV
k. =FINV(probability,deg_freedo
Returns the inverse of
the F probability
oo
m1,deg_freedom2)
distribution
=FORECAST(x,known_y’s,kno Returns a value along
FORECAST
tb
wn_x’s) a linear trend
Returns the beta
no
BETADIST =BETADIST(x,alpha,beta,A,B) cumulative
distribution function
pa
Returns the inverse of
the cumulative
=BETAINV(probability,alpha,b
BETAINV distribution function
eta,A,B)
for a specified beta
distribution
Returns covariance,
the average of the
COVAR =COVAR(array1,array2)
products of paired
deviations
Returns the smallest
value for which the
m
=CRITBINOM(trials,probabilit cumulative binomial
CRITBINOM
y_s,alpha) distribution is less
than or equal to a
co
criterion value
Returns the
k.
EXPONDIST
=EXPONDIST(x,lambda,cumu
lative)
exponential
distribution
oo
=POISSON(x,mean,cumulativ Returns the Poisson
POISSON
e) distribution
tb
Returns the F
=FDIST(x,deg_freedom1,deg_
FDIST probability
freedom2)
no
distribution
GAMMADIS =GAMMADIST(x,alpha,beta,c Returns the gamma
T umulative) distribution
pa
Returns the inverse of
=GAMMAINV(probability,alph the gamma
GAMMAINV
a,beta) cumulative
distribution
=HYPGEOMDIST(sample_s,n Returns the
HYPGEOMD
umber_sample,population_s,n hypergeometric
IST
umber_pop) distribution
NEGBINOM =NEGBINOMDIST(number_f,n Returns the negative
DIST umber_s,probability_s) binomial distribution
Returns the
m
=TTEST(array1,array2,tails,ty probability associated
TTEST
pe) with a Student’s
co
t-test
Calculates variance
WEIBULL k. =WEIBULL(x,alpha,beta,cumu
lative)
based on the entire
population, including
oo
numbers, text, and
logical values
Returns the
tb
one-tailed
ZTEST =ZTEST(array,x,sigma)
probability-value of a
no
z-test
Cube Excel Formulas & Functions
pa
Function
Excel Formulas Description
s
Returns a key
performance indicator
(KPI) name, property,
and measure, and
displays the name and
property in the cell. A
=CUBEKPIMEMBER(connecti
CUBEKPIM KPI is a quantifiable
on,kpi_name,kpi_property,ca
m
EMBER measurement, such as
ption)
monthly gross profit or
co
quarterly employee
turnover, used to
monitor an
k. organization’s
performance.
oo
Returns a member or
tuple in a cube hierarchy.
CUBEMEM =CUBEMEMBER(connection,
Use to validate that the
tb
BER member_expression,caption)
member or tuple exists
in the cube.
no
Returns the value of a
member property in the
cube. Use to validate
pa
CUBEMEM =CUBEMEMBERPROPERTY(
that a member name
BERPROP connection,member_expressi
exists within the cube
ERTY on,property)
and to return the
specified property for
this member.
Returns the nth, or
ranked, member in a set.
CUBERAN =CUBERANKEDMEMBER(co Use to return one or
KEDMEMB nnection,set_expression,rank more elements in a set,
ER ,caption) such as the top sales
performer or top 10
students.
m
Defines a calculated set
of members or tuples by
co
sending a set expression
=CUBESET(connection,set_e
to the cube on the
CUBESET xpression,caption,sort_order,
server, which creates the
k. sort_by)
set, and then returns
that set to Microsoft
oo
Office Excel.
CUBESETC Returns the number of
=CUBESETCOUNT(set)
tb
OUNT items in a set.
CUBEVAL =CUBEVALUE(connection,m Returns an aggregated
no
UE ember_expression1,…) value from a cube
pa
Database Excel Formulas & Functions
Functio Excel
Description
ns Formulas
Extracts from a database a single
=DGET(databa
DGET record that matches the specified
se,field,criteria)
criteria
=DSUM(datab Adds the numbers in the field column
DSUM ase,field,criteri of records in the database that match
a) the criteria
m
=DAVERAGE(d
DAVERAG Returns the average of selected
atabase,field,cr
E database entries
co
iteria)
=DCOUNT(dat
Counts the cells that contain numbers
DCOUNT
k.abase,field,crit
eria)
in a database
oo
=DCOUNTA(d
DCOUNT
atabase,field,cr Counts nonblank cells in a database
A
iteria)
tb
=DMAX(datab
Returns the maximum value from
DMAX ase,field,criteri
no
selected database entries
a)
=DMIN(databa Returns the minimum value from
pa
DMIN
se,field,criteria) selected database entries
=DPRODUCT( Multiplies the values in a particular
DPRODU
database,field, field of records that match the criteria
CT
criteria) in a database
=DSTDEV(data Estimates the standard deviation based
DSTDEV base,field,criter on a sample of selected database
ia) entries
=DSTDEVP(da Calculates the standard deviation
DSTDEVP tabase,field,crit based on the entire population of
eria) selected database entries
m
=DVAR(databa Estimates variance based on a sample
DVAR
se,field,criteria) from selected database entries
co
=DVARP(datab Calculates variance based on the entire
DVARP ase,field,criteri population of selected database
k. a) entries
oo
Date & Time Excel Formulas &
Functions
tb
Function
Excel Formulas Description
no
Returns the serial number
DATE =DATE(year,month,day)
of a particular date
pa
Converts a date in the
DATEVAL
=DATEVALUE(date_text) form of text to a serial
UE
number
Converts a serial number
DAY =DAY(serial_number)
to a day of the month
Converts a serial number
HOUR =HOUR(serial_number)
to an hour
Converts a serial number
MINUTE =MINUTE(serial_number)
to a minute
Converts a serial number
MONTH =MONTH(serial_number)
to a month
m
Returns the serial number
co
NOW =NOW() of the current date and
time
Converts a serial number
SECOND
k. =SECOND(serial_number)
to a second
oo
Returns the serial number
TIME =TIME(hour,minute,second)
of a particular time
Converts a time in the
tb
TIMEVALU
=TIMEVALUE(time_text) form of text to a serial
E
number
no
Returns the serial number
TODAY =TODAY()
of today’s date
pa
Converts a serial number
YEAR =YEAR(serial_number)
to a year
Calculates the number of
=DAYS360(start_date,end_
DAYS360 days between two dates
date,method)
based on a 360-day year
Returns the serial number
of the date that is the
EDATE =EDATE(start_date,months) indicated number of
months before or after
the start date
Returns the serial number
m
of the last day of the
EOMONT =EOMONTH(start_date,mo
month before or after a
H nths)
specified number of
co
months
Returns the number of
NETWOR
KDAYS
k. =NETWORKDAYS(start_da
te,end_date,[holidays])
whole workdays between
two dates
oo
Returns the number of
whole workdays between
NETWOR =NETWORKDAYS.INTL(sta
two dates using
tb
KDAYS.INT rt_date,end_date,[weekend]
parameters to indicate
L ,[holidays])
which and how many
no
days are weekend days
=WEEKDAY(serial_number, Converts a serial number
WEEKDAY
pa
[return_type]) to a day of the week
Converts a serial number
WEEKNU =WEEKNUM(serial_number to a number representing
M ,[return_type]) where the week falls
numerically with a year
Returns the serial number
WORKDA =WORKDAY(start_date, of the date before or after
Y days, [holidays]) a specified number of
workdays
Returns the serial number
of the date before or after
m
a specified number of
WORKDA =WORKDAY.INTL(start_dat
workdays using
Y.INTL e,days,weekend,holidays)
parameters to indicate
co
which and how many
days are weekend days
YEARFRA k. =YEARFRAC(start_date,end
Returns the year fraction
representing the number
oo
C _date,basis) of whole days between
start_date and end_date
tb
Information Excel Formulas &
no
Functions
Function
Excel Formulas Description
pa
Returns information about the
=CELL(info_type,
CELL formatting, location, or contents of
[reference])
a cell
ISBLANK =ISBLANK(value) Returns TRUE if the value is blank
Returns TRUE if the value is any
ISERROR =ISERROR(value)
error value
ISNONTEX =ISNONTEXT(val Returns TRUE if the value is not
T ue) text
ISNUMBE =ISNUMBER(valu Returns TRUE if the value is a
R e) number
m
ISTEXT =ISTEXT(value) Returns TRUE if the value is text
co
ERROR.TY =ERROR.TYPE(err Returns a number corresponding to
PE or_val) an error type
INFO
k. =INFO(type_text)
Returns information about the
current operating environment
oo
Returns TRUE if the value is any
ISERR =ISERR(value)
error value except #N/A
tb
ISEVEN =ISEVEN(number) Returns TRUE if the number is even
ISLOGICA =ISLOGICAL(valu Returns TRUE if the value is a
no
L e) logical value
Returns TRUE if the value is the
ISNA =ISNA(value)
pa
#N/A error value
ISODD =ISODD(number) Returns TRUE if the number is odd
Returns TRUE if the value is a
ISREF =ISREF(value)
reference
Returns a value converted to a
N =N(value)
number
NA =NA() Returns the error value #N/A
Returns a number indicating the
TYPE =TYPE(value)
data type of a value
m
Logical Excel Formulas & Functions
co
Functi
Excel Formulas Description
ons
AND
k.
=AND(logical1,logic Returns TRUE if all of its
oo
al2,…) arguments are TRUE
FALSE =FALSE Returns the logical value FALSE
tb
=IF(logical_test,
IF [value_if_true], Specifies a logical test to perform
no
[value_if_false])
Returns a value you specify if a
IFERRO =IFERROR(value, formula evaluates to an error;
pa
R value_if_error) otherwise, returns the result of the
formula
NOT =NOT(logical) Reverses the logic of its argument
=OR(logical1,logical Returns TRUE if any argument is
OR
2,…) TRUE
TRUE =TRUE Returns the logical value TRUE
LOOKU =LOOKUP(lookup_v
Looks up values in a vector or array
P alue, array)– 2 types
Lookup & Reference Excel Formulas &
m
Functions
co
Funct
Excel Formulas Description
ions
ADDRE
SS
k.
=ADDRESS(row_num,
column_num, [abs_num],
Returns a reference as text
to a single cell in a
oo
[a1], [sheet_text]) worksheet
COLUM Returns the column number
=COLUMN([reference])
N of a reference
tb
COLUM Returns the number of
=COLUMNS(array)
no
NS columns in a reference
=HLOOKUP(lookup_value,t Looks in the top row of an
HLOOK
able_array,row_index_num,[ array and returns the value
pa
UP
range_lookup]) of the indicated cell
Uses an index to choose a
=INDEX(array,row_num,[col
INDEX value from a reference or
umn_num])– 2 types
array
INDIRE Returns a reference indicated
=INDIRECT(ref_text,a1)
CT by a text value
=MATCH(lookup_value,look Looks up values in a
MATCH
up_array,match_type) reference or array
OFFSE =OFFSET(reference,rows,col Returns a reference offset
T s,height,width) from a given reference
m
Returns the row number of a
ROW =ROW([reference])
co
reference
Returns the number of rows
ROWS =ROWS(array)
in a reference
k.
=VLOOKUP(lookup_value,ta
Looks in the first column of
oo
VLOOK an array and moves across
ble_array,col_index_num,[ra
UP the row to return the value
nge_lookup])
of a cell
tb
CHOO =CHOOSE(index_num,value Chooses a value from a list
SE 1,value2,…) of values
no
GETPIV
=GETPIVOTDATA(data_fiel Returns data stored in a
OTDAT
d,pivot_table,field,item,…) PivotTable report
pa
Creates a shortcut or jump
HYPER =HYPERLINK(link_location,f that opens a document
LINK riendly_name) stored on a network server,
an intranet, or the Internet
TRANS Returns the transpose of an
=TRANSPOSE(array)
POSE array
Returns the number of areas
AREAS =AREAS(reference)
in a reference
Retrieves real-time data
from a program that
m
supports COM automation
(Automation: A way to work
co
with an application’s objects
=RTD(progID,server,topic1,t from another application or
RTD
opic2,…) development tool. Formerly
k. called OLE Automation,
Automation is an
oo
industry-standard and a
feature of the Component
Object Model (COM).)
tb
Text Excel Formulas & Functions
no
Function
Excel Formulas Description
s
pa
Checks to see if two text values
EXACT =EXACT(text1,text2)
are identical
LOWER =LOWER(text) Converts text to lowercase
Capitalizes the first letter in each
PROPER =PROPER(text)
word of a text value
TRIM =TRIM(text) Removes spaces from text
UPPER =UPPER(text) Converts text to uppercase
Returns the character specified
CHAR =CHAR(number)
m
by the code number
Removes all nonprintable
co
CLEAN =CLEAN(text)
characters from text
Returns a numeric code for the
CODE =CODE(text)
k. =DOLLAR(number,d
first character in a text string
Converts a number to text, using
oo
DOLLAR
ecimals) the $ (dollar) currency format
=FIXED(number,deci Formats a number as text with a
tb
FIXED
mals,no_commas) fixed number of decimals
PHONETI =PHONETIC(referen Extracts the phonetic (furigana)
no
C ce) characters from a text string
=REPT(text,number_ Repeats text a given number of
REPT
pa
times) times
=SUBSTITUTE(text,
SUBSTITU Substitutes new text for old text
old_text,new_text,in
TE in a text string
stance_num)
T =T(value) Converts its arguments to text
Converts a text argument to a
VALUE =VALUE(text)
number
Changes full-width
(double-byte) English letters or
ASC =ASC(text) katakana within a character
string to half-width (single-byte)
m
characters
BAHTTEX =BAHTTEXT(numbe Converts a number to text, using
co
T r) the ß (baht) currency format
Most Common Excel Formulas &
k.
Functions
oo
Functions Excel Formulas Description
tb
=FIND(find_text,within_te Finds one text value within
FIND
xt,start_num) another (case-sensitive)
no
Returns the leftmost
LEFT =LEFT(text,num_chars) characters from a text
value
pa
Returns the number of
LEN =LEN(text)
characters in a text string
=MID(text,start_num,nu Returns a specific number
MID
m_chars) of characters from a text
string starting at the
position you specify
=REPLACE(old_text,start
Replaces characters within
REPLACE _num,num_chars,new_tex
text
t)
Returns the rightmost
m
RIGHT =RIGHT(text,num_chars) characters from a text
value
co
Finds one text value within
=SEARCH(find_text,withi
SEARCH another (not
n_text,start_num)
k. case-sensitive)
oo
Engineering Excel Formulas &
Functions
tb
Functions Excel Formulas Description
no
Converts a number from one
=CONVERT(number,fro
CONVERT measurement system to
m_unit,to_unit)
another
pa
=DELTA(number1,numb Tests whether two values are
DELTA
er2) equal
=ERF(lower_limit,upper
ERF Returns the error function
_limit)
Returns the complementary
ERFC =ERFC(x)
error function
Tests whether a number is
GESTEP =GESTEP(number,step) greater than a threshold
value
ERF.PRECI
m
=ERF.PRECISE(X) Returns the error function
SE
co
Returns the complementary
ERFC.PREC
=ERFC.PRECISE(X) ERF function integrated
ISE
between x and infinity
BESSELI k. =BESSELI(x,n)
Returns the modified Bessel
function In(x)
oo
Returns the Bessel function
BESSELJ =BESSELJ(x,n)
Jn(x)
tb
Returns the modified Bessel
BESSELK =BESSELK(x,n)
function Kn(x)
no
Returns the Bessel function
BESSELY =BESSELY(x,n)
Yn(x)
pa
Converts a binary number to
BIN2DEC =BIN2DEC(number)
decimal
=BIN2HEX(number,plac Converts a binary number to
BIN2HEX
es) hexadecimal
=DEC2OCT(number,pla Converts a decimal number
DEC2OCT
ces) to octal
=HEX2BIN(number,plac Converts a hexadecimal
HEX2BIN
es) number to binary
Converts a hexadecimal
HEX2DEC =HEX2DEC(number)
number to decimal
m
=HEX2OCT(number,pla Converts a hexadecimal
HEX2OCT
co
ces) number to octal
Returns the absolute value
IMABS =IMABS(inumber) (modulus) of a complex
k. number
oo
Returns the imaginary
IMAGINAR
=IMAGINARY(inumber) coefficient of a complex
Y
number
tb
Returns the argument theta,
IMARGUM =IMARGUMENT(inumb
an angle expressed in
no
ENT er)
radians
Returns the complex
IMCONJUG =IMCONJUGATE(inumb
pa
conjugate of a complex
ATE er)
number
Returns the cosine of a
IMCOS =IMCOS(inumber)
complex number
=IMDIV(inumber1,inum Returns the quotient of two
IMDIV
ber2) complex numbers
Returns the exponential of a
IMEXP =IMEXP(inumber)
complex number
Returns the natural
IMLN =IMLN(inumber) logarithm of a complex
number
Returns the base-10
m
IMLOG10 =IMLOG10(inumber) logarithm of a complex
number
co
Returns the base-2
IMLOG2 =IMLOG2(inumber) logarithm of a complex
k. =IMPOWER(inumber,n
number
Returns a complex number
oo
IMPOWER
umber) raised to an integer power
IMPRODUC =IMPRODUCT(inumber Returns the product of
tb
T 1,inumber2,…) complex numbers
Returns the real coefficient of
no
IMREAL =IMREAL(inumber)
a complex number
Returns the sine of a
IMSIN =IMSIN(inumber)
pa
complex number
Returns the square root of a
IMSQRT =IMSQRT(inumber)
complex number
Returns the difference
=IMSUB(inumber1,inu
IMSUB between two complex
mber2)
numbers
=IMSUM(inumber1,inu Returns the sum of complex
IMSUM
mber2,…) numbers
=OCT2BIN(number,plac Converts an octal number to
OCT2BIN
es) binary
Converts an octal number to
OCT2DEC =OCT2DEC(number)
decimal
m
=OCT2HEX(number,pla Converts an octal number to
OCT2HEX
co
ces) hexadecimal
k.
oo
tb
no
pa
