|
How to Use This Guide
Database Management and List Management
Functions
Date and Time Functions
DDE and External Data Functions
Engineering Functions
Financial Functions
Information Functions
Logical Functions
Lookup and Reference Functions
Math and Trigonometry Functions
Statistical Functions
Text Functions
How
to Use This Guide
This glossary provides a quick reference
summary of the functions available in Excel 2000. For further information and
complete details on using the functions, please refer to the Excel Help
feature.
Database
Management and List Management Functions
General Syntax
In the following sections, a separate syntax
line is included for each database or list function that doesn't conform to the
following general syntax:
=Dfunction(database,field,criteria)
The database argument refers to the range
encompassing the list or database. The field argument refers to a particular column in
the list. If you omit the field argument, the function
operates on the entire list. You can refer to the column number within the
list, the name of the column heading (in quotes), or a cell reference
referring to either the label or the column number. The criteria argument specifies the basis on which
you want the function to select particular cells. The criteria includes a
duplicate list of column headings from the list, with words, phrases,
numeric values, or formulas listed underneath those headings.
DAVERAGE
Indicates the average of the values that
meet the specified criteria.
DCOUNT
Counts the number of cells that contain
numbers that meet the specified criteria.
DCOUNTA
Counts nonblank cells containing numbers or
text that meet the specified criteria.
DGET
Returns a single value that meets the
specified criteria. If duplicates are found, DGET returns #NUM.
DMAX
Extracts the highest value that meets the
specified criteria.
DMIN
Extracts the lowest value that meets the
specified criteria.
DPRODUCT
Returns the product of multiplying the
values that meet the specified criteria.
DSTDEV
Returns the calculation of the standard
deviation of a population, based on the sum of the whole population.
DSUM
Returns the total of the values that meet
the specified criteria.
DVAR
Estimates the variance of a sample
population, based on the values that meet the specified criteria.
DVARP
Returns the calculation of the variance of
an entire population, based on the values that meet the specified criteria.
GETPIVOTDATA
Returns a value of data stored in a
PivotTable. The syntax is as follows:
=GETPIVOTDATA(PivotTable,name)
Date
and Time Functions
DATE
Returns the DATEVALUE serial number.
=DATE(year,month,day)
DATEVALUE
Converts date text to a DATEVALUE serial
number.
=DATEVALUE(date_text)
DAY
Returns the corresponding day of the month
serial number, from 1 to 31.
=DAY(serial_number)
DAYS360
Returns the number of days between dates
that you specify, based on a 360-day year (twelve 30 day months).
=DAYS360(start_date,end_date)
EDATE
Returns the value or serial number of the
date specified by you and the number of months before or after the specified
date. Use EDATE to calculate the maturity date or date due that falls on the
same day of the month as the date of issue.
=EDATE(start_date,months)
EOMONTH
Returns the serial number for the last day
of the month (as specified by months) before or after start_date.
Used to calculate maturity dates or dates due that fall on the last day of the
month.
=EOMonth(start_date,months)
HOUR
Returns the hour as a serial number integer
between 0 (12:00 A.M.) and 23 (11:00 P.M.).
=HOUR(serial_number)
MINUTE
Returns the serial number integer from 0 to
59 that corresponds to the minute.
=MINUTE(serial_number)
MONTH
Returns the corresponding serial number of
the month between 1 and 12.
=MONTH(serial_number)
NETWORKDAYS
Returns the number of working days between two
dates. Excludes weekends and specified holidays.
=NETWORKDAYS(start_date,end_date,holidays)
NOW
Returns the current date and time in the
form of a serial number. When entered in a cell, Excel formats the number as a
date and time. There are no arguments for this function.
=NOW()
SECOND
Returns the corresponding serial number of
seconds as an integer between 0 and 59.
=SECOND(serial_number)
TIME
Returns the corresponding serial number of
time as a decimal between 0 and 0.99999999.
=TIME(hour,minute,second)
TIMEVALUE
Returns the serial number represented by
text as time.
=TIMEVALUE(time_text)
TODAY
Returns the current date as a serial number.
When entered in a cell, Excel formats the number as a date. There are no
arguments for this function.
=TODAY()
WEEKDAY
Returns the corresponding day of the week
(from 1 to 7) as a serial number.
=WEEKDAY(serial_number,return_type)
WORKDAY
Returns a number representing the date that
is the number of days (specified by days) before or after start_date.
You can exclude dates with the holidays argument.
=WORKDAY(start_date,days,holidays).
YEAR
Returns the corresponding year as a serial
number in the form of an integer from 19009999.
=YEAR(serial_number)
YEARFRAC
Returns the calculated fraction of the year
represented by whole numbers between two dates.
=YEARFRAC(start_date,end_date,basis)
DDE
and External Data Functions
CALL
Calls up the procedure in a dynamic link
library or code resource.
Syntax 1, with register:
=CALL(register_ID,argument1,...)
Syntax 2, used alone:
=CALL(module_text,procedure,type_text,argument1,...)
REGISTER.ID
Supplies the registered ID of the dynamic
link library or code resource.
=REGISTER.ID(module_text,procedure,type_text)
SQL.REQUEST
Runs a query from a worksheet and connects
an external data source.
=SQL.REQUEST(connection_string,output_ref,driver_prompt,query_text,col_names_logical)
Engineering
Functions
NOTE
You must have the
Analysis ToolPak installed and enabled to use (see) these functions.
BESSELI
Returns the Bessel function in modified form
for imaginary arguments.
=BESSELI(x,n)
BESSELJ
Returns the actual Bessel function, where x
is the value at which to evaluate the function, and n is the
order of the Bessel function.
=BESSELJ(x,n)
BESSELK
Returns the Bessel function in modified form
for imaginary arguments.
=BESSELK(x,n)
BESSELY
Returns the Bessel function, also known as
the Weber or Neumann function, where x is the value at which to
evaluate the function, and n is the order of the function.
=BESSELY(x,n)
BIN2DEC
Converts a binary number to decimal form.
=BIN2DEC(number)
BIN2HEX
Converts a binary number to hexadecimal.
=BIN2HEX(number,places)
BIN2OCT
Converts a binary number to octal.
=BIN2OCT(number,places)
COMPLEX
Converts real and imaginary coefficients
into a complex number of the form x+yi or x+yj.
=COMPLEX(real_num,I_num,suffix)
CONVERT
Converts from one measurement system to
another.
=CONVERT(number,from_unit,to_unit)
DEC2BIN
Converts decimal numbers to binary.
=DEC2BIN(number,places)
DEC2HEX
Converts decimal numbers to hexadecimal.
=DEC2HEX(number,places)
DEC2OCT
Converts decimal numbers to octal.
=DEC2OCT(number,places)
DELTA
Tests whether numbers or values are equal.
=DELTA(number1,number2)
ERF
Returns the integrated error function
between lower_limit and upper_limit.
=ERF(lower_limit,upper_limit)
ERFC
Returns a complementary ERF function
integrated between x and infinity, where x is the lower
bound for integrating ERF.
=ERF(x)
GESTEP
Returns 1 if number is greater
than or equal to a specified step or threshold, otherwise returns
0.
=GESTEP(number,step)
HEX2BIN
Converts hexadecimal numbers to binary.
=HEX2BIN(number,places)
HEX2DEC
Converts hexadecimal numbers to decimal.
=HEX2DEC(number)
HEX2OCT
Converts hexadecimal numbers to octal.
=HEX2OCT(number)
IMABS
Returns the absolute value (modulus) of a
complex number in x+yi or x+yj text format. The formula takes the square root
of (x2+y2).
=IMABS(inumber)
IMAGINARY
Returns the coefficient of a complex number
in x+yi or x+yj in text format.
=IMAGINARY(inumber)
IMARGUMENT
Returns the theta argument and angle
expressed in radians.
=IMARGUMENT(inumber)
IMCONJUGATE
Returns the complex conjugate of a complex
number in x+yi or x+yj text format.
=IMCONJUGATE(inumber)
IMCOS
Returns the cosine of a complex number x+yi
or x+yj in text format.
=IMCOS(numbers)
IMDIV
Returns the quotient of complex numbers x+yi
or x+yj in text format.
=IMDIV(number1,number2)
IMEXP
Returns the exponential of complex numbers
x+yi or x+yj in text format.
=IMEXP(inumber)
IMLN
Returns the natural logarithm of complex
numbers x+yi or x+yj in text format.
=IMLN(inumber)
IMLOG10
Returns the common logarithm (base 10) of
complex numbers x+yi or x+yj in text format.
=IMLOG10(inumber)
IMLOG2
Returns the base 2 logarithm of complex
numbers in x+yi or x+yj in text format.
=IMLOG2(inumber)
IMPOWER
Returns a complex number raised to a power
in x+yi or x+yj text format.
=IMPOWER(inumber,number)
IMPRODUCT
Returns the product from 2 to 29 in complex
numbers x+yi or x+yj text format.
=IMPRODUCT(inumber1,inumber2,...)
IMREAL
Returns real coefficients of complex numbers
x+yi or x+yj in text format.
=IMREAL(inumber)
IMSIN
Returns the sine of complex numbers x+yi or
x+yj in text format.
=IMSIN(inumber)
IMSQRT
Returns the square root of complex numbers
x+yi or x+yj in text format.
=IMSQRT(inumber)
IMSUB
Returns the difference of two complex
numbers x+yi or x+yj in text format.
=IMSUB(inumber1,inumber2)
IMSUM
Returns the sum of two complex numbers x+yi
or x+yj in text format.
=IMSUM(inumber1,inumber2,...)
OCT2BIN
Converts an octal number to binary.
=OCT2BIN(number,places)
OCT2DEC
Converts an octal number to decimal.
=OCT2DEC(number)
OCT2HEX
Converts an octal number to hexadecimal.
=OCT2HEX(number,places)
SQRTPI
Returns the square root of a positive number
multiplied by Pi. This value cannot be less than zero. (Also found under
"Math and Trigonometry Functions.")
=SQRTPI(number*pi)
Financial
Functions
ACCRINT
Returns accrued interest for security that
pays periodic interest.
=ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis)
ACCRINTM
Returns accrued interest for security that
pays interest at maturity.
=ACCRINTM(issue,maturity,rate,par,basis)
AMORDEGRC
Returns appreciation for each accounting
period.
=AMORDEGRC(cost,date_purchased,first_period,salvage,period,rate,basis)
AMORLINC
Returns depreciation for each accounting
period.
=AMORLINC(cost,date_purchased,first_period,salvage,period,rate,basis)
COUPDAYBS
Returns the number of days from start date
of the coupon period to the settlement.
=COUPDAYBS(settlement,maturity,frequency,basis)
COUPDAYS
Returns the number of days in the coupon
period that includes the settlement date.
=COUPDAYS(settlement,maturity,frequency,basis)
COUPDAYSNC
Returns the number of days from the
settlement date to the next coupon date.
=COUPDAYSNC(settlement,maturity,frequency,basis)
COUPNCD
Returns the number of the next coupon date
after the settlement date.
=COUPNCD(settlement,maturity,frequency,basis)
COUPNUM
Returns the total number of coupons payable
between the settlement and maturity date, rounded up to the nearest whole
coupon.
=COUPNUM(settlement,maturity,frequency,basis)
COUPPCD
Returns the number of the previous coupon
date before the settlement date.
=COUPPCD(settlement,maturity,frequency,basis)
CUMIPMT
Returns the cumulative interest on a loan
between start and stop dates.
=CUMIPMT(rate,nper,pv,start_period,end_period,type)
CUMPRINC
Returns the cumulative principal amount
between start and stop dates.
=CUMPRINC(rate,nper,pv,start_period,end_period,type)
DB
Returns the asset depreciation for a period
using the fixed declining balance method.
=DB(cost,salvage,life,period,month)
DDB
Returns the asset depreciation for a
specified period using the double-declining balance method, or another method
you specify.
=DDB(cost,salvage,life,period,factor)
DISC
Returns the security discount rate.
=DISC(settlement,maturity,pr,redemption,basis)
DOLLARDE
Converts a fraction dollar price into a
decimal dollar price.
=DOLLARDE(fractional_dollar,fraction)
DOLLARFR
Converts a decimal dollar price into a
fraction dollar price.
=DOLLARFR(decimal_dollar,fraction)
DURATION
Returns the duration for an assumed par
value of $100 using the Macauley method.
=DURATION(settlement,maturity,coupon,yield,frequency,basis)
EFFECT
Returns the effective interest rate
annually, give the nominal annual interest rate and the number of compounding
periods per year.
EFFECT(nominal_rate,npery)
FV
Returns the future value of periodic
payments and a constant interest rate.
=FV(rate,nper,pmt,pv,type)
FVSCHEDULE
Returns the future value of the initial
principal after applying several compound interest rates.
=FVSCHEDULE(principal,schedule)
INTRATE
Returns the interest rate of a fully
invested security.
=INTRATE(settlement,maturity,investment,redemption,basis)
IPMT
Returns the interest payment for a period of
time based on an investment with periodic constant payments and a constant
interest rate.
=IPMT(rate,per,nper,pv,fv,type)
IRR
Returns the internal rate of return for a
series of cash flows represented by numbers in the form of values.
=IRR(values,guess)
MDURATION
Returns a modified duration of a security
with an assumed par value of $100.
=MDURATION(settlement,maturity,coupon,yield,frequency,basis)
MIRR
Returns a modified internal rate of return
for several periodic cash flows.
=MIRR(values,finance_rate,reinvest_rate)
NOMINAL
Returns the nominal annual interest rate given
an effective rate and a number of compounding periods per year.
=NOMINAL(effective_rate,npery)
NPER
Returns the number of periods for an
investment based on periodic constant payments and a constant interest rate.
=NPER(rate,pmt,pv,fv,type)
NPV
Calculates the net present value of an
investment with the discount rate and several future payments and income.
=NPV(rate,value1,value2,...)
ODDFPRICE
Returns the value of a security based on a
per $100 face value and an odd first period.
=ODDFPRICE(settlement,maturity,issue,first_coupon,rate,yield,redemption,frequency,basis)
ODDFYIELD
Returns the security yield with an odd first
period.
=ODDFYIELD(settlement,maturity,issue,first_coupon,rate,pr,redemption,frequency,basis)
ODDLPRICE
Returns the per $100 face value of a
security having an odd last coupon period.
=ODDLPRICE(settlement,maturity,last_interest,rate,yield,redemption,frequency,basis)
ODDLYIELD
Returns the security yield that has an odd
last period.
=ODDLYIELD(settlement,maturity,last_interest,rate,pr,redemption,frequency,basis)
PMT
Calculates the loan payment for a loan based
on constant payments and constant interest rates.
=PMT(rate,nper,pv,fv,type)
PPMT
Returns the principal payment for a specific
period of an investment based on periodic constant payments and a constant
interest rate.
=PPMT(rate,per,nper,pv,fv,type)
PRICE
Returns the value of a security based on
price per $100 face value and periodic payments.
=PRICE(settlement,maturity,rate,yield,redemption,frequency,basis)
PRICEDISC
Returns the value of a discounted security
based on a price per $100 face value.
=PRICEDISC(settlement,maturity,discount,redemption,basis)
PRICEMAT
Returns the value of a security that pays
interest at maturity and price per $100 face value.
=PRICEMAT(settlement,maturity,issue,rate,yield,basis)
PV
Based on an investment, returns the present
value.
=PV(rate,nper,pmt,fv,type)
RATE
Returns per period the interest of an
annuity.
=RATE(nper,pmt,pv,fv,type,guess)
RECEIVED
Based on a fully invested security, returns
the amount received at maturity.
=RECEIVED(settlement,maturity,investment,discount,basis)
SLN
Based on one period, returns the
straight-line depreciation on an asset.
=SLN(cost,salvage,life)
SYD
Based on a specified period, returns the
sum-of-years-digits depreciation of an asset.
=SYD(cost,salvage,life,per)
TBILLEQ
For a treasury bill, returns the bond
equivalent yield.
=TBILLEQ(settlement,maturity,discount)
TBILLPRICE
For a treasury bill, returns the price per
$100 face value.
=TBILLPRICE(settlement,maturity,discount)
TBILLYIELD
For a treasury bill, returns the yield.
=TBILLYIELD(settlement,maturity,pr)
VDB
For a period you specify, returns the
depreciation of an asset.
=VDB(cost,salvage,life,start_period,end_period,factor,no_switch)
XIRR
For a schedule of cash flows that are not
necessarily periodic, returns the internal rate of return.
=XIRR(values,dates,guess)
XNPV
For a schedule of cash flows that are not
necessarily periodic, returns the present value.
=XNPV(rate,values,dates)
YIELD
Based on a yield that pays periodic
interest, returns the yield of the security.
=YIELD(settlement,maturity,rate,pr,redemption,frequency,basis)
YIELDDISC
For a discounted security, returns the
annual yield.
=YIELDDISC(settlement,maturity,pr,redemption,basis)
YIELDMAT
Based on a security that pays interest at
maturity, returns the annual yield.
=YIELDMAT(settlement,maturity,issue,rate,pr,basis)
Information
Functions
CELL
Returns information about a cell's location,
formatting, or contents in the upper-left cell in a reference.
=CELL(info_type,reference)
COUNTBLANK
Counts the number of empty cells in a
specified range.
=COUNTBLANK(range)
ERROR.TYPE
Returns the corresponding number value
associated with an error type in Microsoft Excel. Returns #N/A if no error
exists.
=ERROR.TYPE(error_val)
INFO
Returns operating environment information.
=INFO(type_text)
ISBLANK
Returns TRUE if value refers
to an empty cell.
=ISBLANK(value)
ISERR
Returns TRUE if value refers
to any error value in Microsoft Excel except #NA.
=ISERR(value)
ISERROR
Returns TRUE if value refers
to any error value in Microsoft Excel.
=ISERROR(value)
ISEVEN
Returns TRUE or FALSE if the number is even
or odd, TRUE being even and FALSE being odd.
=ISEVEN(number)
ISLOGICAL
Returns TRUE if the value is logical.
=ISLOGICAL(value)
ISNA
Returns TRUE if value is
associated with the error type #NA.
=ISNA(value)
ISNONTEXT
Returns TRUE if value refers
to any item that isn't text. Returns TRUE if value refers to a blank cell.
=ISNONTEXT(value)
ISNUMBER
Returns TRUE if value refers
to a number.
=ISNUMBER(value)
ISODD
Returns TRUE if number is odd
and FALSE if the number is even.
=ISODD(number)
ISREF
Returns TRUE if value refers
to a reference.
=ISREF(value)
ISTEXT
Returns TRUE if value refers
to text.
=ISTEXT(value)
N
Returns value converted to a
number.
=N(value)
NA
Returns the error value associated with #NA.
=NA()
TYPE
Returns the type of value, for example,
number = 1, text = 2, logical value = 4, error value = 16, and array = 64. Use
TYPE when the behavior of another function depends on the type of value in a
particular cell.
=TYPE(value)
Logical
Functions
AND
Returns TRUE if all arguments in the formula
are true, and FALSE if any one argument is false.
=AND(logical1,logical2,...)
FALSE
Returns the value FALSE. There are no
arguments associated with this function.
=FALSE()
IF
Returns a value if one condition is true,
and returns another value if the condition is false.
=IF(logical_test,value_if_true,value_if_false)
NOT
Returns the reverse value of its arguments.
=NOT(logical)
OR
Returns TRUE if any one argument is true.
Returns FALSE if all arguments are false.
=OR(logical1,logical2,...)
TRUE
Returns the value TRUE. There are no
arguments associated with this function.
=TRUE()
Lookup
and Reference Functions
ADDRESS
Given specified row and column numbers,
creates a cell address as text.
=ADDRESS(row_num,column_num,abs_num,A1,sheet_text)
AREAS
Returns the number of areas in reference.
=AREAS(reference)
CHOOSE
Returns the index number from a list of
arguments.
=CHOOSE(index_num,value1,value2,...)
COLUMN
Based on a given reference, returns the
column number.
=COLUMN(reference)
COLUMNS
Based on an array or reference, returns the
number of columns.
=COLUMNS(array)
HLOOKUP
Searches for a specified value in an array
or tables, based on the value found in the first row.
=HLOOKUP(lookup_value,table_array,row_index_number,range_lookup)
HYPERLINK
Creates a shortcut or jump that switches to
another location within the workbook, or opens a document stored on a local
drive, a network server, an intranet, or the Internet.
=HYPERLINK(link_location,cell_contents)
INDEX (Array Form)
Based on a table or array, returns the value
of an element selected by the row number and column letter indexes.
=INDEX(array,row_num,column_num)
INDEX (Reference Form)
Based on the intersection of a particular
row and column, returns the reference of the cell.
=INDEX(reference,row_num,column_num,area_num)
INDIRECT
Returns the reference based on a text
string.
=INDIRECT(ref_text,A1)
LOOKUP (Vector Form)
Based on a range of one row or one column,
returns the value from the same position in a second row or column.
=LOOKUP(lookup_value,lookup_vector,result_vector)
LOOKUP (Array Form)
Looks in the first row or column of an
array, and returns the specified value from the same position in the last row
or column of the array.
=LOOKUP(lookup_value,array)
MATCH
Returns the position of an item in an array
that matches a specified value and order.
=MATCH(lookup_value,lookup_array,match_type)
OFFSET
Returns a reference to a range that is a
specific number of rows and columns from a cell or range of cells.
=OFFSET(reference,rows,columns,height,width)
ROW
Based on a reference, returns the row
number.
=ROW(reference)
ROWS
Based on a reference or array, returns the
number of rows.
=ROWS(array)
TRANSPOSE
Returns a horizontal range of cells as
vertical or vice versa.
=TRANSPOSE(array)
VLOOKUP
Looks for a value in the left most column of
a table and returns a value from the column number you specify.
=VLOOKUP(lookup_value,table_array,column_index_num,range_lookup)
Math
and Trigonometry Functions
ABS
Returns the absolute value of number.
=ABS(number)
ACOS
Returns the arcosine of number.
The arccosine is the angle whose cosine is number.
=ACOS(number)
ACOSH
Returns the inverse hyperbolic cosine of number.
=ACOSH(number)
ASIN
Returns the arcsine of number.
=ASIN(number)
ASINH
Returns the inverse hyperbolic sine of number.
=ASINH(number)
ATAN
Returns the arctangent of number.
=ATAN(number)
ATAN2
Returns the arctangent of the specified x
and y coordinates.
=ATAN2(x_num,y_num)
ATANH
Returns the inverse hyperbolic tangent of a
number.
=ATANH(number)
CEILING
Returns number rounded up to
the nearest integer, or to the nearest multiple of significance (for example,
to the nearest nickel).
=CEILING(number,significance)
COMBIN
Returns the number of combinations for a
given number of items.
=COMBIN(number,number_chosen)
COS
Returns the cosine of a given angle.
=COS(number)
COSH
Returns the hyperbolic cosine of number.
=COSH(number)
COUNTIF
Counts the number of cells in a specified
range that meet the criteria you specify.
=COUNTIF(range,criteria)
DEGREES
Converts radians into degrees.
=DEGREES(angle)
EVEN
Returns number rounded up to
the nearest integer.
=EVEN(number)
EXP
Returns e raised to the power of number.
=EXP(number)
FACT
Returns the factorial of number.
=FACT(number)
FACTDOUBLE
Returns the double factorial of number.
=FACTDOUBLE(number)
FLOOR
Rounds number down, toward
zero, to the nearest multiple of significance.
=FLOOR(number,significance)
GCD
Returns the greatest common divisor of two
or more integers.
=GCD(number1,number2,...)
INT
Rounds number down to the
nearest integer.
=INT(number)
LCM
Returns the least common multiple of
integers.
=LCM(number1,number2,...)
LN
Returns the natural logarithm of a number.
=LN(number)
LOG
Returns the logarithm of a number to the
base you specify.
=LOG(number,base)
LOG10
Returns the base 10 logarithm of a number.
=LOG10(number)
MDETERM
Returns the matrix determinant of an array.
=MDETERM(array)
MINVERSE
Returns the inverse matrix for the matrix
stored in an array.
=MINVERSE(array)
MMULT
Returns the matrix product of two arrays.
=MMULT(array1,array2)
MOD
Returns the remainder after number
is divided by divisor.
=MOD(number,divisor)
MROUND
Returns a number rounded to the desired
multiple.
=MROUND(number,multiple)
MULTINOMIAL
Returns the ratio of the factorial of a sum
of values to the product of factorials.
=MULTINOMIAL(number1,number2,...)
ODD
Returns number rounded to the
nearest odd integer.
=ODD(number)
PI
Returns the number 3.14159265358979, the
mathematical constant Pi, accurate to 15 digits. There are no arguments
associated with this function.
=PI()
POWER
Returns the result of a number raised to a
power.
=POWER(number,power)
PRODUCT
Multiplies all the numbers given as
arguments and returns the product.
=PRODUCT(number1,number2,...)
QUOTIENT
Returns the integer portion of a division.
Use this function to discard the remainder of a division.
=QUOTIENT(numerator,denominator)
RADIANS
Converts degrees to radians.
=RADIANS(angle)
RAND
Returns an evenly distributed number greater
than or equal to 0 and less than 1. There are no arguments associated with this
function.
=RAND()
RANDBETWEEN
Returns a random number between the numbers
you specify.
=RANDBETWEEN(bottom,top)
ROMAN
Converts an Arabic numeral to Roman, as
text.
=ROMAN(number,form)
ROUND
Rounds a number to a specified number of
digits.
=ROUND(number,num_digits)
ROUNDDOWN
Rounds a number down toward 0.
=ROUNDDOWN(number,num_digits)
ROUNDUP
Rounds a number up away from 0.
=ROUNDUP(number,num_digits)
SERIESSUM
Returns the sum of a power series based on
the formula.
=SERIESSUM(x,n,m,coefficients)
SIGN
Determines the sign (positive or negative)
of number.
=SIGN(number)
SIN
Returns the sine of the given angle.
=SIN(number)
SINH
Returns the hyperbolic sine of number.
=SINH(number)
SQRT
Returns the positive square root of number.
=SQRT(number)
SQRTPI
Returns the square root of (number*pi).
=SQRTPI(number)
SUBTOTAL
Returns a subtotal from a list or database.
=SUBTOTAL(function_num,ref1,ref2,...)
SUM
Adds the numbers in a range of cells.
=Sum(number1,number2,...)
SUMIF
Adds the cells specified by criteria.
=SUMIF(range,criteria,sum_range)
SUMPRODUCT
Multiplies corresponding components in the
given array, and returns the sum of those products.
=SUMPRODUCT(array1,array2,array3,...)
SUMSQ
Returns the sum of the squares of the
arguments.
=SUMSQ(number1,number2,...)
SUMX2MY2
Returns the sum of the difference of squares
of corresponding values in two arrays.
=SUMX2MY2(array_x,array_y)
SUMX2PY2
Returns the sum of the sum of squares in
corresponding values in two arrays.
=SUMX2PY2(array_x,array_y)
SUMXMY2
Returns the sum of squares of differences of
corresponding values in two arrays.
=SUMXMY2(array_x,array_y)
TAN
Returns the tangent of the given angle.
=TAN(number)
TANH
Returns the hyperbolic tangent of number.
=TANH(number)
TRUNC
Truncates number to an
integer, removing the fractional part of the number. Note that this function
does not round number.
=TRUNC(number,num_digits)
Statistical
Functions
AVEDEV
Returns the average of the absolute
deviations of data points from their mean.
=AVEDEV(number1,number2,...)
AVERAGE
Returns the average of the arguments.
=AVERAGE(number1,number2,...)
AVERAGEA
Calculates the average of the values in the
list of arguments.
=AVERAGEA(value1,value2,...)
BETADIST
Returns the cumulative beta probability
density function.
=BETADIST(x,alpha,beta,A,B)
BETAINV
Returns the inverse of the cumulative beta
probability density function.
=BETAINV(probability,alpha,beta,A,B)
BINOMDIST
Returns the individual term binomial
distribution probability.
=BINOMDIST(number_s,trials,probability_s,cumulative)
CHIDIST
Returns the one-tailed probability of the
chi-squared distribution.
=CHIDIST(x,degrees_freedom)
CHINV
Returns the inverse of the one-tailed
probability of the chi-squared distribution.
=CHINV(probability,degrees_freedom)
CHITEST
Returns the test for independence.
=CHITEST(actual_range,expected_range)
CONFIDENCE
Returns the confidence interval - the range
on either side of a sample mean - for the population mean.
=CONFIDENCE(alpha,standard_dev,size)
CORREL
Returns the correlation coefficient of array1
and array2 cell ranges.
=CORREL(array1,array2)
COUNT
Counts the number of cells that contain
numbers within the list of arguments.
=COUNT(value1,value2,...)
COUNTA
Counts the number of cells that are not
empty within the list of arguments.
=COUNTA(value1,value2,...)
COVAR
Returns covariance, the average of the
products of deviations for each data point pair.
=COVAR(array1,array2)
CRITBINOM
Returns the smallest value for which the
cumulative binomial distribution is greater than or equal to a criterion value.
=CRITBINOM(trials,probability_s,alpha)
DEVSQ
Returns the sum of squares of deviations of
data points from their sample mean.
=DEVSQ(number1,number2,...)
EXPONDIST
Returns the exponential distribution.
=EXPONDIST(x,lambda,cumulative)
FDIST
Returns the F probability distribution.
=FDIST(x,degrees_freedom1,degrees_freedom2)
FINV
Returns the inverse of the F probability
distribution.
=FINV(probability,degrees_freedom1,degrees_freedom2)
FISHER
Returns the Fisher transformation at x.
=FISHER(x)
FISHERINV
Returns the inverse of the Fisher
transformation.
=FISHERINV(y)
FORECAST
Calculates or predicts a future value by
using existing values.
=FORECAST(x,known_y's,known_x's)
FREQUENCY
Calculates how often values occur within a
range of values, and then returns a vertical array of numbers. This function
returns an array of values and therefore must be entered as an array formula
(using Ctrl+Shift+Enter).
=FREQUENCY(data_array,bins_array)
FTEST
Returns the result of an Ftest.
=FTEST(array1,array2)
GAMMADIST
Returns the gamma distribution.
=GAMMADIST(x,alpha,beta,cumulative)
GAMMAINV
Returns the inverse of the gamma cumulative
distribution.
=GAMMAINV(probability,alpha,beta)
GAMMALN
Returns the natural logarithm of the gamma
function.
=GAMMALN(x)
GEOMEAN
Returns the geometric mean of an array or
range of positive data.
=GEOMEAN(number1,number2,...)
GROWTH
Calculates predicted exponential growth by
using existing data.
=GROWTH(known_y's,known_x's,new_x's,const)
HARMEAN
Returns the harmonic mean of a data set.
=HARMEAN(number1,number2,...)
HYPGEOMDIST
Returns the hypergeometric distribution.
=HYPERGEOMDIST(sample_s,number_sample,population_s,number_population)
INTERCEPT
Calculates the point at which a line will
intersect the y-axis by using existing x-values and y-values.
=INTERCEPT(known_y's,known_x's)
KURT
Returns the Kurtosis of a data set.
=KURT(number1,number2,...)
LARGE
Returns the k-th largest value in a
data set.
=LARGE(array,k)
LINEST
Calculates the statistics for a line by
using the "least squares" method to calculate a straight line that
best fits your data, and returns an array that describes the line.
=LINEST(known_y's,known_x's,const,stats)
LOGEST
In regression analysis, calculates an
exponential curve that fits your data and returns an array of values that
describes the curve.
=LOGEST(known_y's,known_x's,const,stats)
LOGINV
Returns the inverse of the lognormal
cumulative distribution function of x, wherein (x) is normally
distributed with parameters mean and standard_dev.
=LOGINV(probability,mean,standard_dev)
LOGNORMDIST
Returns the cumulative lognormal
distribution of x, wherein (x) is normally distributed with parameters mean
and standard_dev.
=LOGNORMDIST(x,mean,standard_dev)
MAX
Returns the largest value in a set of
values.
=MAX(number1,number2,...)
MAXA
Returns the largest value in a list of
arguments.
=MAXA(value1,value2,...)
MEDIAN
Returns the median of a given set numbers.
=MEDIAN(number1,number2,...)
MIN
Returns the smallest number in a set of
values. Nonnumeric values are ignored.
=MIN(number1,number2,...)
MINA
Returns the smallest value in a list of
arguments including text and logical values such as "No Grade", TRUE,
and FALSE. TRUE evaluates to 1. All other nonnumeric values evaluate to 0.
=MINA(value1,value2,...)
MODE
Returns the most frequently occurring, or
repetitive, value in an array or range of data.
=MODE(number1,number2,...)
NEGBINOMDIST
Returns the negative binomial distribution.
=NEGBINOMDIST(number_f,number_s,probability_s)
NORMDIST
Returns the normal cumulative distribution
for the specified mean and standard deviation.
=NORMDIST(x,mean,standard_dev,cumulative)
NORMINV
Returns the inverse of the normal cumulative
distribution for the specified mean and standard deviation.
=NORMINV(probability,mean,standard_dev)
NORMSDIST
Returns the standard normal cumulative
distribution function.
=NORMSDIST(z)
NORMSINV
Returns the inverse of the standard normal
cumulative distribution.
=NORMSINV(probability)
PEARSON
Returns the Pearson product moment
correlation coefficient, r, a dimensionless index ranges from -1.0 to 1.0
inclusive and reflects the extent of a linear relationship between two data
sets.
=PEARSON(array1,array2)
PERCENTILE
Returns the k-th percentile of values
in a range.
=PERCENTILE(array,k)
PERCENTRANK
Returns the rank of a value in a data set as
a percentage of the data set.
=PERCENTRANK(array,x,significance)
PERMUT
Returns the number of permutations for a
given number of objects that can be selected from number objects.
=PERMUT(number,number_chosen)
POISSON
Returns the Poisson distribution.
=POISSON(x,mean,cumulative)
PROB
Returns the probability that values in a
range are between two specified limits.
=PROB(x_range,prob_range,lower_limit,upper_limit)
QUARTILE
Returns the quartile of a data set.
=QUARTILE(array,quart)
RANK
Returns the rank of a number in a list of
numbers.
=RANK(number,ref,order)
RSQ
Returns the square of the Pearson product
moment correlation coefficient through data points in known_y's
and known_x's.
=RSQ(known_y's,known_x's)
SKEW
Returns the skewness of a distribution.
=SKEW(number1,number2,...)
SLOPE
Returns the slope of the regression line
through data points in known_y's and known_x's.
=SLOPE(known_y's,known_x's)
SMALL
Returns the k-th smallest value in a
data set.
=SMALL(array,k)
STANDARDIZE
Returns a normalized value from a
distribution characterized by mean and standard_dev.
=STANDARDIZE(x,mean,standard_dev)
STDEV
Estimates standard deviation based on a
sample. Nonnumeric values are ignored.
=STDEV(number1,number2,...)
STDEVA
Estimates standard deviation based on a
sample including text and logical values such as "None", TRUE, and
FALSE. TRUE evaluates to 1. All other nonnumeric values evaluate to 0.
=STDEVA(value1,value2,...)
STDEVP
Calculates standard deviation based on the
entire population given as arguments. Nonnumeric values are ignored.
=STDEVP(number1,number2,...)
STDEVPA
Calculates standard deviation based on the
entire population given as arguments including text and logical values such as
"None", TRUE, and FALSE. TRUE evaluates to 1. All other nonnumeric
values evaluate to 0.
=STDEVPA(value1,value2,...)
STEYX
Returns the standard error of the predicted
y value for each x in the regression.
=STEYX(known_y's,known_x's)
TDIST
Returns the student's t-distribution.
TDIST(x,degrees_freedom,tails)
TINV
Returns the inverse of the student's
t-distribution for the specified degrees of freedom.
=TINV(probability,degrees_freedom)
TREND
Returns values along a linear trend.
=TREND(known_y's,known_x's,new_x's,const)
TRIMMEAN
Returns the mean of the interior of a data
set.
=TRIMMEAN(array,percent)
TTEST
Returns the probability associated with the
student's t-test.
=TTEST(array1,array2,tails,type)
VAR
Estimates variance based on a sample, with
from 1 to 30 arguments.
=VAR(number1,number2,...)
VARA
Estimates variance based on a sample
including text and logical values such as "None", TRUE, and FALSE.
TRUE evaluates to 1. All other nonnumeric values evaluate to 0.
=VARA(value1,value2,...)
VARP
Calculates variance based on the entire
population. Nonnumeric values are ignored.
=VARP(number1,number2,...)
VARPA
Calculates variance based on the entire
population. including text and logical values such as "None", TRUE,
and FALSE. TRUE evaluates to 1. All other nonnumeric values evaluate to 0.
=VARPA(value1,value2,...)
WEIBULL
Returns the Weibull distribution.
=WEIBULL(x,alpha,beta,cumulative)
ZTEST
Returns the two-tailed P-value of a z-test.
=ZTEST(array,x,sigma)
Text
Functions
CHAR
Returns the character specified by a number.
=CHAR(number)
CLEAN
Removes all nonprintable characters from
text.
=CLEAN(text)
CODE
Returns a numeric code from the first
character in a text string.
=CODE(text)
CONCATENATE
Joins several text strings into one text
string.
=CONCATENATE(text1,text2,...)
DOLLAR
Converts a number to text using Currency
format, with the decimals rounded to the specified place. The format used is
$#,##0.00_);($#,##0.00).
=DOLLAR(number,decimals)
EXACT
Compares two text strings and returns TRUE
if they're exactly the same, and FALSE otherwise. EXACT is case sensitive.
=EXACT(text1,text2)
FIND
Finds one text string with another text
string, and returns the number of the starting position of find_text,
from the leftmost character of within_text. FIND is case
sensitive and cannot include wildcard characters.
=FIND(find_text,within_text,start_num)
FINDB
Finds one text string with another text
string, and returns the number of the starting position of find_text,
from the leftmost character of within_text. FINDB is case
sensitive and cannot include wildcard characters. This function is for use with
double-byte characters.
=FINDB(find_text,within_text,start_num)
FIXED
Rounds a number to a specified number of
decimals, formats the number in decimal format using a period and commas, and
returns the result as text.
=FIXED(number,decimals,no_commas)
LEFT
Returns the first character or characters in
a text string.
=LEFT(text,num_char)
LEN
Returns the number of characters in a text
string.
=LEN(text)
LOWER
Converts all uppercase letters in a text
string to lowercase.
=LOWER(text)
MID
Returns a specific number of characters from
a text string, starting at the position you specify.
=MID(text,start_num,num_char)
PROPER
Capitalizes the first letter in a text string
and any other letters in text that follow any character other than a letter.
=PROPER(text)
REPLACE
Replaces part of a text string with a
different text string based on the number of characters you specify.
=REPLACE(old_text,start_num,num_chars,new_text)
REPLACEB
Replaces part of a text string with a
different text string, based on the number of bytes you specify.
=REPLACEB(old_text,start_num,num_bytes,new_text)
REPT
Repeats text a given number of times.
=REPT(text,number_times)
RIGHT
Returns the last character or characters in
a text string.
=RIGHT(text,num_chars)
SEARCH
Returns the number of the character at which
a specific character or text string is first found, reading from left to right.
SEARCH is not case sensitive and can include wildcard characters.
=SEARCH(find_text,within_text,start_num)
SEARCHB
Returns the number of the double-byte
character at which a specific character or text string is first found, reading
from left to right. SEARCHB is not case sensitive and can include wildcard
characters.
=SEARCHB(find_text,within_text,start_num)
SUBSTITUTE
Substitutes new_text for old_text
in a text string.
=SUBSTITUTE(text,old_text,new_text,instance_num)
T
Returns the text referred to by value.
=T(value)
TEXT
Converts a value to text in a specific
number format.
=TEXT(value,format_text)
TRIM
Removes all spaces from text except for
single spaces between words.
=TRIM(text)
UPPER
Converts text to uppercase.
=UPPER(text)
VALUE
Converts a text string that represents a
number to a number.
=VALUE(text)
|
