Excel Functions

EXCEL FUNCTIONS Index

COMPLETE LIST OF EXCEL FUNCTIONS Name

Source

Type

Description

ABS

Built In

Maths

Returns the absolute value of a number

ACCRINT

Analysis ToolPak Financial

Returns the accrued interest for a security that pays periodic interest

ACCRINTM

Analysis ToolPak Financial

Returns the accrued interest for a security that pays interest at maturity

ACOS

Built In

Maths

Returns in radians the arccosine of a number

ACOSH

Built In

Maths

Returns the inverse hyperbolic cosine of a number

ADDRESS

Built In

Lookup & Ref

Creates a cell address as text based on given row and column rumbers

AMORDEGRC

Analysis ToolPak Financial

Returns the depreciation for each accounting period using the French accounting system

AMORLINC

Analysis ToolPak Financial

Returns the depreciation for each accounting period using the French accounting system

AND

Built In

Logical

Returns TRUE if all its arguments are TRUE

AREAS

Built In

Lookup & Ref

Returns the number of areas in a reference

ASC

Built In

Text

Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters

ASIN

Built In

Maths

Returns in radians the arcsine of a number

ASINH

Built In

Maths

Returns the inverse hyperbolic sine of a number

ATAN

Built In

Maths

Returns in radians the arctangent of a number

ATAN2

Built In

Maths

Returns in radians the arctangent from x and y coordinates

ATANH

Built In

Maths

Returns the inverse hyperbolic tangent of a number

AVEDEV

Built In

Statistical

Returns the average of the absolute deviations of data points from their mean

AVERAGE

Built In

Statistical

Returns the average (arithmetic mean) of up to 30 numeric arguments

AVERAGEA

Built In

Statistical

Returns the average (arithmetic mean) of its arguments and includes evaluation of text and logical arguments

BAHTTEXT

Built In

Text

Converts a number to Thai text and adds a suffix of "Baht"

BESSELI

Analysis ToolPak Engineering

Returns the Bessel function evaluated for purely imaginary arguments

BESSELJ

Analysis ToolPak Engineering

Returns the Bessel function represented by Jn(x)

BESSELK

Analysis ToolPak Engineering

Returns the modified Bessel function represented by Kn(x)

BESSELY

Analysis ToolPak Engineering

Returns the Bessel / Weber / Neumann function Yn(x)

BETADIST

Built In

Statistical

Returns the cumulative beta probability density function

BETAINV

Built In

Statistical

Returns the inverse of the cumulative beta probability density function

BIN2DEC

Analysis ToolPak Engineering

Converts binary number to decimal

BIN2HEX

Analysis ToolPak Engineering

Converts binary number to hexadecimal

BIN2OCT

Analysis ToolPak Engineering

Converts binary number to octal

BINOMDIST

Built In

Statistical

Returns the individual term binomial distribution probability

CALL

Built In

External

Calls a procedure in a dynamic link library or code resource

CEILING

Built In

Maths

Rounds a number (away from zero) to the nearest integer or to the nearest multiple of significance

CELL

Built In

Information

Returns information about the formatting , location or contents of the cell or upper left cell of the reference

CHAR

Built In

Text

Returns the ANSI character set (Microsoft) or Macintosh character set (Macintosh) specified by the code number

CHIDIST

Built In

Statistical

Returns the one-tailed probability of the chisquared distribution

CHIINV

Built In

Statistical

Returns the inverse of the one-tailed probability of the chi-squared distribution

CHITEST

Built In

Statistical

Returns the value from the chi-squared (γ2) distribution for the statistic and the appropriate degrees of freedom

CHOOSE

Built In

Lookup & Ref

Uses a specified index number to select one from up to 29 specified values

CLEAN

Built In

Text

Removes all nonprintable characters from text

CODE

Built In

Text

Returns a numeric code for the first character in a text string

COLUMN

Built In

Lookup & Ref

Returns the column number of the cell or a specified reference

COLUMNS

Built In

Lookup & Ref

Returns the number of columns in an array or reference

COMBIN

Built In

Maths

Returns the number of combinations for a given number of objects

COMPLEX

Analysis ToolPak Engineering

Converts real and imaginary coefficients into complex numbers of the form x + yi or x + yj depending upon suffix

CONCATENATE Built In

Text

Joins several text items into one text item

CONFIDENCE

Statistical

Returns the confidence interval for a population mean

Built In

CONVERT

Analysis ToolPak Engineering

Converts a number from one measurement system to another

CORREL

Built In

Statistical

Returns the correlation coefficient between two arrays of data

COS

Built In

Maths

Returns the cosine of a given angle given in radians

COSH

Built In

Maths

Returns the hyperbolic cosine of a number

COUNT

Built In

Statistical

Counts the number of cells that contain numbers and also numbers within the list of arguments

COUNTA

Built In

Statistical

counts how many values are in the list of arguments

COUNTBLANK

Built In

Information

Counts the number of non-empty cells and the values within the list of arguments

COUNTIF

Built In

Maths

Counts the number of cells that meet the criteria specified in the argument

COUPDAYBS

Analysis ToolPak Financial

Returns the number of days from the beginning of the coupon period to the settlement date using the specified or default day counting basis

COUPDAYS

Analysis ToolPak Financial

Returns the number of days in the coupon period that contains the settlent date using the specified or default day counting basis

COUPDAYSNC

Analysis ToolPak Financial

Returns the number of days from the settlement date to the next coupon date using the specified or default day counting basis

COUPNCD

Analysis ToolPak Financial

Returns the next coupon date after the settlement date using the specified or default day counting basis

COUPNUM

Analysis ToolPak Financial

Returns the number of coupons payable between the settlement date and maturity date using the specified or default day counting basis

COUPPCD

Analysis ToolPak Financial

Returns the previous coupon date before the settlement date using the specified or default day counting basis

COVAR

Built In

Statistical

Returns covariance; the average of the products of deviations for each data point pair

CRITBINOM

Built In

Statistical

Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value

CUMIPMT

Analysis ToolPak Financial

Returns the cumulative interest paid between two periods

CUMPRINC

Analysis ToolPak Financial

Returns the cumulative principal paid on a loan between two periods

DATE

Built In

Returns the sequential Excel date / time serial number that represents a particular date

DATEDIF

Analysis ToolPak Date & Time

Date & Time

Calculates differences between two dates in terms of specified units and assumptions

DATEVALUE

Built In

Date & Time

Converts a date text form to an Excel date / time serial number

DAVERAGE

Built In

Database

Returns the average of selected list or database entries based on specified criteria

DAY

Built In

Date & Time

Converts an Excel date / time serial number to the day of a month

DAYS360

Built In

Date & Time

Calculates the number of days between two dates using a specified 30 day month 360 day year method

DB

Built In

Financial

Returns the depreciation of an asset for a specified period, using the fixed declining balance method

DCOUNT

Built In

Database

Counts the cells containing numbers from a specified database that match specified criteria

DCOUNTA

Built In

Database

Counts non blank cells from a specified database that match specified criteria criteria

DDB

Built In

Financial

Returns the deoreciation of an asset for a specified period, using the double-declining balance method of some other method that is specified

DEC2BIN

Analysis ToolPak Engineering

Converts a decimal number to binary

DEC2HEX

Analysis ToolPak Engineering

Converts a decimal number to hexadecimal

DEC2OCT

Analysis ToolPak Engineering

Converts a decimal number to octal

DEGREES

Built In

Converts radians to degrees

DELTA

Analysis ToolPak Engineering

Test whether two values are equal

DEVSQ

Built In

Statistical

Returns the sum of the squares of the deviations from the sample mean

DGET

Built In

Database

Extracts from a specified database a single value that matches specified criteria

DISC

Analysis ToolPak Financial

Returns the discount rate for a security using the specified or default day counting basis

DMAX

Built In

Database

Extracts maximum number in a column of a list or database that matches specified conditions

DMIN

Built In

Database

Extracts minimum number in a column of a list or database that matches specified conditions

DOLLAR

Built In

Text

Converts a number to text, using currency format

DOLLARDE

Analysis ToolPak Financial

Converts a dollar price (expressed as a fraction) into a dollar price expressed as a decimal number

DOLLARFR

Analysis ToolPak Financial

Converts a dollar price (expressed as a decimal number) into a dollar price expressed as a fraction

DPRODUCT

Built In

Multiplies the values in a particular field of records that match the specified criteria in a database

Maths

Database

DSTDEV

Built In

Database

Estimates standard deviation of a population based on a sample using numbers in a column of a list or database that match specified conditions

DSTDEVP

Built In

Database

Calculates the standard deviation based on the entire population using numbers in a column of a list or database that match specified conditions

DSUM

Built In

Database

Adds the numbers in the field column of records in the database that match the specified criteria

DURATION

Analysis ToolPak Financial

Returns the Macauley duration of a security with periodic interest payments using the specified or default day counting basis

DVAR

Built In

Database

Estimates the variance of a population based on a sample by using the numbers in a column of a list or database that match specified criteria

DVARP

Built In

DataBase

Calculates the variance of a population based on the entire population by using the numbers in a column of a list or database that match specified criteria

EDATE

Analysis ToolPak Date & Time

Returns the Excel date / time serial number of the date that is the indicated number of months before or after the specified number of months from the start_date

EFFECT

Analysis ToolPak Financial

Returns the effective annual interest rate of a given nominal rate with its compounding frequency

EOMONTH

Analysis ToolPak Date & Time

Returns the Excel date / time serial number of the last day of the month before or after a specified number of months from start_date

ERF

Analysis ToolPak Engineering

Returns the error function integrated between lower_limit and upper_limit

ERFC

Analysis ToolPak Engineering

Returns the complementary ERF function integrated between x and infinity

ERROR.TYPE

Built In

Information

Returns a number corresponding to an Excel error type

EUROCONVERT Add-in

External

Converts a number to or from Euros to or from a member currency or converts between one euro member currency to another using the Euro as an intermediary (triangulation)

EVEN

Built In

Maths

Rounds a number away from zero to the nearest even integer

EXACT

Built In

Text

Checks to see whether two text values are identical

EXP

Built In

Maths

Returns e (=2.71828182845904) raised to the power of a given number

EXPONDIST

Built In

Statistical

Returns the exponential distribution

FACT

Built In

FACTDOUBLE

Analysis ToolPak Maths

Returns the double factorial of a number

FALSE

Built In

Logical

Returns the logical value FALSE

FDIST

Built In

Statistical

Returns the F probability distribution

FIND

Built In

Text

Finds one text value within another (case sensitive)

FINDB

Built In

Text

Finds one text value within another (case sensitive) based on the number of bytes each character uses.

FINV

Built In

Statistical

Returns the inverse of the F probability distribution

FISHER

Built In

Statistical

Returns the Fisher transformation at x

FISHERINV

Built In

Statistical

Returns the inverse of the Fisher transformation

FIXED

Built In

Text

Formats a number as text with a fixed number of decimals

FLOOR

Built In

Maths

Rounds a number down towards 0 to the nearest integer or to the nearest multiple of significance

FORECAST

Built In

Statistical

Calculates a predicted value of y for a given x value based on known values for x and y

FREQUENCY

Built In

Statistical

Counts how often values occur within given ranges of values and returns those counts as a vertical array of numbers

FTEST

Built In

Statistical

Returns the result of an F-test; the one-tailed probability that the variances in array1 and array2 are not significantly different

FV

Built In

Financial

Returns the future value of an investment

FVSCHEDULE

Analysis ToolPak Financial

Returns the future value of an initial principal after applying a series of compound interest rates

GAMMADIST

Built In

Statistical

Returns the gamma distribution

GAMMAINV

Built In

Statistical

Returns the inverse of the gamma distribution

GAMMALN

Built In

Statistical

Returns the natural logarithm of the gamma function Γ(x).

GCD

Analysis ToolPak Maths

Returns the greatest common divisor of 2 - 29 integers

GEOMEAN

Built In

Returns the geometric mean of an array or range of positive data

GESTEP

Analysis ToolPak Engineering

GETPIVOTDATA Built In

Maths

Statistical

External

Returns the factorial of a number

Tests whether a number is greater than a threshold value Returns data stored in a pivot table

GROWTH

Built In

Statistical

Calculates predicted exponential growth and returns the y-values for a series of specified new x-values by using known x-values and yvalues

HARMEAN

Built In

Statistical

Returns the harmonic mean of a data set by calculating the reciprocal of the arithmetic mean of reciprocals

HEX2BIN

Analysis ToolPak Engineering

Converts a hexadecimal to a binary

HEX2DEC

Analysis ToolPak Engineering

Converts a hexadecimal to a decimal

HEX2OCT

Analysis ToolPak Engineering

Converts a hexadecimal to an octal

HLOOKUP

Built In

Lookup & Ref

Looks in the top row of a table or array and returns the value of the indicated cell

HOUR

Built In

Date & Time

Converts an Excel date / time serial number to an hour

HYPERLINK

Built In

Lookup & Ref

Creates a shortcut that opens a document on your hard drive, a server or the internet

HYPGEOMDIST

Built In

Statistical

Returns the hypergeometric distribution by calculating the probability of a given number of sample successes, given the sample size, population successes, and population size

IF

Built In

Logical

Returns one value specified condition evaluates to TRUE and another value if it evaluates to FALSE

IMABS

Analysis ToolPak Engineering

Returns the absolute value (modulus) of a complex number provided in the text format "x + yi" or "x + yj"

IMAGINARY

Analysis ToolPak Engineering

Returns the imaginary coefficient of a complex number provided in the text format "x + yi" or "x + yj"

IMARGUMENT

Analysis ToolPak Engineering

Returns the argument theta - an angle expressed in radians

IMCONJUGATE

Analysis ToolPak Engineering

Returns the complex conjugate of a complex number provided in the text format "x + yi" or "x + yj"

IMCOS

Analysis ToolPak Engineering

Returns the cosine of a complex number provided in the text format "x + yi" or "x + yj"

IMDIV

Analysis ToolPak Engineering

Returns the quotient of two complex numbers provided in the text format "x + yi" or "x + yj"

IMEXP

Analysis ToolPak Engineering

Returns the exponential of a complex number provided in the text format "x + yi" or "x + yj"

IMLN

Analysis ToolPak Engineering

Returns the natural logarithm of a complex number provided in the text format "x + yi" or "x + yj"

IMLOG10

Analysis ToolPak Engineering

Returns the base-10 logarithm of a complex number provided in the text format "x + yi" or "x + yj"

IMLOG2

Analysis ToolPak Engineering

Returns the base-2 logarithm of a complex number provided in the text format "x + yi" or "x + yj"

IMPOWER

Analysis ToolPak Engineering

Returns a complex number provided in the text format "x + yi" or "x + yj" raised to an integer number

IMPRODUCT

Analysis ToolPak Engineering

Returns the product of 2 - 29 complex numbers provided in the text format "x + yi" or "x + yj"

IMREAL

Analysis ToolPak Engineering

Returns the real coefficient of a complex number provided in the text format "x + yi" or "x + yj"

IMSIN

Analysis ToolPak Engineering

Returns the sine of a complex number provided in the text format "x + yi" or "x + yj"

IMSQRT

Analysis ToolPak Engineering

Returns the square root of a complex number provided in the text format "x + yi" or "x + yj"

IMSUB

Analysis ToolPak Engineering

Returns the difference of two complex numbers provided in the text format "x + yi" or "x + yj"

IMSUM

Analysis ToolPak Engineering

Returns the sum of 2 - 29 complex numbers provided in the text format "x + yi" or "x + yj"

INDEX

Built In

Lookup & Ref

Alternative forms. Array form returns a value or array of values. Reference form returns a reference.

INDIRECT

Built In

Lookup & Ref

Returns a reference indicated by a value provided as text

INFO

Built In

Information

returns information about the current operating environment

INT

Built In

Maths

Rounds a number away from 0 to the nearest integer

INTERCEPT

Built In

Statistical

Calculates from given x and y values the point at which a line will intersect the y-axis

INTRATE

Analysis ToolPak Financial

Returns the interest rate for a fully invested security using the specified or default day counting basis

IPMT

Built In

Financial

Returns the amount of the interest element in a payment for an investment for a given period

IRR

Built In

Financial

Returns the internal rate of return for a series of cash flows

ISBLANK

Built In

Information

Returns TRUE if the value is blank

ISERR

Built In

Information

Returns TRUE if the value is any error value except #N/A

ISERROR

Built In

Information

Returns TRUE if the value is any error value

ISEVEN

Analysis ToolPak Information

Returns TRUE if the number is even

ISLOGICAL

Built In

Returns TRUE if the value is a logical value

Information

ISNA

Built In

Information

Returns TRUE if the value is the #N/A error value

ISNONTEXT

Built In

Information

Returns TRUE if the value is not text

ISNUMBER

Built In

Information

Returns TRUE if the value is a number

ISODD

Analysis ToolPak Information

Returns TRUE if the number is odd

ISPMT

Built In

Financial

Returns the interest associated with a specific loan payment

ISREF

Built In

Information

Returns TRUE if the value is a reference

ISTEXT

Built In

Information

Returns TRUE if the value is text

JIS

Built In

Text

Changes half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters.

KURT

Built In

Statistical

Returns the kurtosis of a data set; a measure that compares the relative peakedness or flatness of a distribution compared with the normal distribution

LARGE

Built In

Statistical

Returns the kth largest value in a data set

LCM

Analysis ToolPak Maths

Returns the least common multiple of 1 - 29 integers

LEFT

Built In

Text

Returns the left most characters from a text value

LEFTB

Built In

Text

Returns the first character or characters in a text string, based on the number of bytes you specify.

LEN

Built In

Text

Returns the number of characters in a text string

LENB

Built In

Text

Returns the number of bytes used to represent the characters in a text string

LINEST

Built In

Statistical

Entered as an array formula, LINEST returns an array that describes a line of best fit by using the least squares method

LN

Built In

Maths

Returns the natural logarithm (base e = 2.71828182845904) of a number

LOG

Built In

Maths

Returns the logarithm of a number to a specified base

LOG10

Built In

Maths

Returns the base-10 logarithm of a number

LOGEST

Built In

Statistical

Entered as an array formula, LOGEST calculates an exponential curve that fits known data and returns an array of values that describes that curve

LOGINV

Built In

Statistical

Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with given probability, mean, and standard deviation

LOGNORMDIST Built In

Statistical

Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with known mean and standard deviation

LOOKUP

Built In

Lookup & Ref

Alternative forms. Vector form looks up values in a one row or column range and returns a value in a second one row or column range. Array form looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array

LOWER

Built In

Text

Converts text to lowercase

MATCH

Built In

Lookup & Ref

Returns the relative position of an item in an array that matches a specified value in a specified order

MAX

Built In

Statistical

Returns the maximum value in a list of arguments ignoring logical values and text

MAXA

Built In

Statistical

Returns the maximum value in a list of arguments including logical values and text

MDETERM

Built In

Maths

Returns the matrix determinant of an array

MDURATION

Analysis ToolPak Financial

Returns the Macauley modified duration for a security with an assumed par value of $100 using the specified or default day counting basis

MEDIAN

Built In

Statistical

Returns the median of the given numbers

MID

Built In

Text

Returns a specific number of characters from a string starting at a specified position

MIDB

Built In

Text

Returns a specific number of characters from a text string, starting at the position you specify, based on the number of bytes specified

MIN

Built In

Statistical

Returns the minimum value in a list of arguments ignoring logical values and text

MINA

Built In

Statistical

Returns the minimum value in a list of arguments including logical values and text

MINUTE

Built In

Date & Time

Converts an Excel date / time serial number to a minute

MINVERSE

Built In

Maths

Returns the matrix inverse of an array

MIRR

Built In

Financial

Returns the modified internal rate of return based on different finance and reinvestment rates for negative and positive cash flows

MMULT

Built In

Maths

Returns the matrix product of two arrays

MOD**

Built In

Maths

Returns the remainder from division with the result having the same sign as the divisor

MODE

Built In

Statistical

Returns the most common value in a data set

MONTH

Built In

MROUND

Analysis ToolPak Maths

Returns a number rounded to the desired multiple. Midway points are rounded away from 0

MULTINOMIAL

Analysis ToolPak Maths

Returns the ratio of the factorial of a sum of values to the product of factorials

N

Built In

Information

Returns a value converted to a number

NA

Built In

Information

Returns the error value #N/A

Statistical

Returns the negative binomial distribution; the probability that there will be number_f failures before the number_s-th success, when the constant probability of a success is probability_s

NEGBINOMDIST Built In

Date & Time

Converts an Excel date / time serial number to a month number

NETWORKDAYS Analysis ToolPak Date & Time

Returns the number of whole working days between two dates excluding specified holidays

NOMINAL

Analysis ToolPak Financial

Returns the nominal rate equivalent to a given annual effective with a given compounding frequency for the nominal rate

NORMDIST

Built In

Statistical

Returns the cumulative distribution function or probability mass function for the value x with specified mean and standard deviation

NORMINV

Built In

Statistical

Returns the inverse of the normal cumulative distribution for the specified probability, mean, and standard deviation

NORMSDIST

Built In

Statistical

Returns the standard normal cumulative distribution function. The distribution has a mean of 0 and a standard deviation of 1

NORMSINV

Built In

Statistical

Returns the inverse of the standard normal cumulative distribution

NOT

Built In

Logical

Reverses the logic of its argument

NOW

Built In

Date & Time

Returns the Excel date / time serial number of the current date and time

NPER

Built In

Financial

Returns the number of periods for an investment

NPV

Built In

Financial

Returns the net present value of an investment based upon a series of periodic cash flows and a discount rate where the first cash flow is received at the end of the first period

OCT2BIN

Analysis ToolPak Engineering

Converts an octal number to binary

OCT2DEC

Analysis ToolPak Engineering

Converts an octal number to decimal

OCT2HEX

Analysis ToolPak Engineering

Converts an octal number to hexadecimal

ODD

Built In

Rounds a number away from 0 to the nearest odd integer

Maths

ODDFPRICE

Analysis ToolPak Financial

Returns the price per $100 face value of a security with an odd first period using the specified or default day counting basis

ODDFYIELD

Analysis ToolPak Financial

Returns the yield of a security with an odd first period using the specified or default day counting basis

ODDLPRICE

Analysis ToolPak Financial

Returns the price per $100 face value of a security with an odd last period using the specified or default day counting basis

ODDLYIELD

Analysis ToolPak Financial

Returns the yield of a security with an odd last period using the specified or default day counting basis

OFFSET

Built In

Lookup & Ref

Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells

OR

Built In

Logical

ReturnsTRUE if any argument is TRUE

PEARSON

Built In

Statistical

Returns the Pearson product moment correlation coefficient

PERCENTILE

Built In

Statistical

Returns the kth percentile of values in a range

PERCENTRANK Built In

Statistical

Returns the percentage rank of a value in a data set

PERMUT

Built In

Statistical

Returns the number of permutations for a given number of objects that can be selected from a number of objects without replacement

PHONETIC

Built In

Text

Extracts the phonetic (furigana) characters from a text string

PI

Built In

Maths

Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits

PMT

Built In

Financial

Returns the periodic payment for an annuity

POISSON

Built In

Statistical

Returns the Poisson distribution

POWER

Built In

Maths

Returns the result of a number raised to a power

PPMT

Built In

Financial

Returns the amount of principal element in a payment for an investment for a given period

PRICE

Analysis ToolPak Financial

Returns the price per $100 face value of a security that pays periodic interest using the specified or default day counting basis

PRICEDISC

Analysis ToolPak Financial

Returns the price per $100 face value of a discounted security using the specified or default day counting basis

PRICEMAT

Analysis ToolPak Financial

Returns the price per $100 face value of a security that pays interest at maturity using the specified or default day counting basis

PROB

Built In

Statistical

Returns either the probability that values in a range are between two limits or if upper_limit is not specified, the probability that values in x_range are equal to lower_limit

PRODUCT

Built In

Maths

Multiplies together 1 - 30 numbers

PROPER

Built In

Text

Capitalizes the first letter in each word of a text value

PV

Built In

Financial

Returns the present value of an investment

QUARTILE

Built In

Statistical

Returns the quartile of a data set

QUOTIENT

Analysis ToolPak Maths

Returns the integer portion of a division

RADIANS

Built In

Maths

Converts degrees to radians

RAND

Built In

Maths

Returns an evenly distributed random number greater than or equal to 0 and less than 1

RANDBETWEEN Analysis ToolPak Maths

Returns a random number between (and inclusive of) two specified numbers

RANK

Built In

Statistical

Returns the rank of a number in a list of numbers

RATE

Built In

Financial

Returns the interest rate per period of an annuity

RECEIVED

Analysis ToolPak Financial

Returns the amount received at maturity for a fully invested security using the specified or default day counting basis

REGISTER.ID

Built In

External

Returns the register ID of the specified dynamic link library (DLL) or code resource that has been previously registered

REPLACE

Built In

Text

Replaces characters within text

REPLACEB

Built In

Text

Replaces part of a text string, based on the number of bytes you specify, with a different text string.

REPT

Built In

Text

Repeats text a given number of times

RIGHT

Built In

Text

Returns the rightmost characters from a text value

RIGHTB

Built In

Text

Returns the last character or characters in a text string, based on the number of bytes you specify

ROMAN

Built In

Maths

Converts an arabic number to Roman, as text

ROUND

Built In

Maths

Rounds a number to a specified number of digits to the left (-) or right (+) of the decimal point. The midway digit 5 is rounded away from 0.

ROUNDDOWN

Built In

Maths

Rounds a number down towards 0 to a specified number of digits to the left (-) or right (+) of the decimal point

ROUNDUP

Built In

Maths

Round a number up away from 0 to a specified number of digits to the left (-) or right (+) of the decimal point

ROW

Built In

Lookup & Ref

Returns the row number of a reference

ROWS

Built In

Lookup & Ref

Returns the number of rows in a reference

RSQ

Built In

Statistical

Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's

RTD

Built In

Lookup & Ref

Retrieves real-time data from a program that supports COM automation

SEARCH

Built In

Text

Finds one text value within another (not case sensitive) and returns the number of the starting position

SEARCHB

Built In

Text

Finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text. The result is based on the number of bytes each character uses, beginning with start_num

SECOND

Built In

Date & Time

Converts an Excel date / time serial number to a second

SERIESSUM

Analysis ToolPak Maths

Returns the sum of a power series expansion

SIGN

Built In

Maths

Returns 1 for positve numbers, 0 if the number is 0 and, -1 if the number is negative

SIN

Built In

Maths

Returns the sine of a given angle given in radians

SINH

Built In

Maths

Returns the hyperbolic sine of a given angle

SKEW

Built In

Statistical

Returns the skewness of a distribution

SLN

Built In

Financial

Returns the straight-line depreciation of an asset for one period.

SLOPE

Built In

Statistical

Returns the slope (vertical distance / horizontal distance) of the linear regression line through data points in known_y's and known_x's

SMALL

Built In

Statistical

Returns the kth smallest value in a data set

SQL.REQUEST

ODBC

External

Connects with an external data source, and runs a query from a worksheet. SQL.REQUEST then returns the result as an array

SQRT

Built In

Maths

Returns a positive square root

SQRTPI

Analysis ToolPak Maths

Returns the square root of a number multiplied by pi

STANDARDIZE

Built In

Returns a normalized value from a distribution with known mean and standard_dev

Statistical

STDEV

Built In

Statistical

Estimates standard distribution based on a sample ignoring text and logical values

STDEVA

Built In

Statistical

Estimates standard deviation based on a sample including text and logical values

STDEVP

Built In

Statistical

Calculates standard distribution based on the entire population ignoring text and logical values

STDEVPA

Built In

Statistical

Calculates standard deviation based on the entire population including text and logical values

STEYX

Built In

Statistical

Returns the standard error of the predicted yvalue for each x in the regression

SUBSTITUTE

Built In

Text

Substitutes new text for old text in a text string

SUBTOTAL

Built In

Maths

Returns the sutotal in a list or database

SUM

Built In

Maths

Adds its arguments

SUMIF

Built In

Maths

Add the cells specified by a given criteria

SUMPRODUCT

Built In

Maths

Returns the sum of the products of corresponding array components

SUMSQ

Built In

Maths

Returns the sum of the squares of 1 - 30 numbers

SUMX2MY2

Built In

Maths

Returns the sum of the difference of squares of corresponding values in two arrays

SUMX2PY2

Built In

Maths

Returns the sum of the sum of squares of corresponding values in two arrays

SUMXMY2

Built In

Maths

Returns the sum of squares of differences of corresponding values in two array

SYD

Built In

Financial

Returns the sum of years' digits depreciation of an asset for a specified period

T

Built In

Text

Converts its arguments to text

TAN

Built In

Maths

Returns the tangent of a given angle given in radians

TANH

Built In

Maths

Returns the hyperbolic tangent of a number

TBILLEQ

Analysis ToolPak Financial

Returns the bond-equivalent yield for a treasury bill

TBILLPRICE

Analysis ToolPak Financial

Returns the price per $100 face value for a Treasury bill

TBILLYIELD

Analysis ToolPak Financial

Returns the yield for a treasury bill

TDIST

Built In

Statistical

Returns the probability for the Student tdistribution where a numeric value (x) is a calculated value of t for which the probability is to be computed

TEXT

Built In

Text

Formats a number and converts it to text

TIME

Built In

Date & Time

Returns the decimal portion of an Excel date / time serial number for a particular time

TIMEVALUE

Built In

Date & Time

Converts the time in an acceptable form of text enclosed in quotation marks to the decimal portion of an Excel date / time serial number

TINV

Built In

Statistical

Returns the t-value of the Student's tdistribution based on given probability and degrees of freedom

TODAY

Built In

Date & Time

Returns the Excel date / time serial number of today's date

TRANSPOSE

Built In

Lookup & Ref

Returns the transpose of an array

TREND

Built In

Statistical

Entered as an array formula, TREND fits a straight line using the least squares method to arrays of known_y's and known_x's and returns the y-values along that line for the array of specified new_x's

TRIM

Built In

Text

Removes all spaces from text except single spaces between words

TRIMMEAN

Built In

Statistical

Calculates the mean by excluding a specified percentage of data points from the top and bottom tails of a data set

TRUE

Built In

Logical

Returns the logical value TRUE

TRUNC

Built In

Maths

Truncates a number to an integer or to specified precision by removing the fractional part of the number. (Serves to round down towards 0)

TTEST

Built In

Statistical

Returns the probability associated with a student's t-test

TYPE

Built In

Information

Returns the type of value (1 for value is a number; 2 for value is text; 4 for value is logical value; 16 for value is error value; 64 for value is array)

UPPER

Built In

Text

Converts text to uppercase

VALUE

Built In

Text

Converts a text argument to a number

VAR

Built In

Statistical

Estimates variance based on a sample ignoring logical values and text

VARA

Built In

Statistical

Estimates variance based on a sample including text and logical values

VARP

Built In

Statistical

Calculates variance based on the entire population ignoring text and logical values

VARPA

Built In

Statistical

Calculates variance based on the entire population including text and logical values

VDB

Built In

Financial

Returns the depreciation of an asset for a specified or partial period using a variable declining balance method

VLOOKUP

Built In

Lookup & Ref

Locates a specified value in the leftmost column of a specified table, and returns the value in the same row from a specified column in the table

WEEKDAY

Built In

Date & Time

Converts an Excel date / time serial number to the number of the day of the week based upon counting system of return_type

WEEKNUM

Analysis ToolPak Date & Time

Returns the weeknumber in the year based upon return_type counting basis

WEIBULL

Built In

Returns the Weibull distribution

WORKDAY

Analysis ToolPak Date & Time

Returns the Excel date / time serial number of the date before or after a specified number of workdays excluding holidays

XIRR

Analysis ToolPak Financial

Returns the annual effective interest rate for a schedule of cash flows received at specified dates

XNPV

Analysis ToolPak Financial

Returns the net present value for a schedule of cash flows received at specified dates

YEAR

Built In

Date & Time

Converts an Excel date / time serial number to a year

YEARFRAC

Analysis ToolPak Date & Time

Returns the difference between start_date and end_date expressed as a number of years including decimal fraction of a year.

YEN

Built In

Converts a number to text, using the ¥ (yen) currency format, with the number rounded to a specified place

YIELD

Analysis ToolPak Financial

Returns the yield on a security that pays periodic interest using the specified or default day counting basis

YIELDDISC

Analysis ToolPak Financial

Returns the annual yield for a discounted security using the specified or default day counting basis.

YIELDMAT

Analysis ToolPak Financial

Returns the annual yield of a security that pays interest at maturity using the specified or default day counting basis

ZTEST

Built In

Returns the two-tailed P-value of a z-test

Notes:

** The Excel MOD function uses a different definition of MOD to that used by VBA Mod function insofar as negative numbers are conc

Statistical

Text

Statistical

XCEL FUNCTIONS Syntax =ABS(Number) =ACCRINT(Issue,First_Interest,Settlement,Rate, Par,Frequency,Basis) =ACCRINTM(Issue,Settlement,Rate,Par,Basis) =ACOS(Number) =ACOSH(Number) =ADDRESS(Row_num,Column_num,Abs_num, A1,Sheet_text) =AMORDEGRC(Cost,Date_purchased,First_period, Salvage,Period,Rate,Basis)

=AMORLINC(Cost,Date_purchased,First_period, Salvage,Period,Rate,Basis)

=AND(Logical1,Logical2,…) =AREAS(reference) =ASC(Text)

=ASIN(Number) =ASINH(Number) =ATAN(Number) =ATAN2(x_num,y_num) =ATANH(Number) =AVEDEV(Number1,Number2,…) =AVERAGE(Number1,Number2,…) =AVERAGEA(Value1,Value2,…)

=BAHTTEXT(number) =BESSELI(x,n) =BESSELJ(x,n) =BESSELK(x,n)

=BESSELY(x,n) =BETADIST(x,alpha,beta,A,B) =BETAINV(probability,alpha,beta,A,B) =BIN2DEC(Number) =BIN2HEX(Number,Places) =BIN2OCT(Number,Places) =BINOMDIST(number_s,trials,probability_s, cumulative) =CALL(register_id,argument1,...) With Register.id =CALL(module_text,procedure,type_text, argument1,...) Excel for Windows =CALL(file_text,resource,type_text, argument1,...) Excel for Macintosh =CEILING(number,significance)

=CELL(info_type,reference)

=CHAR(Number)

=CHIDIST(x,degrees_freedom) =CHIINV(probability,degrees_freedom) =CHITEST(actual_range,expected_range)

=CHOOSE(index_num,value1,value2,...) =CLEAN(text) =CODE(text) =COLUMN(reference) =COLUMNS(array) =COMBIN(number,number_chosen) =COMPLEX(real_num,i_num,suffix)

=CONCATENATE (text1,text2,...) =CONFIDENCE(alpha,standard_dev,size)

=CONVERT(number,from_unit,to_unit) =CORREL(array1,array2) =COS(Number) =COSH(Number) =COUNT(value1,value2, ...)

=COUNTA(value1,value2, ...) =COUNTBLANK(range) =COUNTIF(range,criteria) =COUPDAYBS(settlement,maturity, frequency,basis)

=COUPDAYS(settlement,maturity, frequency,basis)

=COUPDAYSNC(settlement,maturity, frequency,basis)

=COUPNCD(settlement,maturity,frequency,basis)

=COUPNUM(settlement,maturity,frequency,basis)

=COUPPCD(settlement,maturity,frequency,basis)

=COVAR(array1,array2)

=CRITBINOM(trials,probability_s,alpha)

=CUMIPMT(rate,nper,pv,start_period,end_period, type) =CUMPRINC(rate,nper,pv,start_period,end_period, type) =DATE(year,month,day)

=DATEDIF(start_date,end_date,unit)

=DATEVALUE(date_text) =DAVERAGE(database,field,criteria) =DAY(serial_number) =DAYS360(start_date,end_date,method)

=DB(cost,salvage,life,period,month)

=DCOUNT(database,field,criteria)

=DCOUNTA(database,field,criteria) =DDB(cost,salvage,life,period,factor)

=DEC2BIN(Number) =DEC2HEX(Number) =DEC2OCT(Number) =DEGREES(angle) =DELTA(number1,number2) =DEVSQ(number1,number2,...) =DGET(database,field,criteria) =DISC(settlement,maturity,pr,redemption,basis)

=DMAX(database,field,criteria) =DMIN(database,field,criteria) =DOLLAR(number,decimals) =DOLLARDE(fractional_dollar,fraction)

=DOLLARFR(decimal_dollar,fraction)

=DPRODUCT(database,field,criteria)

=DSTDEV(database,field,criteria)

=DSTDEVP(database,field,criteria)

=DSUM(database,field,criteria)

=DURATION(settlement,maturity,coupon,yld, frequency,basis)

=DVAR(database,field,criteria)

=DVARP(database,field,criteria)

=EDATE(start_date,months)

=EFFECT(nominal_rate,npery)

=EOMONTH(start_date,months)

=ERF(lower_limit,upper_limit) =ERFC(x) =ERROR.TYPE(error_val) =EUROCONVERT(number,source,target, full_precision,triangulation_precision)

=EVEN(Number) =EXACT(text1,text2) =EXP(Number) =EXPONDIST(x,lambda,cumulative)

=FACT(Number) =FACTDOUBLE(number) =FALSE() =FDIST(x,degrees_freedom1, degrees_freedom2) =FIND(find_text,within_text,start_num) =FINDB(find_text,within_text,start_num)

=FINV(probability,degrees_freedom1, degrees_freedom2) =FISHER(x) =FISHERINV(y) =FIXED(number,decimals,no_commas) =FLOOR(number,significance)

=FORECAST(x,known_y's,known_x's)

=FREQUENCY(data_array,bins_array)

=FTEST(array1,array2)

=FV(rate,nper,pmt,pv,type) =FVSCHEDULE(principal,schedule)

=GAMMADIST(x,alpha,beta,cumulative) =GAMMAINV(probability,alpha,beta) =GAMMALN(x) =GCD(number1,number2, ...) =GEOMEAN(number1,number2, ...) =GESTEP(number,step) =GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,...)

=GROWTH(known_y's,known_x's,new_x's,const)

=HARMEAN(number1,number2, ...)

=HEX2BIN(Number,Places) =HEX2DEC(Number) =HEX2OCT(Number,Places) =HLOOKUP(lookup_value,table_array, row_index_num,range_lookup) =HOUR(serial_number) =HYPERLINK(link_location,friendly_name) =HYPGEOMDIST(sample_s,number_sample, population_s,number_population)

=IF(logical_test,value_if_true,value_if_false)

=IMABS(inumber)

=IMAGINARY(inumber)

=IMARGUMENT(inumber) =IMCONJUGATE(inumber)

=IMCOS(inumber)

=IMDIV(inumber1,inumber2)

=IMEXP(inumber)

=IMLN(inumber)

=IMLOG10(inumber)

=IMLOG2(inumber)

=IMPOWER(inumber,number)

=IMPRODUCT(inumber1,inumber2,...)

=IMREAL(inumber)

=IMSIN(inumber)

=IMSQRT(inumber)

=IMSUB(inumber1,inumber2)

=IMSUM(inumber1,inumber2,...)

=INDEX(array,row_num,column_num) =INDEX(reference,row_num,column_num, area_num) =INDIRECT(ref_text,a1) =INFO(type_text) =INT(Number) =INTERCEPT(known_y's,known_x's)

=INTRATE(settlement,maturity,investment, redemption,basis)

=IPMT(rate,per,nper,pv,fv,type)

=IRR(values,guess) =ISBLANK(Value) =ISERR(Value) =ISERROR(Value) =ISEVEN(Number) =ISLOGICAL(Value)

=ISNA(Value) =ISNONTEXT(Value) =ISNUMBER(Value) =ISODD(Number) =ISPMT(rate,per,nper,pv) =ISREF(Value) =ISTEXT(Value) =JIS(text)

=KURT(number1,number2, ...)

=LARGE(array,k) =LCM(number1,number2, ...) =LEFT(text,num_chars) =LEFTB(text,num_bytes)

=LEN(text) =LENB(text) =LINEST(known_y's,known_x's,const,stats)

=LN(Number) =LOG(Number,base) =LOG10(Number) =LOGEST(known_y's,known_x's,const,stats)

=LOGINV(probability,mean,standard_dev)

=LOGNORMDIST(x,mean,standard_dev)

=LOOKUP(lookup_value,lookup_vector, result_vector) =LOOKUP(lookup_value,array)

=LOWER(text) =MATCH(lookup_value,lookup_array, match_type)

=MAX(number1,number2,...) =MAXA(value1,value2,...) =MDETERM(array) =MDURATION(settlement,maturity,coupon,yld, frequency,basis)

=MEDIAN(number1,number2, ...) =MID(text,start_num,num_chars) =MIDB(text,start_num,num_bytes)

=MIN(number1,number2,...) =MINA(value1,value2,...) =MINUTE(serial_number) =MINVERSE(array) =MIRR(values,finance_rate,reinvest_rate)

=MMULT(array1,array2) =MOD(number,divisor)

=MODE(number1,number2,...)

=MONTH(serial_number) =MROUND(number,multiple)

=MULTINOMIAL(number1,number2, ...) =N(Value) =NA() =NEGBINOMDIST(number_f,number_s, probability_s)

=NETWORKDAYS(start_date,end_date,holidays)

=NOMINAL(effect_rate,npery)

=NORMDIST(x,mean,standard_dev,cumulative)

=NORMINV(probability,mean,standard_dev)

=NORMSDIST(z)

=NORMSINV(probability) =NOT(Logical) =NOW() =NPER(rate, pmt, pv, fv, type) =NPV(rate,value1,value2, ...)

=OCT2BIN(number,places) =OCT2DEC(number) =OCT2HEX(number,places) =ODD(number)

=ODDFPRICE(settlement,maturity,issue, first_coupon,rate,yld,redemption, frequency,basis) =ODDFYIELD(settlement,maturity,issue, first_coupon,rate,pr,redemption, frequency,basis) =ODDLPRICE(settlement,maturity,last_interest, rate,yld,redemption,frequency,basis)

=ODDLYIELD(settlement,maturity,last_interest, rate,pr,redemption,frequency,basis)

=OFFSET(reference,rows,cols,height,width)

=OR(logical1,logical2,...) =PEARSON(array1,array2) =PERCENTILE(array,k) =PERCENTRANK(array,x,significance) =PERMUT(number,number_chosen)

=PHONETIC(reference) =PI()

=PMT(rate,nper,pv,fv,type) =POISSON(x,mean,cumulative) =POWER(number,power) =PPMT(rate,per,nper,pv,fv,type)

=PRICE(settlement,maturity,rate,yld,redemption, frequency,basis)

=PRICEDISC(settlement,maturity,discount, redemption,basis)

=PRICEMAT(settlement,maturity,issue,rate, yld,basis)

=PROB(x_range,prob_range,lower_limit, upper_limit)

=PRODUCT(number1,number2, ...) =PROPER(text) =PV(rate,nper,pmt,fv,type) =QUARTILE(array,quart) =QUOTIENT(numerator,denominator) =RADIANS(angle) =RAND()

=RANDBETWEEN(bottom,top) =RANK(number,ref,order) =RATE(nper,pmt,pv,fv,type,guess) =RECEIVED(settlement,maturity,investment, discount,basis)

=REGISTER.ID(module_text,procedure,type_text)

=REPLACE(old_text,start_num,num_chars, new_text) =REPLACEB(old_text,start_num,num_bytes, new_text)

=REPT(text,number_times) =RIGHT(text,num_chars) =RIGHTB(text,num_bytes)

=ROMAN(number,form) =ROUND(number,num_digits)

=ROUNDDOWN(number,num_digits)

=ROUNDUP(number,num_digits)

=ROW(reference) =ROWS(array) =RSQ(known_y's,known_x's)

=RTD(ProgID,server,topic1,[topic2],...) =SEARCH(find_text,within_text,start_num)

=SEARCHB(find_text,within_text,start_num)

=SECOND(serial_number) =SERIESSUM(x,n,m,coefficients) =SIGN(number)

=SIN(number) =SINH(number) =SKEW(number1,number2,...) =SLN(cost,salvage,life) =SLOPE(known_y's,known_x's)

=SMALL(array,k) =SQL.REQUEST(connection_string,output_ref, driver_prompt,query_text,col_names_logical)

=SQRT(number) =SQRTPI(number) =STANDARDIZE(x,mean,standard_dev)

=STDEV(number1,number2,...) =STDEVA(value1,value2,...) =STDEVP(number1,number2,...)

=STDEVPA(value1,value2,...)

=STEYX(known_y's,known_x's) =SUBSTITUTE(text,old_text,new_text, instance_num) =SUBTOTAL(function_num,ref1,ref2,...) =SUM(number1,number2, ...) =SUMIF(range,criteria,sum_range) =SUMPRODUCT(array1,array2,array3, ...) =SUMSQ(number1,number2, ...) =SUMX2MY2(array_x,array_y)

=SUMX2PY2(array_x,array_y) =SUMXMY2(array_x,array_y)

=SYD(cost,salvage,life,per)

=T(value) =TAN(number) =TANH(number) =TBILLEQ(settlement,maturity,discount) =TBILLPRICE(settlement,maturity,discount) =TBILLYIELD(settlement,maturity,pr) =TDIST(x,degrees_freedom,tails)

=TEXT(value,format_text)

=TIME(hour,minute,second)

=TIMEVALUE(time_text)

=TINV(probability,degrees_freedom)

=TODAY() =TRANSPOSE(array) =TREND(known_y's,known_x's,new_x's,const)

=TRIM(text) =TRIMMEAN(array,percent)

=TRUE() =TRUNC(number,num_digits)

=TTEST(array1,array2,tails,type) =TYPE(value)

=UPPER(text) =VALUE(text) =VAR(number1,number2,...) =VARA(value1,value2,...) =VARP(number1,number2,...) =VARPA(value1,value2,...) =VDB(cost,salvage,life,start_period,end_period, factor,no_switch)

=VLOOKUP(lookup_value,table_array, col_index_num,range_lookup)

=WEEKDAY(serial_number,return_type)

=WEEKNUM(serial_num,return_type) =WEIBULL(x,alpha,beta,cumulative) =WORKDAY(start_date,days,holidays)

=XIRR(values,dates,guess)

=XNPV(rate,values,dates)

=YEAR(serial_number) =YEARFRAC(start_date,end_date,basis)

=YEN(number,decimals)

=YIELD(settlement,maturity,rate,pr,redemption, frequency,basis)

=YIELDDISC(settlement,maturity,pr,redemption, basis)

=YIELDMAT(settlement,maturity,issue,rate,pr, basis)

=ZTEST(array,x,sigma)

t used by VBA Mod function insofar as negative numbers are concerned.

Database Functions Name

Source

Description

DAVERAGE

Built In

Returns the average of selected list or database entries based on specified criteria

DCOUNT*

Built In

Counts the cells containing numbers from a specified database that match specified criteria

DCOUNTA*

Built In

Counts non blank cells from a specified database that match specified criteria criteria

DGET

Built In

Extracts from a specified database a single value that matches specified criteria

DMAX

Built In

Extracts maximum number in a column of a list or database that matches specified conditions

DMIN

Built In

Extracts minimum number in a column of a list or database that matches specified conditions

DPRODUCT

Built In

Multiplies the values in a particular field of records that match the specified criteria in a database

DSTDEV

Built In

Estimates standard deviation of a population based on a sample using numbers in a column of a list or database that match specified conditions

DSTDEVP

Built In

Calculates the standard deviation based on the entire population using numbers in a column of a list or database that match specified criteria

DSUM

Built In

Adds the numbers in the field column of records in the database that match the specified criteria

DVAR

Built In

Estimates the variance of a population based on a sample by using the numbers in a column of a list or database that match specified criteria

DVARP

Built In

Calculates the variance of a population based on the entire population by using the numbers in a column of a list or database that match specified criteria

Notes:

* See also COUNT, COUNTA and FREQUENCY (Statistical) and COUNTIF (Maths) and COUNTBLANK (Information)

Database Function Arguments Argument

Description

Database

The range of cells that makes up the list or database

Field

Indicates which column is used in the function as text in inverted commas or as number of field

Criteria

The range of cells that contains the conditions you specify

base Functions Syntax =DAVERAGE(database,field,criteria) =DCOUNT(database,field,criteria)

=DCOUNTA(database,field,criteria)

=DGET(database,field,criteria) =DMAX(database,field,criteria)

=DMIN(database,field,criteria)

=DPRODUCT(database,field,criteria)

=DSTDEV(database,field,criteria)

=DSTDEVP(database,field,criteria)

=DSUM(database,field,criteria)

=DVAR(database,field,criteria)

=DVARP(database,field,criteria)

Statistical) and COUNTIF (Maths) and COUNTBLANK (Information)

Function Arguments

p the list or database

n the function as text in inverted commas or as number of field

the conditions you specify

Date & Time Functions Name

Source

Description

DATE

Built In

Returns the sequential Excel date / time serial number that represents a particular date

DATEDIF*

Analysis ToolPak Calculates differences between two dates in terms of specified units and assumptions

DATEVALUE

Built In

Converts a date text form to an Excel date / time serial number

DAY

Built In

Converts an Excel date / time serial number to the day of a month

DAYS360

Built In

Calculates the number of days between two dates using a specified 30 day month 360 day year method

EDATE

Analysis ToolPak Returns the Excel date / time serial number of the date that is the indicated number of months before or after the specified number of months from the start_date

EOMONTH

Analysis ToolPak Returns the Excel date / time serial number of the last day of the month before or after a specified number of months from start_date

HOUR

Built In

Converts an Excel date / time serial number to an hour

MINUTE

Built In

Converts an Excel date / time serial number to a minute

MONTH

Built In

Converts an Excel date / time serial number to a month number

NETWORKDAYS Analysis ToolPak Returns the number of whole working days between two dates excluding specified holidays NOW

Built In

Returns the Excel date / time serial number of the current date and time

SECOND

Built In

Converts an Excel date / time serial number to a second

TIME

Built In

Returns the decimal portion of an Excel date / time serial number for a particular time

TIMEVALUE

Built In

Converts the time in an acceptable form of text enclosed in quotation marks to the decimal portion of an Excel date / time serial number

TODAY

Built In

Returns the Excel date / time serial number of today's date

WEEKDAY

Built In

Converts an Excel date / time serial number to the number of the day of the week based upon counting system of return_type

WEEKNUM

Analysis ToolPak Returns the weeknumber in the year based upon return_type counting basis

WORKDAY

Analysis ToolPak Returns the Excel date / time serial number of the date before or after a specified number of workdays excluding holidays

YEAR

Built In

Converts an Excel date / time serial number to a year

YEARFRAC

Analysis ToolPak Returns the difference between start_date and end_date expressed as a number of years including decimal fraction of a year.

Notes:

* See Appendix 2. DATEDIF really does exist! It is an Excel built in function. It doesn't appear in the listings in the function wizard and the only documentation reference is in the Help files for Excel 2000.

Date and Time Function Arguments Argument

Description

Basis (For Securities Functions)

The type of day count basis to use: 0 or omitted = US(NASD) 30/360 1 = Actual / Actual 2 = Actual / 360 3 = Actual / 365 4 = European 30/360

Date_text

A date enclosed in quotation marks that is in a date text form acceptable to Excel. Note that acceptable date forms vary with system and Excel settings.

Day

A number representing the day of the month. If day is greater than the number of days in the month specified, day aggregates the month and year arguments appropriately and day represents the balance after this aggregation

Days

The number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date

End_date

The last date as an acceptable date in inverted commas or as a serial number

Holidays

An optional range of one or more dates to exclude from the working calendar. The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates

Hour

A number from 0 to 32767 representing the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value

Method

FALSE or omitted = US (NASD) 30/360 method; TRUE = European 30/360 method

Minute

A number from 0 to 32767 representing the minute. Any value greater than 59 will be converted to hours and minutes

Month (For DATE function)

A number representing the month of the year. If month is greater than 12, month adds that number of months to the first month in the year specified

Months

The number of months before or after start_date

Return_type (WEEKDAY only)

A number that determines the type of return value: 1 (or omitted) 1 = Sunday; 2 = 1 = Monday; 3 = 0 = Monday

Return_type (WEEKNUM only)

A number that determines on which day the week begins; 1 (or omitted) = starts Sunday nums 1 to 7; 2 = starts Monday nums 1 to 7

Second

A number from 0 to 32767 representing the second. Any value greater than 59 will be converted to hours, minutes, and seconds

Serial_num

A date / time serial number. Dates should be entered by using the DATE function, or as results of other formulas or functions

Serial_Number

An Excel date / time serial number. Dates should be entered by using the DATE function, or as results of other formulas or functions. The Excel date serial number varies according to setting of 1900 or 1904 date system in Tools > Options > Calculation.

Start_date

The first date as an acceptable date in inverted commas or as a serial number

Time_text

A text string enclosed in quotation marks that represents a time in any one of the Excel time formats

Unit

"y" = years; "m" = months; "d" = days; "md" = days ignoring months and years; "ym" = years and months; "yd" = years and days

Year

The year number as one to four digits

& Time Functions Syntax =DATE(year,month,day)

=DATEDIF(start_date,end_date,unit) =DATEVALUE(date_text) =DAY(serial_number) =DAYS360(start_date,end_date,method)

=EDATE(start_date,months)

=EOMONTH(start_date,months)

=HOUR(serial_number) =MINUTE(serial_number) =MONTH(serial_number) =NETWORKDAYS(start_date,end_date,holidays)

=NOW() =SECOND(serial_number) =TIME(hour,minute,second)

=TIMEVALUE(time_text)

=TODAY() =WEEKDAY(serial_number,return_type)

=WEEKNUM(serial_num,return_type) =WORKDAY(start_date,days,holidays)

=YEAR(serial_number) =YEARFRAC(start_date,end_date,basis)

an Excel built in function. It doesn't appear in the listings in the function he Help files for Excel 2000.

me Function Arguments

se: 0 or omitted = US(NASD) 30/360 360 an 30/360

rks that is in a date text form acceptable to Excel. Note that acceptable date el settings.

of the month. If day is greater than the number of days in the month onth and year arguments appropriately and day represents the balance

d nonholiday days before or after start_date. A positive value for days yields ields a past date

date in inverted commas or as a serial number

e dates to exclude from the working calendar. The list can be either a range r an array constant of the serial numbers that represent the dates

senting the hour. Any value greater than 23 will be divided by 24 and the hour value 30/360 method; TRUE = European 30/360 method

senting the minute. Any value greater than 59 will be converted to hours

th of the year. If month is greater than 12, month adds that number of year specified

r after start_date

pe of return value: 1 (or omitted) 1 = Sunday; 2 = 1 = Monday; 3 = 0 =

hich day the week begins; 1 (or omitted) = starts Sunday nums 1 to 7; 2 =

senting the second. Any value greater than 59 will be converted to hours,

es should be entered by using the DATE function, or as results of other

ber. Dates should be entered by using the DATE function, or as results of Excel date serial number varies according to setting of 1900 or 1904 date culation.

date in inverted commas or as a serial number

on marks that represents a time in any one of the Excel time formats

days; "md" = days ignoring months and years; "ym" = years and months;

digits

Engineering Functions Name

Source

BESSELI

Analysis ToolPak Returns the Bessel function evaluated for purely imaginary arguments

Description

BESSELJ

Analysis ToolPak Returns the Bessel function represented by Jn(x)

BESSELK

Analysis ToolPak Returns the modified Bessel function represented by Kn(x)

BESSELY

Analysis ToolPak Returns the Bessel / Weber / Neumann function Yn(x)

BIN2DEC

Analysis ToolPak Converts binary number to decimal

BIN2HEX

Analysis ToolPak Converts binary number to hexadecimal

BIN2OCT

Analysis ToolPak Converts binary number to octal

COMPLEX

Analysis ToolPak Converts real and imaginary coefficients into complex numbers of the form x + yi or x + yj depending upon suffix

CONVERT

Analysis ToolPak Converts a number from one measurement system to another

DEC2BIN

Analysis ToolPak Converts a decimal number to binary

DEC2HEX

Analysis ToolPak Converts a decimal number to hexadecimal

DEC2OCT

Analysis ToolPak Converts a decimal number to octal

DELTA

Analysis ToolPak Test whether two values are equal

ERF

Analysis ToolPak Returns the error function integrated between lower_limit and upper_limit

ERFC

Analysis ToolPak Returns the complementary ERF function integrated between x and infinity

GESTEP

Analysis ToolPak Tests whether a number is greater than a threshold value

HEX2BIN

Analysis ToolPak Converts a hexadecimal to a binary

HEX2DEC

Analysis ToolPak Converts a hexadecimal to a decimal

HEX2OCT

Analysis ToolPak Converts a hexadecimal to an octal

IMABS

Analysis ToolPak Returns the absolute value (modulus) of a complex number provided in the text format "x + yi" or "x + yj"

IMAGINARY

Analysis ToolPak Returns the imaginary coefficient of a complex number provided in the text format "x + yi" or "x + yj"

IMARGUMENT

Analysis ToolPak Returns the argument theta - an angle expressed in radians

IMCONJUGATE

Analysis ToolPak Returns the complex conjugate of a complex number provided in the text format "x + yi" or "x + yj"

IMCOS

Analysis ToolPak Returns the cosine of a complex number provided in the text format "x + yi" or "x + yj"

IMDIV

Analysis ToolPak Returns the quotient of two complex numbers provided in the text format "x + yi" or "x + yj"

IMEXP

Analysis ToolPak Returns the exponential of a complex number provided in the text format "x + yi" or "x + yj"

IMLN

Analysis ToolPak Returns the natural logarithm of a complex number provided in the text format "x + yi" or "x + yj"

IMLOG10

Analysis ToolPak Returns the base-10 logarithm of a complex number provided in the text format "x + yi" or "x + yj"

IMLOG2

Analysis ToolPak Returns the base-2 logarithm of a complex number provided in the text format "x + yi" or "x + yj"

IMPOWER

Analysis ToolPak Returns a complex number provided in the text format "x + yi" or "x + yj" raised to an integer number

IMPRODUCT

Analysis ToolPak Returns the product of 2 - 29 complex numbers provided in the text format "x + yi" or "x + yj"

IMREAL

Analysis ToolPak Returns the real coefficient of a complex number provided in the text format "x + yi" or "x + yj"

IMSIN

Analysis ToolPak Returns the sine of a complex number provided in the text format "x + yi" or "x + yj"

IMSQRT

Analysis ToolPak Returns the square root of a complex number provided in the text format "x + yi" or "x + yj"

IMSUB

Analysis ToolPak Returns the difference of two complex numbers provided in the text format "x + yi" or "x + yj"

IMSUM

Analysis ToolPak Returns the sum of 2 - 29 complex numbers provided in the text format "x + yi" or "x + yj"

OCT2BIN

Analysis ToolPak Converts an octal number to binary

OCT2DEC

Analysis ToolPak Converts an octal number to decimal

OCT2HEX

Analysis ToolPak Converts an octal number to hexadecimal

Engineering Function Arguments Argument

Description

From_unit(1)

A unit of measurement

From_unit(2)

Weight & Mass: "g" = gram; "sg" = slug; "lbm" = pound (avoirdupois); "u" = Atomic mass; "ozm" = ounces (avoirdupois) Distance: "m" = meter; "mi" = Statute Mile; "Nmi" = Nautical mile; "in" = inch; "ft" = foot; "yd" = yard; "ang" = Angstrom; "pica" = pica (1/72in) Time: "yr" = year; "day" = day; "hr" = hour; "mn" = minute; "sec" = second Pressure: "pa" = Pascal; "atm" = atmosphere; "mmHg" = mm of mercury Force: "N" = Newton; "dyn" = dyne; "lbf" = pound force Energy: "J" = Joule; "e" = erg; "c" Thermodynamic calorie; "cal" = IT calorie; "eV" = electron volt; "HPh" = horsepower-hour; "Wh" = Watt-hour; "flb" = foot-pound; "BTU" = BTU Power: "HP" = horsepower; "W" = Watt Magnetism: "T" = Tesler; "ga" = Gauss Temperature: "C" = degrees Celsius; "F" = degrees Fahrenheit; "K" = Kelvin Liquid Measure: "tsp" = teaspoon; "tbs" = tablespoon; "oz" = fluid ounce; "cup" = cup; "pt" = US Pint; "UK_pt" = UK pint; "qt" = quart; "gal" = gallon; "l" = liter

From_unit(3)

Prefix Abbreviations for metric: "E" = 1E+18 = exa; "P" = 1E+15 = peta; "T" 1E+12 = tera; "G" = 1E+09 = giga; "M" = 1E+06 = mega; "k" = 1E+03 = kilo; "h" = 1E+02 = hecto; "e" = 1E+01 = dekao; "d" = 1E-01 = deci; "c"=1E-02 = centi; "m" = 1E-03 = milli; "u" = 1E-06 = micro; "n" = 1E-09 = nano; "p" = 1E-12 = pico; "f" = 1E-15 = femto; "a" = 1E-18 = atto

I_num

Imaginary coeffient of the complex number

Inumber

A complex number

Inumber1

First complex number

Inumber2

Second complex number

Inumber2,…

The second of 1-29 complex numbers

Lower_limit

The lower bound for integrating ERF

N

The order of the function

Number

A number or expression that evaluates to a number

Number1

The first number

Number2

The second number

Number2,…

Second of up to 30 numbers. You can also use a single array or a reference to an array instead of arguments separated by commas

Places

The number of characters to use. If places is omitted, Excel uses the minimum number of characters necessary

Real_num

The real coefficient of the complex number

Step

The threshold value. If you omit a value for step, GESTEP uses zero

Suffix

The suffix for the imaginary component of the complex number. If omitted, suffix is assumed to be "i"

To_unit(1)

A unit of measurement

To_unit(2)

Weight & Mass: "g" = gram; "sg" = slug; "lbm" = pound (avoirdupois); "u" = Atomic mass; "ozm" = ounces (avoirdupois) Distance: "m" = meter; "mi" = Statute Mile; "Nmi" = Nautical mile; "in" = inch; "ft" = foot; "yd" = yard; "ang" = Angstrom; "pica" = pica (1/72in) Time: "yr" = year; "day" = day; "hr" = hour; "mn" = minute; "sec" = second Pressure: "pa" = Pascal; "atm" = atmosphere; "mmHg" = mm of mercury Force: "N" = Newton; "dyn" = dyne; "lbf" = pound force Energy: "J" = Joule; "e" = erg; "c" Thermodynamic calorie; "cal" = IT calorie; "eV" = electron volt; "HPh" = horsepower-hour; "Wh" = Watt-hour; "flb" = foot-pound; "BTU" = BTU Power: "HP" = horsepower; "W" = Watt Magnetism: "T" = Tesler; "ga" = Gauss Temperature: "C" = degrees Celsius; "F" = degrees Fahrenheit; "K" = Kelvin Liquid Measure: "tsp" = teaspoon; "tbs" = tablespoon; "oz" = fluid ounce; "cup" = cup; "pt" = US Pint; "UK_pt" = UK pint; "qt" = quart; "gal" = gallon; "l" = liter

To_unit(3)

Prefix Abbreviations for metric: "E" = 1E+18 = exa; "P" = 1E+15 = peta; "T" 1E+12 = tera; "G" = 1E+09 = giga; "M" = 1E+06 = mega; "k" = 1E+03 = kilo; "h" = 1E+02 = hecto; "e" = 1E+01 = dekao; "d" = 1E-01 = deci; "c"=1E-02 = centi; "m" = 1E-03 = milli; "u" = 1E-06 = micro; "n" = 1E-09 = nano; "p" = 1E-12 = pico; "f" = 1E-15 = femto; "a" = 1E-18 = atto

Upper_limit

The upper bound for integrating ERF. If omitted, ERF integrates between zero and lower_limit

X

The value at which to evaluate the function

eering Functions Syntax =BESSELI(x,n) =BESSELJ(x,n) =BESSELK(x,n) =BESSELY(x,n) =BIN2DEC(Number) =BIN2HEX(Number,Places) =BIN2OCT(Number,Places) =COMPLEX(real_num,i_num,suffix)

=CONVERT(number,from_unit,to_unit) =DEC2BIN(Number) =DEC2HEX(Number) =DEC2OCT(Number) =DELTA(number1,number2) =ERF(lower_limit,upper_limit) =ERFC(x) =GESTEP(number,step) =HEX2BIN(Number,Places) =HEX2DEC(Number) =HEX2OCT(Number,Places) =IMABS(inumber)

=IMAGINARY(inumber)

=IMARGUMENT(inumber) =IMCONJUGATE(inumber)

=IMCOS(inumber)

=IMDIV(inumber1,inumber2)

=IMEXP(inumber)

=IMLN(inumber)

=IMLOG10(inumber)

=IMLOG2(inumber)

=IMPOWER(inumber,number)

=IMPRODUCT(inumber1,inumber2,...)

=IMREAL(inumber)

=IMSIN(inumber)

=IMSQRT(inumber)

=IMSUB(inumber1,inumber2)

=IMSUM(inumber1,inumber2,...)

=OCT2BIN(number,places) =OCT2DEC(number) =OCT2HEX(number,places)

g Function Arguments

" = slug; "lbm" = pound (avoirdupois); "u" = Atomic mass; "ozm" = ounces

atute Mile; "Nmi" = Nautical mile; "in" = inch; "ft" = foot; "yd" = yard; (1/72in) hr" = hour; "mn" = minute; "sec" = second = atmosphere; "mmHg" = mm of mercury ne; "lbf" = pound force c" Thermodynamic calorie; "cal" = IT calorie; "eV" = electron volt; = Watt-hour; "flb" = foot-pound; "BTU" = BTU = Watt Gauss elsius; "F" = degrees Fahrenheit; "K" = Kelvin on; "tbs" = tablespoon; "oz" = fluid ounce; "cup" = cup; t; "qt" = quart; "gal" = gallon; "l" = liter

c: "E" = 1E+18 = exa; "P" = 1E+15 = peta; "T" 1E+12 = tera; "G" = 1E+09 = 1E+03 = kilo; "h" = 1E+02 = hecto; "e" = 1E+01 = dekao; "d" = 1E-01 = E-03 = milli; "u" = 1E-06 = micro; "n" = 1E-09 = nano; "p" = 1E-12 = pico; "f" = o

ex number

mbers

ERF

aluates to a number

ou can also use a single array or a reference to an array instead of as

e. If places is omitted, Excel uses the minimum number of characters

ex number

a value for step, GESTEP uses zero

ponent of the complex number. If omitted, suffix is assumed to be "i"

" = slug; "lbm" = pound (avoirdupois); "u" = Atomic mass; "ozm" = ounces

atute Mile; "Nmi" = Nautical mile; "in" = inch; "ft" = foot; "yd" = yard; (1/72in) hr" = hour; "mn" = minute; "sec" = second = atmosphere; "mmHg" = mm of mercury ne; "lbf" = pound force c" Thermodynamic calorie; "cal" = IT calorie; "eV" = electron volt; = Watt-hour; "flb" = foot-pound; "BTU" = BTU = Watt Gauss elsius; "F" = degrees Fahrenheit; "K" = Kelvin on; "tbs" = tablespoon; "oz" = fluid ounce; "cup" = cup; t; "qt" = quart; "gal" = gallon; "l" = liter

c: "E" = 1E+18 = exa; "P" = 1E+15 = peta; "T" 1E+12 = tera; "G" = 1E+09 = 1E+03 = kilo; "h" = 1E+02 = hecto; "e" = 1E+01 = dekao; "d" = 1E-01 = E-03 = milli; "u" = 1E-06 = micro; "n" = 1E-09 = nano; "p" = 1E-12 = pico; "f" = o ERF. If omitted, ERF integrates between zero and lower_limit

he function

External Functions Name

Source

Description

CALL

Built In

Calls a procedure in a dynamic link library or code resource

EUROCONVERT Add-in

Converts a number to or from Euros to or from a member currency or converts between one euro member currency to another using the Euro as an intermediary (triangulation)

GETPIVOTDATA Built In

Returns data stored in a pivot table

REGISTER.ID

Built In

Returns the register ID of the specified dynamic link library (DLL) or code resource that has been previously registered

SQL.REQUEST

ODBC

Connects with an external data source, and runs a query from a worksheet. SQL.REQUEST then returns the result as an array

External Function Arguments Argument

Description

Argument1,…

The arguments to be passed to the procedure

Col_names_logical

Indicates whether column names are returned as the first row of the results. TRUE if the column names to be returned as the first row of the results. FALSE if column names not wanted. If column_names_logical is omitted, SQL.REQUEST does not return column names

Connection_string

Supplies information, such as the data source name, user ID, and passwords, required by the driver being used to connect to a data source and must follow the driver's format

Data_field

The name, enclosed in quotation marks, for the data field that contains the data

Driver_prompt

Specifies when the driver dialog box is displayed and which options are available

Field1, Item1

One of up to 14 pairs of field names and item names that describe the data

Field2, Item2,…

Second of up to 14 pairs of field names and item names that describe the data

File_text

The name of the file that contains the code resource in Microsoft Excel for the Macintosh

Item1

One of up to 14 pairs of field names and item names that describe the data

Item2,…

Second of up to 14 pairs of field names and item names that describe the data

Module_text

Quoted text specifying the name of the dynamic link library (DLL) that contains the procedure in Microsoft Excel for Windows

Number

A number or expression that evaluates to a number

Output_ref

A cell reference where you want the completed connection string placed

Pivot_table

A reference to any cell, range of cells, or named range of cells in a PivotTable report

Procedure

Text specifying the name of the function in the DLL. You can also use the ordinal value of the function from the EXPORTS statement in the module-definition file (.DEF).

Query_text

The SQL statement that you want to execute on the data source

Register_id

The value returned by a previously executed REGISTER or REGISTER.ID function

Resource

The name of the code resource in Microsoft Excel for the Macintosh. You can also use the resource ID number. The resource ID number must not be in the form of text

Source

A three-letter string, or reference to a cell containing the string, corresponding to the ISO code for the source currency: Belgium - franc - BEF Luxembourg - franc - LUF Germany - deutche mark - DEM Spain - peseta - ESP France - franc - FRF Ireland - pound - IEP Italy - lira - ITL Netherlands - guilder - NLG Austria - schilling - ATS Portugal - escudo - PTE Finland - markka -FIM Euro member states - euro - EUR Others may be added later: Denmark - krone - DKK Greece - drachma - GRD Sweden - krona - SEK UK - pound sterling - GBP

Target

A three-letter string, or reference to a cell containing the string, corresponding to the ISO code for the source currency Belgium - franc - BEF Luxembourg - franc - LUF Germany - deutche mark - DEM Spain - peseta - ESP France - franc - FRF Ireland - pound - IEP Italy - lira - ITL Netherlands - guilder - NLG Austria - schilling - ATS Portugal - escudo - PTE Finland - markka -FIM Euro member states - euro - EUR Others may be added later: Denmark - krone - DKK Greece - drachma - GRD Sweden - krona - SEK UK - pound sterling - GBP

Type_text (CALL function)

Text specifying the data type of the return value and the data types of all arguments to the DLL or code resource

ernal Functions Syntax =CALL(register_id,argument1,...) With Register.id =CALL(module_text,procedure,type_text, argument1,...) Excel for Windows =CALL(file_text,resource,type_text, argument1,...) Excel for Macintosh =EUROCONVERT(number,source,target, full_precision,triangulation_precision)

=GETPIVOTDATA(data_field,pivot_table,field1,item1, field2,item2,...) =REGISTER.ID(module_text,procedure,type_text)

=SQL.REQUEST(connection_string,output_ref, driver_prompt,query_text,col_names_logical)

Function Arguments

the procedure

s are returned as the first row of the results. TRUE if the column names to e results. FALSE if column names not wanted. If column_names_logical is ot return column names

e data source name, user ID, and passwords, required by the driver being e and must follow the driver's format

n marks, for the data field that contains the data box is displayed and which options are available

mes and item names that describe the data names and item names that describe the data

s the code resource in Microsoft Excel for the Macintosh

mes and item names that describe the data names and item names that describe the data

e of the dynamic link library (DLL) that contains the procedure in Microsoft

aluates to a number the completed connection string placed

cells, or named range of cells in a PivotTable report

function in the DLL. You can also use the ordinal value of the function from module-definition file (.DEF).

nt to execute on the data source

sly executed REGISTER or REGISTER.ID function

in Microsoft Excel for the Macintosh. You can also use the resource ID er must not be in the form of text

e to a cell containing the string, corresponding to the ISO code for the source

bourg - franc - LUF Germany - deutche mark - DEM - franc - FRF Ireland - pound - IEP Italy - lira - ITL ustria - schilling - ATS Portugal - escudo - PTE member states - euro - EUR

ce - drachma - GRD

Sweden - krona - SEK

e to a cell containing the string, corresponding to the ISO code for the source

bourg - franc - LUF Germany - deutche mark - DEM - franc - FRF Ireland - pound - IEP Italy - lira - ITL ustria - schilling - ATS Portugal - escudo - PTE member states - euro - EUR

ce - drachma - GRD

Sweden - krona - SEK

the return value and the data types of all arguments to the DLL or code

Financial Functions Name

Source

ACCRINT

Analysis ToolPak Returns the accrued interest for a security that pays periodic interest

Description

ACCRINTM

Analysis ToolPak Returns the accrued interest for a security that pays interest at maturity

AMORDEGRC

Analysis ToolPak Returns the depreciation for each accounting period using the French accounting system

AMORLINC

Analysis ToolPak Returns the depreciation for each accounting period using the French accounting system

COUPDAYBS

Analysis ToolPak Returns the number of days from the beginning of the coupon period to the settlement date using the specified or default day counting basis

COUPDAYS

Analysis ToolPak Returns the number of days in the coupon period that contains the settlent date using the specified or default day counting basis

COUPDAYSNC

Analysis ToolPak Returns the number of days from the settlement date to the next coupon date using the specified or default day counting basis

COUPNCD

Analysis ToolPak Returns the next coupon date after the settlement date using the specified or default day counting basis

COUPNUM

Analysis ToolPak Returns the number of coupons payable between the settlement date and maturity date using the specified or default day counting basis

COUPPCD

Analysis ToolPak Returns the previous coupon date before the settlement date using the specified or default day counting basis

CUMIPMT

Analysis ToolPak Returns the cumulative interest paid between two periods

CUMPRINC

Analysis ToolPak Returns the cumulative principal paid on a loan between two periods

DB

Built In

Returns the depreciation of an asset for a specified period, using the fixed declining balance method

DDB

Built In

Returns the deoreciation of an asset for a specified period, using the double-declining balance method of some other method that is specified

DISC

Analysis ToolPak Returns the discount rate for a security using the specified or default day counting basis

DOLLARDE

Analysis ToolPak Converts a dollar price (expressed as a fraction) into a dollar price expressed as a decimal number

DOLLARFR

Analysis ToolPak Converts a dollar price (expressed as a decimal number) into a dollar price expressed as a fraction

DURATION

Analysis ToolPak Returns the Macauley duration of a security with periodic interest payments using the specified or default day counting basis

EFFECT

Analysis ToolPak Returns the effective annual interest rate of a given nominal rate with its compounding frequency

FV

Built In

FVSCHEDULE

Analysis ToolPak Returns the future value of an initial principal after applying a series of compound interest rates

INTRATE

Analysis ToolPak Returns the interest rate for a fully invested security using the specified or default day counting basis

IPMT

Built In

Returns the amount of the interest element in a payment for an investment for a given period

IRR

Built In

Returns the internal rate of return for a series of cash flows

ISPMT

Built In

Returns the interest associated with a specific loan payment

MDURATION

Analysis ToolPak Returns the Macauley modified duration for a security with an assumed par value of $100 using the specified or default day counting basis

MIRR

Built In

NOMINAL

Analysis ToolPak Returns the nominal rate equivalent to a given annual effective with a given compounding frequency for the nominal rate

NPER

Built In

Returns the number of periods for an investment

NPV

Built In

Returns the net present value of an investment based upon a series of periodic cash flows and a discount rate where the first cash flow is received at the end of the first period

ODDFPRICE

Analysis ToolPak Returns the price per $100 face value of a security with an odd first period using the specified or default day counting basis

Returns the future value of an investment

Returns the modified internal rate of return based on different finance and reinvestment rates for negative and positive cash flows

ODDFYIELD

Analysis ToolPak Returns the yield of a security with an odd first period using the specified or default day counting basis

ODDLPRICE

Analysis ToolPak Returns the price per $100 face value of a security with an odd last period using the specified or default day counting basis

ODDLYIELD

Analysis ToolPak Returns the yield of a security with an odd last period using the specified or default day counting basis

PMT

Built In

Returns the periodic payment for an annuity

PPMT

Built In

Returns the amount of principal element in a payment for an investment for a given period

PRICE

Analysis ToolPak Returns the price per $100 face value of a security that pays periodic interest using the specified or default day counting basis

PRICEDISC

Analysis ToolPak Returns the price per $100 face value of a discounted security using the specified or default day counting basis

PRICEMAT

Analysis ToolPak Returns the price per $100 face value of a security that pays interest at maturity using the specified or default day counting basis

PV

Built In

Returns the present value of an investment

RATE

Built In

Returns the interest rate per period of an annuity

RECEIVED

Analysis ToolPak Returns the amount received at maturity for a fully invested security using the specified or default day counting basis

SLN

Built In

Returns the straight-line depreciation of an asset for one period.

SYD

Built In

Returns the sum of years' digits depreciation of an asset for a specified period

TBILLEQ

Analysis ToolPak Returns the bond-equivalent yield for a treasury bill

TBILLPRICE

Analysis ToolPak Returns the price per $100 face value for a Treasury bill

TBILLYIELD

Analysis ToolPak Returns the yield for a treasury bill

VDB

Built In

XIRR

Analysis ToolPak Returns the annual effective interest rate for a schedule of cash flows received at specified dates

XNPV

Analysis ToolPak Returns the net present value for a schedule of cash flows received at specified dates

Returns the depreciation of an asset for a specified or partial period using a variable declining balance method

YIELD

Analysis ToolPak Returns the yield on a security that pays periodic interest using the specified or default day counting basis

YIELDDISC

Analysis ToolPak Returns the annual yield for a discounted security using the specified or default day counting basis.

YIELDMAT

Analysis ToolPak Returns the annual yield of a security that pays interest at maturity using the specified or default day counting basis

Financial Function Arguments Argument

Description

Basis (For Securities Functions)

Year basis to be used: 0 or omitted 360 (NASD); 1 = Actual; 2 = 365; 3 = 360 (European)

Cost

Cost of the asset

Coupon

The security's annual coupon rate

Date_purchased

Date of purchase of the asset. Entered as an acceptable date in quotation marks or as a serial number or function returning a date serial number

Dates

A schedule of payment dates that corresponds to the cash flow payments. The first payment date indicates the beginning of the schedule of payments. All other dates must be later than this date, but they may occur in any order

Decimal_dollar

A decimal number

Discount

The security's discount rate

Effect_rate

The effective interest rate

End_period

Last period number

Factor

The rate at which the balance declines. If factor is omitted, it is assumed to be 2 (the double-declining balance method)

Finance_rate

The interest rate you pay on the money used in the cash flows

First_coupon

The security's first coupon date

First_Interest

A security's first interest date entered as an acceptable date in quotation marks or as a serial number or function returning a date serial number

First_period

Date of the end of the first period. Entered as an acceptable date in quotation marks or as a serial number or function returning a date serial number

Fraction

The integer to use in the denominator of the fraction

Fractional_dollar

A number expressed as a fraction

Frequency

The number of coupon payments per year for a security.

FV

Future Value

Guess

A number that you guess is close to the result of IRR

Investment

The amount invested in the security

Issue

A security's issue date entered as an acceptable date in quotation marks or as a serial number or function returning a date serial number

Last_interest

The security's last coupon date

Life

The number of periods over which the asset is being depreciated (sometimes called the useful life of the asset)

Maturity

The security's maturity date

Month (For DB function)

The number of months in the first year. If month is omitted, it is assumed to be 12

No_switch

A logical value specifying whether to switch to straight-line depreciation when depreciation is greater than the declining balance calculation.If TRUE, Excel does not switch to straight-line depreciation even when the depreciation is greater than the declining balance calculation. FALSE or omitted, Excel switches to straightline depreciation when depreciation is greater than the declining balance calculation

Nominal_rate

The nominal interest rate

Nper

The number of periods

Npery

The number of compounding periods per year

Par

A security's par value. (Default = $1,000)

Period

The number of the accounting period

Pmt

The payment made each period

Pr

The security's price per $100 face value

Principal

The present value

PV

The Present Value

Rate (For amortization functions)

The interest rate per period

Rate (For Depreciation Functions)

The rate of depreciation

Rate (For NPV and XNPV)

The discount rate

Rate (For Securities Functions)

A security's annual coupon rate

Redemption

The security's redemption value per $100 face value

Reinvest_rate

The interest rate you receive on the cash flows as you reinvest them

Salvage

Salvage value at the end of the life of the asset

Schedule

An array of interest rates to apply

Settlement

A security's settlement date, which is the date after the issue date when the security is traded to the buyer. Entered as an acceptable date in quotation marks or as a serial number or function returning a date serial number

Start_period

First period number

Type (Amortization functions)

0 = Payment in arrears; 1 = payment in advance

Value1

A numeric value

Value2,…

Second of 1 to n possible values (n=30 for AVERAGEA; n=29 for CHOOSE, NPV)

Values (IRR and MIRR)

An array or a reference to cells that contain numbers for which you want to calculate the internal rate of return. Values must contain at least one positive value and one negative value to calculate the internal rate of return. If an array or reference argument contains text, logical values, or empty cells, those values are ignored.

Values (XIRR and XNPV)

A series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive value and one negative value

Yld

The security's annual yield

ncial Functions Syntax =ACCRINT(Issue,First_Interest,Settlement,Rate, Par,Frequency,Basis) =ACCRINTM(Issue,Settlement,Rate,Par,Basis) =AMORDEGRC(Cost,Date_purchased,First_period, Salvage,Period,Rate,Basis)

=AMORLINC(Cost,Date_purchased,First_period, Salvage,Period,Rate,Basis)

=COUPDAYBS(settlement,maturity, frequency,basis)

=COUPDAYS(settlement,maturity, frequency,basis)

=COUPDAYSNC(settlement,maturity, frequency,basis)

=COUPNCD(settlement,maturity,frequency,basis)

=COUPNUM(settlement,maturity,frequency,basis)

=COUPPCD(settlement,maturity,frequency,basis)

=CUMIPMT(rate,nper,pv,start_period,end_period, type) =CUMPRINC(rate,nper,pv,start_period,end_period, type) =DB(cost,salvage,life,period,month)

=DDB(cost,salvage,life,period,factor)

=DISC(settlement,maturity,pr,redemption,basis)

=DOLLARDE(fractional_dollar,fraction)

=DOLLARFR(decimal_dollar,fraction)

=DURATION(settlement,maturity,coupon,yld, frequency,basis)

=EFFECT(nominal_rate,npery)

=FV(rate,nper,pmt,pv,type) =FVSCHEDULE(principal,schedule)

=INTRATE(settlement,maturity,investment, redemption,basis)

=IPMT(rate,per,nper,pv,fv,type)

=IRR(values,guess) =ISPMT(rate,per,nper,pv) =MDURATION(settlement,maturity,coupon,yld, frequency,basis)

=MIRR(values,finance_rate,reinvest_rate)

=NOMINAL(effect_rate,npery)

=NPER(rate, pmt, pv, fv, type) =NPV(rate,value1,value2, ...)

=ODDFPRICE(settlement,maturity,issue, first_coupon,rate,yld,redemption, frequency,basis)

=ODDFYIELD(settlement,maturity,issue, first_coupon,rate,pr,redemption, frequency,basis) =ODDLPRICE(settlement,maturity,last_interest, rate,yld,redemption,frequency,basis)

=ODDLYIELD(settlement,maturity,last_interest, rate,pr,redemption,frequency,basis)

=PMT(rate,nper,pv,fv,type) =PPMT(rate,per,nper,pv,fv,type)

=PRICE(settlement,maturity,rate,yld,redemption, frequency,basis)

=PRICEDISC(settlement,maturity,discount, redemption,basis)

=PRICEMAT(settlement,maturity,issue,rate, yld,basis)

=PV(rate,nper,pmt,fv,type) =RATE(nper,pmt,pv,fv,type,guess) =RECEIVED(settlement,maturity,investment, discount,basis)

=SLN(cost,salvage,life) =SYD(cost,salvage,life,per)

=TBILLEQ(settlement,maturity,discount) =TBILLPRICE(settlement,maturity,discount) =TBILLYIELD(settlement,maturity,pr) =VDB(cost,salvage,life,start_period,end_period, factor,no_switch)

=XIRR(values,dates,guess)

=XNPV(rate,values,dates)

=YIELD(settlement,maturity,rate,pr,redemption, frequency,basis)

=YIELDDISC(settlement,maturity,pr,redemption, basis)

=YIELDMAT(settlement,maturity,issue,rate,pr, basis)

Function Arguments

ted 360 (NASD); 1 = Actual; 2 = 365; 3 = 360 (European)

te

ntered as an acceptable date in quotation marks or as a serial number or umber

at corresponds to the cash flow payments. The first payment date indicates payments. All other dates must be later than this date, but they may occur

eclines. If factor is omitted, it is assumed to be 2 (the double-declining money used in the cash flows

tered as an acceptable date in quotation marks or as a serial number or umber

d. Entered as an acceptable date in quotation marks or as a serial number al number

inator of the fraction

on

s per year for a security.

e to the result of IRR

urity

as an acceptable date in quotation marks or as a serial number or function

ch the asset is being depreciated (sometimes called the useful life of the

st year. If month is omitted, it is assumed to be 12

er to switch to straight-line depreciation when depreciation is greater than n.If TRUE, Excel does not switch to straight-line depreciation even when the declining balance calculation. FALSE or omitted, Excel switches to straighttion is greater than the declining balance calculation

riods per year

= $1,000)

eriod

ce value

per $100 face value the cash flows as you reinvest them

ife of the asset

y

ch is the date after the issue date when the security is traded to the buyer. n quotation marks or as a serial number or function returning a date serial

ment in advance

s (n=30 for AVERAGEA; n=29 for CHOOSE, NPV)

hat contain numbers for which you want to calculate the internal rate of east one positive value and one negative value to calculate the internal rate e argument contains text, logical values, or empty cells, those values are

sponds to a schedule of payments in dates. The first payment is optional yment that occurs at the beginning of the investment. If the first value is a gative value. All succeeding payments are discounted based on a 365-day contain at least one positive value and one negative value

Information Functions Name

Source

Description

CELL

Built In

Returns information about the formatting , location or contents of the cell or upper left cell of the reference

COUNTBLANK*

Built In

Counts the number of blank cells in the range

ERROR.TYPE

Built In

Returns a number corresponding to an Excel error type

INFO

Built In

Returns information about the current operating environment

ISBLANK

Built In

Returns TRUE if the value is blank

ISERR

Built In

Returns TRUE if the value is any error value except #N/A

ISERROR

Built In

Returns TRUE if the value is any error value

ISEVEN

Analysis ToolPak Returns TRUE if the number is even

ISLOGICAL

Built In

Returns TRUE if the value is a logical value

ISNA

Built In

Returns TRUE if the value is the #N/A error value

ISNONTEXT

Built In

Returns TRUE if the value is not text

ISNUMBER

Built In

Returns TRUE if the value is a number

ISODD

Analysis ToolPak Returns TRUE if the number is odd

ISREF

Built In

Returns TRUE if the value is a reference

ISTEXT

Built In

Returns TRUE if the value is text

N

Built In

Returns a value converted to a number

NA

Built In

Returns the error value #N/A

TYPE

Built In

Returns the type of value (1 for value is a number; 2 for value is text; 4 for value is logical value; 16 for value is error value; 64 for value is array)

Notes:

* See also COUNT, COUNTA and FREQUENCY (Statistical) and COUNTIF (Maths) and DCOUNT and DCOUNTA (Database)

Information Function Arguments Argument

Description

Error_val

The error value whose identifying number you want: 1 = #Null!; 2 = #Div/0!; 3 = #Value!; 4 = #Ref!; 5 = #Name?; 6 = #N/A

Info_type

A text value that specifies what type of cell information you want: "Address" = first cell in reference "Col" = First column number in reference "Color" Returns 1 for negative colored, 0 otherwise "Contents" = Value of upper left cell in reference "Format" = Text value corresponding to number format "Prentheses" = Returns 1 for parenthese for positive or all values, 0 otherwise "Prefix" = Text value corresponding to label prefix "Protect" = returns 0 if unlucked, 1 if locked "Row" = First row number in reference "Type" = Text value for type of data; b for blank, l for label, v for value "Width" = Column width rounded to nearest integer

Number

A number or expression that evaluates to a number

Reference

Reference to a cell or range of cells and can refer to multiple areas. If you want to specify several references as a single argument, then you must include extra sets of parentheses so that Microsoft Excel will not interpret the comma as a field separator

Type_text (INFO function)

Text that specifies what type of information you want returned: "directory" = Path of current directory of folder; "memavail" = Amount of currently available memory in bytes; "memused" = Amount of memory being used for data; "numfile" = number of active worksheets in open workbooks; "origin" = cell reference of top leftmost cell visible in window, based on current scrolling position; "osversion" = current operating system version, as text; "recalc" = current recalculation mode; "release" = version of Excel; "system" = name of the operating environment (Mac or pcdos); "totmem" = total memory available

Value

A value or an expression that evaluates to a value:

mation Functions Syntax =CELL(info_type,reference)

=COUNTBLANK(range) =ERROR.TYPE(error_val) =INFO(type_text) =ISBLANK(Value) =ISERR(Value) =ISERROR(Value) =ISEVEN(Number) =ISLOGICAL(Value) =ISNA(Value) =ISNONTEXT(Value) =ISNUMBER(Value) =ISODD(Number) =ISREF(Value) =ISTEXT(Value) =N(Value) =NA() =TYPE(Value)

Statistical) and COUNTIF (Maths) and DCOUNT and DCOUNTA (Database)

n Function Arguments

g number you want: ue!; 4 = #Ref!; 5 = #Name?;

ype of cell information you want: e eference olored, 0 otherwise cell in reference nding to number format renthese for positive or all values, 0 otherwise ing to label prefix 1 if locked erence ata; b for blank, l for label, v for value d to nearest integer

aluates to a number

ells and can refer to multiple areas. If you want to specify several references must include extra sets of parentheses so that Microsoft Excel will not eparator

nformation you want returned: ctory of folder; y available memory in bytes; y being used for data; rksheets in open workbooks; ftmost cell visible in window, based on current scrolling position; system version, as text; mode;

g environment (Mac or pcdos); ble

aluates to a value:

Logical Functions Name

Source

Description

AND

Built In

Returns TRUE if all its arguments are TRUE

FALSE

Built In

Returns the logical value FALSE

IF

Built In

Returns one value specified condition evaluates to TRUE and another value if it evaluates to FALSE

NOT

Built In

Reverses the logic of its argument

OR

Built In

ReturnsTRUE if any argument is TRUE

TRUE

Built In

Returns the logical value TRUE

Logical Function Arguments Argument

Description

Logical

A value or expression that can be evaluated to TRUE or FALSE

Logical_test

Any value or expression that can be evaluated to TRUE or FALSE

Logical1

Condition to be tested. Argument must evaluate to a logical value or be arrays or references that contain logical values

Logical2,…

Second of up to 30 conditions you want to test that can be either TRUE or FALSE

Value_if_false

The value that is returned if logical_test is FALSE

Value_if_true

The value that is returned if logical_test is TRUE

gical Functions Syntax =AND(Logical1,Logical2,…) =FALSE() =IF(logical_test,value_if_true,value_if_false)

=NOT(Logical) =OR(logical1,logical2,...) =TRUE()

Function Arguments

e evaluated to TRUE or FALSE

n be evaluated to TRUE or FALSE

nt must evaluate to a logical value or be arrays or references that contain

ou want to test that can be either TRUE or FALSE

cal_test is FALSE

cal_test is TRUE

Lookup & Reference Functions Name

Source

Description

ADDRESS

Built In

Creates a cell address as text based on given row and column rumbers

AREAS

Built In

Returns the number of areas in a reference

CHOOSE

Built In

Uses a specified index number to select one from up to 29 specified values

COLUMN

Built In

Returns the column number of the cell or a specified reference

COLUMNS

Built In

Returns the number of columns in an array or reference

HLOOKUP

Built In

Looks in the top row of a table or array and returns the value of the indicated cell

HYPERLINK

Built In

Creates a shortcut that opens a document on your hard drive, a server or the internet

INDEX

Built In

Alternative forms. Array form returns a value or array of values. Reference form returns a reference.

INDIRECT

Built In

Returns a reference indicated by a value provided as text

LOOKUP

Built In

Alternative forms. Vector form looks up values in a one row or column range and returns a value in a second one row or column range. Array form looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array

MATCH

Built In

Returns the relative position of an item in an array that matches a specified value in a specified order

OFFSET

Built In

Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells

ROW

Built In

Returns the row number of a reference

ROWS

Built In

Returns the number of rows in a reference

RTD

Built In

Retrieves real-time data from a program that supports COM automation

TRANSPOSE

Built In

Transposes vertical or horizontal arrays. Entered as an array formula in same number of cells as array.

VLOOKUP

Built In

Locates a specified value in the leftmost column of a specified table, and returns the value in the same row from a specified column in the table

Lookup & Reference Function Arguments Argument

Description

A1

A logical value that specifies the A1 (TRUE) or R1C1 (FALSE) reference style

Abs_num

The type of reference to return: 1 = Absolute; 2 = Abs Row / Rel Col; 3 = Rel Row / Abs Col; 4 = Relative

Area_num

Selects a range in reference from which to return the intersection of row_num and column_num

Array

An array or array formula, or a reference to a range of cells

Col_index_num

The column number in table_array from which the matching value must be returned

Cols

The number of columns, to the left (-) or right (+), that you want the upper-left cell of the result to refer to

Column_num

Column number

Friendly_name

The jump text or numeric value that is displayed in the cell

Height

The height, in number of rows, that you want the returned reference to be. Height must be a positive number

Index_num

Specifies which value argument is selected. Index_num must be a number between 1 and 29, or a formula or reference to a cell containing a number between 1 and 29

Link_location

The path and file name to the document to be opened as text

Lookup_array

A contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference

Lookup_value

The value to be found

Lookup_vector

A range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values

Match_type

The number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array: 1 = (default) largest value less than or equal to look_up value; 0 = first value to exactly equal lookup value -1 = smallest value greater than or equal to look_value

ProgID

The name of the ProgID of a registered COM automation add-in that has been installed on the local computer. Enclose the name in quotation marks

Range_lookup

A logical value that specifies whether you want to find an exact match or an approximate match. If TRUE = approximate match is returned FALSE = Exact

Ref_text

Refers to another workbook (an external reference),

Reference

Reference to a cell or range of cells and can refer to multiple areas. If you want to specify several references as a single argument, then you must include extra sets of parentheses so that Microsoft Excel will not interpret the comma as a field separator

Result_vector

A range that contains only one row or column. It must be the same size as lookup_vector

Row_index_num

The row number in table_array from which the matching value will be returned

Row_num

Row number

Rows

The number of rows, up (-) or down (+), that you want the upper-left cell to refer to

Server

Name of the server where the add-in should be run. If there is no server, and the program is run locally, leave the argument blank. Otherwise, enter quotation marks ("") around the server name

Sheet_text

Text specifying the name of the worksheet to be used as the external reference. If sheet_text is omitted, no sheet name is used

Table_array

A table of information in which data is looked up

Topic1

First of up to 28 parameters that together represent a unique piece of real-time data

Topic2,…

Second of up to 28 parameters that together represent a unique piece of real-time data

Value1

A numeric value

Value2,…

Second of 1 to n possible values (n=30 for AVERAGEA; n=29 for CHOOSE, NPV)

Width

The width, in number of columns, that you want the returned reference to be. Width must be a positive number

Reference Functions Syntax =ADDRESS(Row_num,Column_num,Abs_num, A1,Sheet_text) =AREAS(reference) =CHOOSE(index_num,value1,value2,...) =COLUMN(reference) =COLUMNS(array) =HLOOKUP(lookup_value,table_array, row_index_num,range_lookup) =HYPERLINK(link_location,friendly_name) =INDEX(array,row_num,column_num) =INDEX(reference,row_num,column_num, area_num) =INDIRECT(ref_text,a1) =LOOKUP(lookup_value,lookup_vector, result_vector) =LOOKUP(lookup_value,array)

=MATCH(lookup_value,lookup_array, match_type)

=OFFSET(reference,rows,cols,height,width)

=ROW(reference) =ROWS(array) =RTD(ProgID,server,topic1,[topic2],...) =TRANSPOSE(array)

=VLOOKUP(lookup_value,table_array, col_index_num,range_lookup)

rence Function Arguments A1 (TRUE) or R1C1 (FALSE) reference style

Col; 3 = Rel Row / Abs Col; 4 = Relative

m which to return the intersection of row_num and column_num

eference to a range of cells

ay from which the matching value must be returned

eft (-) or right (+), that you want the upper-left cell of the result to refer to

hat is displayed in the cell

hat you want the returned reference to be. Height must be a positive number

is selected. Index_num must be a number between 1 and 29, or a formula a number between 1 and 29

ocument to be opened as text

aining possible lookup values. Lookup_array must be an array or an array

ow or one column. The values in lookup_vector can be text, numbers, or

ype specifies how Microsoft Excel matches lookup_value with values in

han or equal to look_up value; 0 = first value to exactly equal lookup value or equal to look_value

gistered COM automation add-in that has been installed on the local quotation marks

ether you want to find an exact match or an approximate match. If TRUE = FALSE = Exact

external reference),

ells and can refer to multiple areas. If you want to specify several references must include extra sets of parentheses so that Microsoft Excel will not eparator

ow or column. It must be the same size as lookup_vector

rom which the matching value will be returned

own (+), that you want the upper-left cell to refer to

dd-in should be run. If there is no server, and the program is run locally, wise, enter quotation marks ("") around the server name

worksheet to be used as the external reference. If sheet_text is omitted, no

ata is looked up together represent a unique piece of real-time data

hat together represent a unique piece of real-time data

s (n=30 for AVERAGEA; n=29 for CHOOSE, NPV)

s, that you want the returned reference to be. Width must be a positive

Maths Functions Name

Source

Description

ABS

Built In

Returns the absolute value of a number

ACOS

Built In

Returns in radians the arccosine of a number

ACOSH

Built In

Returns the inverse hyperbolic cosine of a number

ASIN

Built In

Returns in radians the arcsine of a number

ASINH

Built In

Returns the inverse hyperbolic sine of a number

ATAN

Built In

Returns in radians the arctangent of a number

ATAN2

Built In

Returns in radians the arctangent from x and y coordinates

ATANH

Built In

Returns the inverse hyperbolic tangent of a number

CEILING

Built In

Rounds a number (away from zero) to the nearest integer or to the nearest multiple of significance

COMBIN++

Built In

Returns the number of combinations for a given number of objects

COS

Built In

Returns the cosine of a given angle given in radians

COSH

Built In

Returns the hyperbolic cosine of a number

COUNTIF*

Built In

Counts the number of cells that meet the criteria specified in the argument

DEGREES

Built In

Converts radians to degrees

EVEN

Built In

Rounds a number away from zero to the nearest even integer

EXP

Built In

Returns e (=2.71828182845904) raised to the power of a given number

FACT

Built In

Returns the factorial of a number

FACTDOUBLE

Analysis ToolPak Returns the double factorial of a number

FLOOR

Built In

GCD

Analysis ToolPak Returns the greatest common divisor of 2 - 29 integers

INT

Built In

LCM

Analysis ToolPak Returns the least common multiple of 1 - 29 integers

LN

Built In

Returns the natural logarithm (base e = 2.71828182845904) of a number

LOG

Built In

Returns the logarithm of a number to a specified base

Rounds a number down towards 0 to the nearest integer or to the nearest multiple of significance

Rounds a number away from 0 to the nearest integer

LOG10

Built In

Returns the base-10 logarithm of a number

MDETERM

Built In

Returns the matrix determinant of an array

MINVERSE

Built In

Returns the matrix inverse of an array

MMULT

Built In

Returns the matrix product of two arrays

MOD**

Built In

Returns the remainder from division with the result having the same sign as the divisor

MROUND

Analysis ToolPak Returns a number rounded to the desired multiple. Midway points are rounded away from 0

MULTINOMIAL

Analysis ToolPak Returns the ratio of the factorial of a sum of values to the product of factorials

ODD

Built In

Rounds a number away from 0 to the nearest odd integer

PI

Built In

Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits

POWER

Built In

Returns the result of a number raised to a power

PRODUCT

Built In

Multiplies together 1 - 30 numbers

QUOTIENT

Analysis ToolPak Returns the integer portion of a division

RADIANS

Built In

Converts degrees to radians

RAND

Built In

Returns an evenly distributed random number greater than or equal to 0 and less than 1

RANDBETWEEN Analysis ToolPak Returns a random number between (and inclusive of) two specified numbers ROMAN

Built In

Converts an arabic number to Roman, as text

ROUND

Built In

Rounds a number to a specified number of digits to the left (-) or right (+) of the decimal point. The midway digit 5 is rounded away from 0.

ROUNDDOWN

Built In

Rounds a number down towards 0 to a specified number of digits to the left (-) or right (+) of the decimal point

ROUNDUP

Built In

Round a number up away from 0 to a specified number of digits to the left (-) or right (+) of the decimal point

SERIESSUM

Analysis ToolPak Returns the sum of a power series expansion

SIGN

Built In

Returns 1 for positve numbers, 0 if the number is 0 and, -1 if the number is negative

SIN

Built In

Returns the sine of a given angle given in radians

SINH

Built In

Returns the hyperbolic sine of a given number

SQRT

Built In

Returns a positive square root

SQRTPI

Analysis ToolPak Returns the square root of a number multiplied by pi

SUBTOTAL

Built In

Returns the sutotal in a list or database

SUM

Built In

Adds its arguments

SUMIF

Built In

Add the cells specified by a given criteria

SUMPRODUCT

Built In

Returns the sum of the products of corresponding array components

SUMSQ

Built In

Returns the sum of the squares of 1 - 30 numbers

SUMX2MY2

Built In

Returns the sum of the difference of squares of corresponding values in two arrays

SUMX2PY2

Built In

Returns the sum of the sum of squares of corresponding values in two arrays

SUMXMY2

Built In

Returns the sum of squares of differences of corresponding values in two array

TAN

Built In

Returns the tangent of a given angle given in radians

TANH

Built In

Returns the hyperbolic tangent of a number

TRUNC

Built In

Truncates a number to an integer or to specified precision by removing the fractional part of the number. (Serves to round down towards 0)

Notes:

* See also COUNT, COUNTA and FREQUENCY (Statistical) and COUNTBLANK (Information) and DCOUNT and DCOUNTA (Database) ** The Excel MOD function uses a different definition of MOD to that used by VBA Mod function insofar as negative numbers are concerned. ++

See also PERMUT function (Statistical)

Maths Function Arguments Argument

Description

Angle

The angle in radians

Array

An array or array formula, or a reference to a range of cells

Array_x

The first array or range of values

Array_y

The second array or range of values

Array1 (SUMPRODUCT)

A cell range of values (up to 30 with SUMPRODUCT)

Array2 (SUMPRODUCT)

A second cell range of values (up to 30 with SUMPRODUCT)

Array3,…

A third cell range of values (up to 30 with SUMPRODUCT)

Base

The base of the logarithm. If base is omitted, it is assumed to be 10

Bottom

The smallest integer to be returned

Coefficients

A set of coefficients by which each successive power of x is multiplied. The number of values in coefficients determines the number of terms in the power series

Criteria

The range of cells that contains the conditions you specify

Denominator

The divisor

Divisor

The number by which you want to divide number

Form

A number specifying the type of roman numeral you want; 0 (Default) = Classic; 1-4 Progressively simplified; TRUE = Classic; FALSE = Most Simplified

Function_num

The number 1 to 11 that specifies which function to use in calculating subtotals within a list: 1 = AVERAGE; 2 = COUNT; 3 = COUNTA; 4 = MAX; 5 = MIN; 6 = PRODUCT; 7 = STDEV; 8 = STDEVP; 9 = SUM; 10 = VAR; 11 = VARP

M

The step by which to increase n for each term in the series

Multiple

The multiple to which you want to round number

N

The order of the function

Num_digits

Specifies the number of digits to which you want to round number: 0 = Integer; -numbers = to left of decimal; +numbers = to right of decimal

Number

A number or expression that evaluates to a number

Number_chosen

The number of items

Number1

The first number

Number2,…

Second of up to 30 numbers. You can also use a single array or a reference to an array instead of arguments separated by commas

Numerator

The dividend

Power

The exponent to which the base number is raised

Range

A range

Ref1

First of up to 29 ranges or references for which you want the subtotal

Ref2,…

Second of up to 29 ranges or references for which you want the subtotal

Significance

The multiple to which you want to round

Sum_range

The actual cells to sum

Top

The largest integer to be returned

X

The value at which to evaluate the function

X_num

The x-coordinate of the point

Y_num

The y-coordinate of the point

ths Functions Syntax =ABS(Number) =ACOS(Number) =ACOSH(Number) =ASIN(Number) =ASINH(Number) =ATAN(Number) =ATAN2(x_num,y_num) =ATANH(Number) =CEILING(number,significance)

=COMBIN(number,number_chosen) =COS(Number) =COSH(Number) =COUNTIF(range,criteria) =DEGREES(angle) =EVEN(Number) =EXP(Number) =FACT(Number) =FACTDOUBLE(number) =FLOOR(number,significance)

=GCD(number1,number2, ...) =INT(Number) =LCM(number1,number2, ...) =LN(Number) =LOG(Number,base)

=LOG10(Number) =MDETERM(array) =MINVERSE(array) =MMULT(array1,array2) =MOD(number,divisor)

=MROUND(number,multiple)

=MULTINOMIAL(number1,number2, ...) =ODD(number) =PI()

=POWER(number,power) =PRODUCT(number1,number2, ...) =QUOTIENT(numerator,denominator) =RADIANS(angle) =RAND()

=RANDBETWEEN(bottom,top) =ROMAN(number,form) =ROUND(number,num_digits)

=ROUNDDOWN(number,num_digits)

=ROUNDUP(number,num_digits)

=SERIESSUM(x,n,m,coefficients) =SIGN(number)

=SIN(number)

=SINH(number) =SQRT(number) =SQRTPI(number) =SUBTOTAL(function_num,ref1,ref2,...) =SUM(number1,number2, ...) =SUMIF(range,criteria,sum_range) =SUMPRODUCT(array1,array2,array3, ...) =SUMSQ(number1,number2, ...) =SUMX2MY2(array_x,array_y)

=SUMX2PY2(array_x,array_y) =SUMXMY2(array_x,array_y) =TAN(number) =TANH(number) =TRUNC(number,num_digits)

Statistical) and COUNTBLANK (Information) and DCOUNT and DCOUNTA

n of MOD to that used by VBA Mod function insofar as negative numbers

unction Arguments

eference to a range of cells

s

lues

with SUMPRODUCT)

p to 30 with SUMPRODUCT)

o 30 with SUMPRODUCT)

se is omitted, it is assumed to be 10

ned

ch successive power of x is multiplied. The number of values in coefficients in the power series

the conditions you specify

to divide number

roman numeral you want; 0 (Default) = Classic; 1-4 Progressively simplified; Simplified

es which function to use in calculating subtotals within a list: 1 = AVERAGE; MAX; 5 = MIN; 6 = PRODUCT; 7 = STDEV; 8 = STDEVP; 9 = SUM; 10 =

for each term in the series

o round number

which you want to round number: 0 = Integer; -numbers = to left of decimal;

aluates to a number

ou can also use a single array or a reference to an array instead of as

number is raised

ences for which you want the subtotal

ferences for which you want the subtotal

o round

d

he function

Statistical Functions Name

Source

Description

AVEDEV

Built In

Returns the average of the absolute deviations of data points from their mean

AVERAGE

Built In

Returns the average (arithmetic mean) of up to 30 numeric arguments

AVERAGEA

Built In

Returns the average (arithmetic mean) of its arguments and includes evaluation of text and logical arguments

BETADIST

Built In

Returns the cumulative beta probability density function

BETAINV

Built In

Returns the inverse of the cumulative beta probability density function

BINOMDIST

Built In

Returns the individual term binomial distribution probability

CHIDIST

Built In

Returns the one-tailed probability of the chisquared distribution

CHIINV

Built In

Returns the inverse of the one-tailed probability of the chi-squared distribution

CHITEST

Built In

Returns the value from the chi-squared (γ2) distribution for the statistic and the appropriate degrees of freedom

CONFIDENCE

Built In

Returns the confidence interval for a population mean

CORREL

Built In

Returns the correlation coefficient between two arrays of data

COUNT*

Built In

Counts the number of cells that contain numbers and also numbers within the list of arguments

COUNTA*

Built In

Counts the number of non-empty cells and the values within the list of arguments

COVAR

Built In

Returns covariance; the average of the products of deviations for each data point pair

CRITBINOM

Built In

Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value

DEVSQ

Built In

Returns the sum of the squares of the deviations from the sample mean

EXPONDIST

Built In

Returns the exponential distribution

FDIST

Built In

Returns the F probability distribution

FINV

Built In

Returns the inverse of the F probability distribution

FISHER

Built In

Returns the Fisher transformation at x

FISHERINV

Built In

Returns the inverse of the Fisher transformation

FORECAST

Built In

Calculates a predicted value of y for a given x value based on known values for x and y

FREQUENCY*

Built In

Counts how often values occur within given ranges of values and returns those counts as a vertical array of numbers

FTEST

Built In

Returns the result of an F-test; the one-tailed probability that the variances in array1 and array2 are not significantly different

GAMMADIST

Built In

Returns the gamma distribution

GAMMAINV

Built In

Returns the inverse of the gamma distribution

GAMMALN

Built In

Returns the natural logarithm of the gamma function Γ(x).

GEOMEAN

Built In

Returns the geometric mean of an array or range of positive data

GROWTH

Built In

Calculates predicted exponential growth and returns the y-values for a series of specified new x-values by using known x-values and yvalues

HARMEAN

Built In

Returns the harmonic mean of a data set by calculating the reciprocal of the arithmetic mean of reciprocals

HYPGEOMDIST

Built In

Returns the hypergeometric distribution by calculating the probability of a given number of sample successes, given the sample size, population successes, and population size

INTERCEPT

Built In

Calculates from given x and y values the point at which a line will intersect the y-axis

KURT

Built In

Returns the kurtosis of a data set; a measure that compares the relative peakedness or flatness of a distribution compared with the normal distribution

LARGE

Built In

Returns the kth largest value in a data set

LINEST

Built In

Entered as an array formula, LINEST returns an array that describes a line of best fit by using the least squares method

LOGEST

Built In

Entered as an array formula, LOGEST calculates an exponential curve that fits known data and returns an array of values that describes that curve

LOGINV

Built In

Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with given probability, mean, and standard deviation

LOGNORMDIST Built In

Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with known mean and standard deviation

MAX

Built In

Returns the maximum value in a list of arguments ignoring logical values and text

MAXA

Built In

Returns the maximum value in a list of arguments including logical values and text

MEDIAN

Built In

Returns the median of the given numbers

MIN

Built In

Returns the minimum value in a list of arguments ignoring logical values and text

MINA

Built In

Returns the minimum value in a list of arguments including logical values and text

MODE

Built In

Returns the most common value in a data set

NEGBINOMDIST Built In

Returns the negative binomial distribution; the probability that there will be number_f failures before the number_s-th success, when the constant probability of a success is probability_s

NORMDIST

Built In

Returns the cumulative distribution function or probability mass function for the value x with specified mean and standard deviation

NORMINV

Built In

Returns the inverse of the normal cumulative distribution for the specified probability, mean, and standard deviation

NORMSDIST

Built In

Returns the standard normal cumulative distribution function. The distribution has a mean of 0 and a standard deviation of 1

NORMSINV

Built In

Returns the inverse of the standard normal cumulative distribution

PEARSON

Built In

Returns the Pearson product moment correlation coefficient

PERCENTILE

Built In

Returns the kth percentile of values in a range

PERCENTRANK Built In

Returns the percentage rank of a value in a data set

PERMUT**

Built In

Returns the number of permutations for a given number of objects that can be selected from a number of objects without replacement

POISSON

Built In

Returns the Poisson distribution

PROB

Built In

Returns either the probability that values in a range are between two limits or if upper_limit is not specified, the probability that values in x_range are equal to lower_limit

QUARTILE

Built In

Returns the quartile of a data set

RANK

Built In

Returns the rank of a number in a list of numbers

RSQ

Built In

Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's

SKEW

Built In

Returns the skewness of a distribution

SLOPE

Built In

Returns the slope (vertical distance / horizontal distance) of the linear regression line through data points in known_y's and known_x's

SMALL

Built In

Returns the kth smallest value in a data set

STANDARDIZE

Built In

Returns a normalized value from a distribution with known mean and standard_dev

STDEV

Built In

Estimates standard distribution based on a sample ignoring text and logical values

STDEVA

Built In

Estimates standard deviation based on a sample including text and logical values

STDEVP

Built In

STDEVPA

Built In

STEYX

Built In

Calculates standard distribution based on the entire population ignoring text and logical values Calculates standard deviation based on the entire population including text and logical values Returns the standard error of the predicted yvalue for each x in the regression

TDIST

Built In

Returns the probability for the Student tdistribution where a numeric value (x) is a calculated value of t for which the probability is to be computed

TINV

Built In

Returns the t-value of the Student's tdistribution based on given probability and degrees of freedom

TREND

Built In

Entered as an array formula, TREND fits a straight line using the least squares method to arrays of known_y's and known_x's and returns the y-values along that line for the array of specified new_x's

TRIMMEAN

Built In

Calculates the mean by excluding a specified percentage of data points from the top and bottom tails of a data set

TTEST

Built In

Returns the probability associated with a student's t-test

VAR

Built In

Estimates variance based on a sample ignoring logical values and text

VARA

Built In

Estimates variance based on a sample including text and logical values

VARP

Built In

Calculates variance based on the entire population ignoring text and logical values

VARPA

Built In

Calculates variance based on the entire population including text and logical values

WEIBULL

Built In

Returns the Weibull distribution

ZTEST

Built In

Returns the two-tailed P-value of a z-test

Notes:

* See also COUNTIF (Maths) and COUNTBLANK (Information) and DCOUNT and DCOUNTA (Database) ** See also COMBIN function (Maths)

Statistical Function Arguments Argument

Description

A

An optional lower bound to the interval of x

Actual_range

The range of data that contains observations to test against expected values

Alpha

A parameter of a distribution

Array

An array or array formula, or a reference to a range of cells

Array1 (PEARSON function)

A set of independent values

Array2 (PEARSON function)

A set of dependent values

B

An optional lower bound to the interval of y

Beta

A parameter of a distribution

Bins_array

An array of or reference to intervals into which you want to group the values

Const

A logical value specifying whether to force the constant b to equal 1: TRUE or omitted, b is calculated normally; FALSE, b is set equal to 1 and the m-values are adjusted so that y = m^x

Criteria

The range of cells that contains the conditions you specify

Cumulative

A logical value that determines the form of the function

Data_array

An array of or reference to a set of values

Degrees_freedom

The number of degrees of freedom

Degrees_freedom1

The numerator degrees of freedom

Degrees_freedom2

The denominator degrees of freedom

Expected_range

The range of data that contains the ratio of the product of row totals and column totals to the grand total

K

Is the position in the array or cell range of data to return

Known_x's

The independent array or range of data

Known_y's

The dependent array or range of data

Lambda

The parameter value

Lower_limit

The lower bound for integrating ERF

Mean

The arithmetic mean

Mean (LOGNORMDIST and LOGINV)

The mean of ln(x).

New_x's

new x-values for which you want GROWTH to return corresponding y-values

Number_chosen

The number of items

Number_population

The population size

Number_s

The number of successes in trials

Number_sample

The size of the sample

Number1

The first number

Number2,…

Second of up to 30 numbers. You can also use a single array or a reference to an array instead of arguments separated by commas

Order

A number specifying how to rank number; 0 or omitted = descending; non-zero = ascending

Percent

The fractional number of data points to exclude from the calculation

Population_s

The number of successes in the population

Prob_range

A set of probabilities associated with values in x_range

Probability

A probability associated with the distribution

Probability_s

The probability of success on each trial

Quart

Indicates which value to return: 0 = minimum value; 1 = 1st Quartile (25th Percentile); 2 = 2nd Quartile; 3 = 3rd Quartile; 4 = Maximum value

Range

A range

Ref

An array of, or a reference to, a list of numbers

Sample_s

The number of successes in the sample

Sigma

The population (known) standard deviation. If omitted, the sample standard deviation is used

Significance

The multiple to which you want to round

Size

The sample size

Standard_dev

The population standard deviation for the data range

Standard_dev (LOGNORMDIST and The standard deviation of ln(x). LOGINV) Stats

A logical value specifying whether to return additional regression statistics TRUE returns additional statistics

Tails

Specifies the number of distribution tails to return. If tails = 1, TDIST returns the one-tailed distribution. If tails = 2, TDIST returns the two-tailed distribution

Trials

The number of independent trials

Type (TTEST function)

The kind of t-Test to perform; 1 = Paired; 2 = Two-sample equal variance (homoscedastic); 3 = Two-sample unequal variance (heteroscedastic)

Upper_limit

The upper bound for integrating ERF. If omitted, ERF integrates between zero and lower_limit

Value1

A numeric value

Value2,…

Second of 1 to n possible values (n=30 for AVERAGEA; n=29 for CHOOSE, NPV)

X

The value at which to evaluate the function

X_range

The range of numeric values of x with which there are associated probabilities

Z

The value for which you want the distribution

stical Functions Syntax =AVEDEV(Number1,Number2,…) =AVERAGE(Number1,Number2,…) =AVERAGEA(Value1,Value2,…)

=BETADIST(x,alpha,beta,A,B) =BETAINV(probability,alpha,beta,A,B) =BINOMDIST(number_s,trials,probability_s, cumulative) =CHIDIST(x,degrees_freedom) =CHIINV(probability,degrees_freedom) =CHITEST(actual_range,expected_range)

=CONFIDENCE(alpha,standard_dev,size) =CORREL(array1,array2) =COUNT(value1,value2, ...)

=COUNTA(value1,value2, ...) =COVAR(array1,array2)

=CRITBINOM(trials,probability_s,alpha)

=DEVSQ(number1,number2,...) =EXPONDIST(x,lambda,cumulative) =FDIST(x,degrees_freedom1, degrees_freedom2) =FINV(probability,degrees_freedom1, degrees_freedom2) =FISHER(x)

=FISHERINV(y) =FORECAST(x,known_y's,known_x's)

=FREQUENCY(data_array,bins_array)

=FTEST(array1,array2)

=GAMMADIST(x,alpha,beta,cumulative) =GAMMAINV(probability,alpha,beta) =GAMMALN(x) =GEOMEAN(number1,number2, ...) =GROWTH(known_y's,known_x's,new_x's,const)

=HARMEAN(number1,number2, ...)

=HYPGEOMDIST(sample_s,number_sample, population_s,number_population)

=INTERCEPT(known_y's,known_x's)

=KURT(number1,number2, ...)

=LARGE(array,k) =LINEST(known_y's,known_x's,const,stats)

=LOGEST(known_y's,known_x's,const,stats)

=LOGINV(probability,mean,standard_dev)

=LOGNORMDIST(x,mean,standard_dev)

=MAX(number1,number2,...) =MAXA(value1,value2,...) =MEDIAN(number1,number2, ...) =MIN(number1,number2,...) =MINA(value1,value2,...) =MODE(number1,number2,...) =NEGBINOMDIST(number_f,number_s, probability_s)

=NORMDIST(x,mean,standard_dev,cumulative)

=NORMINV(probability,mean,standard_dev)

=NORMSDIST(z)

=NORMSINV(probability) =PEARSON(array1,array2) =PERCENTILE(array,k) =PERCENTRANK(array,x,significance) =PERMUT(number,number_chosen)

=POISSON(x,mean,cumulative)

=PROB(x_range,prob_range,lower_limit, upper_limit)

=QUARTILE(array,quart) =RANK(number,ref,order) =RSQ(known_y's,known_x's)

=SKEW(number1,number2,...) =SLOPE(known_y's,known_x's)

=SMALL(array,k) =STANDARDIZE(x,mean,standard_dev)

=STDEV(number1,number2,...) =STDEVA(value1,value2,...) =STDEVP(number1,number2,...) =STDEVPA(value1,value2,...) =STEYX(known_y's,known_x's) =TDIST(x,degrees_freedom,tails)

=TINV(probability,degrees_freedom)

=TREND(known_y's,known_x's,new_x's,const)

=TRIMMEAN(array,percent)

=TTEST(array1,array2,tails,type)

=VAR(number1,number2,...) =VARA(value1,value2,...) =VARP(number1,number2,...) =VARPA(value1,value2,...) =WEIBULL(x,alpha,beta,cumulative) =ZTEST(array,x,sigma)

(Information) and DCOUNT and DCOUNTA (Database)

Function Arguments

nterval of x

observations to test against expected values

eference to a range of cells

nterval of y

vals into which you want to group the values

er to force the constant b to equal 1: normally; FALSE, b is set equal to 1 and the m-values are adjusted so that

the conditions you specify

he form of the function of values

om

om

edom

the ratio of the product of row totals and column totals to the grand total

l range of data to return of data

f data

ERF

t GROWTH to return corresponding y-values

ls

ou can also use a single array or a reference to an array instead of as

k number; 0 or omitted = descending; non-zero = ascending

oints to exclude from the calculation population with values in x_range distribution

ach trial

0 = minimum value; 1 = 1st Quartile (25th Percentile); 2 = 2nd Quartile; 3 = e

list of numbers sample

d deviation. If omitted, the sample standard deviation is used

o round

on for the data range

er to return additional regression statistics TRUE returns additional statistics

ion tails to return. If tails = 1, TDIST returns the one-tailed distribution. If tails d distribution

s

= Paired; 2 = Two-sample equal variance (homoscedastic); 3 = Two-sample tic) ERF. If omitted, ERF integrates between zero and lower_limit

s (n=30 for AVERAGEA; n=29 for CHOOSE, NPV)

he function

x with which there are associated probabilities

e distribution

Text Functions Name

Source

Description

ASC

Built In

Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters

BAHTTEXT

Built In

Converts a number to Thai text and adds a suffix of "Baht"

CHAR

Built In

Returns the ANSI character set (Microsoft) or Macintosh character set (Macintosh) specified by the code number

CLEAN

Built In

Removes all nonprintable characters from text

CODE

Built In

Returns a numeric code for the first character in a text string

CONCATENATE Built In

Joins several text items into one text item

DOLLAR

Built In

Converts a number to text, using currency format

EXACT

Built In

Checks to see whether two text values are identical

FIND

Built In

Finds one text value within another (case sensitive)

FINDB

Built In

Finds one text value within another (case sensitive) based on the number of bytes each character uses.

FIXED

Built In

Formats a number as text with a fixed number of decimals

JIS

Built In

Changes half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters.

LEFT

Built In

Returns the left most characters from a text value

LEFTB

Built In

Returns the first character or characters in a text string, based on the number of bytes you specify.

LEN

Built In

Returns the number of characters in a text string

LENB

Built In

Returns the number of bytes used to represent the characters in a text string

LOWER

Built In

Converts text to lowercase

MID

Built In

Returns a specific number of characters from a string starting at a specified position

MIDB

Built In

Returns a specific number of characters from a text string, starting at the position you specify, based on the number of bytes specified

PHONETIC

Built In

Extracts the phonetic (furigana) characters from a text string

PROPER

Built In

Capitalizes the first letter in each word of a text value

REPLACE

Built In

Replaces characters within text

REPLACEB

Built In

Replaces part of a text string, based on the number of bytes you specify, with a different text string.

REPT

Built In

Repeats text a given number of times

RIGHT

Built In

Returns the rightmost characters from a text value

RIGHTB

Built In

Returns the last character or characters in a text string, based on the number of bytes you specify

SEARCH

Built In

Finds one text value within another (not case sensitive) and returns the number of the starting position

SEARCHB

Built In

Finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text. The result is based on the number of bytes each character uses, beginning with start_num

SUBSTITUTE

Built In

Substitutes new text for old text in a text string

T

Built In

Converts its arguments to text

TEXT

Built In

Formats a number and converts it to text

TRIM

Built In

Removes all spaces from text except single spaces between words

UPPER

Built In

Converts text to uppercase

VALUE

Built In

Converts a text argument to a number

YEN

Built In

Converts a number to text, using the ¥ (yen) currency format, with the number rounded to a specified place

Text Function Arguments Argument

Description

Decimals

The number of digits to the right of the decimal point. If decimals is negative, number is rounded to the left of the decimal point. If you omit decimals, it is assumed to be 2

Find_text

The text you want to find

Format_text

A number format in text form from in the Category box on the Number tab in the Format Cells dialog box

Instance_num

Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text

New_text

The text that will replace characters in old_text

No_commas

A logical value that, if TRUE, prevents FIXED from including commas in the returned text

Num_bytes

The number of bytes you want to extract

Num_chars

The number of characters you want to extract

Number

A number or expression that evaluates to a number

Number_times

A positive number specifying the number of times to repeat text

Old_text

Text in which you want to replace some characters

Reference

Reference to a cell or range of cells and can refer to multiple areas. If you want to specify several references as a single argument, then you must include extra sets of parentheses so that Microsoft Excel will not interpret the comma as a field separator

Start_num

The character at which to start the search

Text

Text or a reference to a cell that contains the text you want to change

Text1

First item of text

Text2

Second item of text

Text2,…

Second of up to 30 items of text

Value

A value or an expression that evaluates to a value

Within_text

The text containing the text you want to find

ext Functions Syntax =ASC(Text)

=BAHTTEXT(number) =CHAR(Number)

=CLEAN(text) =CODE(text) =CONCATENATE (text1,text2,...) =DOLLAR(number,decimals) =EXACT(text1,text2) =FIND(find_text,within_text,start_num) =FINDB(find_text,within_text,start_num)

=FIXED(number,decimals,no_commas) =JIS(text)

=LEFT(text,num_chars) =LEFTB(text,num_bytes)

=LEN(text) =LENB(text) =LOWER(text) =MID(text,start_num,num_chars) =MIDB(text,start_num,num_bytes)

=PHONETIC(reference) =PROPER(text)

=REPLACE(old_text,start_num,num_chars, new_text) =REPLACEB(old_text,start_num,num_bytes, new_text)

=REPT(text,number_times) =RIGHT(text,num_chars) =RIGHTB(text,num_bytes)

=SEARCH(find_text,within_text,start_num)

=SEARCHB(find_text,within_text,start_num)

=SUBSTITUTE(text,old_text,new_text, instance_num) =T(value) =TEXT(value,format_text) =TRIM(text) =UPPER(text) =VALUE(text) =YEN(number,decimals)

unction Arguments

of the decimal point. If decimals is negative, number is rounded to the left of cimals, it is assumed to be 2

m in the Category box on the Number tab in the Format Cells dialog box

d_text you want to replace with new_text. If you specify instance_num, only ced. Otherwise, every occurrence of old_text in text is changed to new_text

ters in old_text

events FIXED from including commas in the returned text

o extract

want to extract

aluates to a number

e number of times to repeat text

e some characters

ells and can refer to multiple areas. If you want to specify several references must include extra sets of parentheses so that Microsoft Excel will not eparator

he search contains the text you want to change

aluates to a value

want to find

COMPLETE LIST OF FUNCTION ARGUMENTS Argument

Description

A

An optional lower bound to the interval of x

A1

A logical value that specifies the A1 (TRUE) or R1C1 (FALSE) reference style

Abs_num

The type of reference to return: 1 = Absolute; 2 = Abs Row / Rel Col; 3 = Rel Row / Abs Col; 4 = Relative

Actual_range

The range of data that contains observations to test against expected values

Alpha

A parameter of a distribution

Angle

The angle in radians

Area_num

Selects a range in reference from which to return the intersection of row_num and column_num

Argument1,…

The arguments to be passed to the procedure

Array

An array or array formula, or a reference to a range of cells

Array_x

The first array or range of values

Array_y

The second array or range of values

Array1 (PEARSON function)

A set of independent values

Array1 (SUMPRODUCT)

A cell range of values (up to 30 with SUMPRODUCT)

Array2 (PEARSON function)

A set of dependent values

Array2 (SUMPRODUCT)

A second cell range of values (up to 30 with SUMPRODUCT)

Array3,…

A third cell range of values (up to 30 with SUMPRODUCT)

B

An optional lower bound to the interval of y

Base

The base of the logarithm. If base is omitted, it is assumed to be 10

Basis (For Securities Functions)

The type of day count basis to use: 0 or omitted = US(NASD) 30/360 1 = Actual / Actual; 2 = Actual / 360; 3 = Actual / 365; 4 = European 30/360

Basis (For YEARFRAC)

The type of day count basis to use: 0 or omitted = US(NASD) 30/360; 1 = Actual / Actual; 2 = Actual / 360; 3 = Actual / 365; 4 = European 30/360

Basis(For Depreciation Functions)

Year basis to be used: 0 or omitted 360 (NASD); 1 = Actual; 2 = 365; 3 = 360 (European)

Beta

A parameter of a distribution

Bins_array

An array of or reference to intervals into which you want to group the values

Bottom

The smallest integer to be returned

Coefficients

A set of coefficients by which each successive power of x is multiplied. The number of values in coefficients determines the number of terms in the power series

Col_index_num

The column number in table_array from which the matching value must be returned

Col_names_logical

Indicates whether column names are returned as the first row of the results. TRUE if the column names to be returned as the first row of the results. FALSE if column names not wanted. If column_names_logical is omitted, SQL.REQUEST does not return column names

Cols

The number of columns, to the left (-) or right (+), that you want the upper-left cell of the result to refer to

Column_num

Column number

Connection_string

Supplies information, such as the data source name, user ID, and passwords, required by the driver being used to connect to a data source and must follow the driver's format

Const Cost

A logical value specifying whether to force the constant b to equal 1: TRUE or omitted, b is calculated normally FALSE, b is set equal to 1 and the m-values are adjusted so that y = m^x Cost of the asset

Coupon

The security's annual coupon rate

Criteria

The range of cells that contains the conditions you specify

Cumulative

A logical value that determines the form of the function

Data_array

An array of or reference to a set of values

Data_field

The name, enclosed in quotation marks, for the data field that contains the data

Database

The range of cells that makes up the list or database

Date_purchased

Date of purchase of the asset. Entered as an acceptable date in quotation marks or as a serial number or function returning a date serial number

Date_text

A date enclosed in quotation marks that is in a date text form acceptable to Excel. Note that acceptable date forms vary with system and Excel settings.

Dates

A schedule of payment dates that corresponds to the cash flow payments. The first payment date indicates the beginning of the schedule of payments. All other dates must be later than this date, but they may occur in any order

Day

A number representing the day of the month. If day is greater than the number of days in the month specified, day aggregates the month and year arguments appropriately and day represents the balance after this aggregation

Days

The number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date

Decimal_Dollar

A decimal number

Decimals

The number of digits to the right of the decimal point. If decimals is negative, number is rounded to the left of the decimal point. If you omit decimals, it is assumed to be 2

Degrees_freedom

The number of degrees of freedom

Degrees_freedom1

The numerator degrees of freedom

Degrees_freedom2

The denominator degrees of freedom

Denominator

The divisor

Discount

The security's discount rate

Divisor

The number by which you want to divide number

Driver_prompt

Specifies when the driver dialog box is displayed and which options are available

Effect_rate

The effective interest rate

End_Date

The last date as an acceptable date in inverted commas or as a serial number

End_Period

Last period number

Error_val

The error value whose identifying number you want: 1 = #Null!; 2 = #Div/0!; 3 = #Value!; 4 = #Ref!; 5 = #Name?; 6 = #N/A

Expected_range

The range of data that contains the ratio of the product of row totals and column totals to the grand total

Factor

The rate at which the balance declines. If factor is omitted, it is assumed to be 2 (the double-declining balance method)

Field

Indicates which column is used in the function as text in inverted commas or as number of field

Field1, Item1

One of up to 14 pairs of field names and item names that describe the data

Field2, Item2,…

Second of up to 14 pairs of field names and item names that describe the data

File_text

The name of the file that contains the code resource in Microsoft Excel for the Macintosh

Finance_rate

The interest rate you pay on the money used in the cash flows

Find_Text

The text you want to find

First_coupon

The security's first coupon date

First_Interest

A security's first interest date entered as an acceptable date in quotation marks or as a serial number or function returning a date serial number

First_Period

Date of the end of the first period. Entered as an acceptable date in quotation marks or as a serial number or function returning a date serial number

Form

A number specifying the type of roman numeral you want; 0 (Default) = Classic; 1-4 Progressively simplified; TRUE = Classic; FALSE = Most Simplified

Format_text

A number format in text form from in the Category box on the Number tab in the Format Cells dialog box

Fraction

The integer to use in the denominator of the fraction

Fractional_Dollar

A number expressed as a fraction

Frequency

The number of coupon payments per year for a security.

Friendly_name

The jump text or numeric value that is displayed in the cell

From_unit(1)

A unit of measurement:

From_unit(2)

Weight & Mass: "g" = gram; "sg" = slug; "lbm" = pound (avoirdupois); "u" = Atomic mass; "ozm" = ounces (avoirdupois) Distance: "m" = meter; "mi" = Statute Mile; "Nmi" = Nautical mile; "in" = inch; "ft" = foot; "yd" = yard; "ang" = Angstrom; "pica" = pica (1/72in) Time: "yr" = year; "day" = day; "hr" = hour; "mn" = minute; "sec" = second Pressure: "pa" = Pascal; "atm" = atmosphere; "mmHg" = mm of mercury Force: "N" = Newton; "dyn" = dyne; "lbf" = pound force Energy: "J" = Joule; "e" = erg; "c" Thermodynamic calorie; "cal" = IT calorie; "eV" = electron volt; "HPh" = horsepower-hour; "Wh" = Watt-hour; "flb" = foot-pound; "BTU" = BTU Power: "HP" = horsepower; "W" = Watt Magnetism: "T" = Tesler; "ga" = Gauss Temperature: "C" = degrees Celsius; "F" = degrees Fahrenheit; "K" = Kelvin Liquid Measure: "tsp" = teaspoon; "tbs" = tablespoon; "oz" = fluid ounce; "cup" = cup; "pt" = US Pint; "UK_pt" = UK pint; "qt" = quart; "gal" = gallon; "l" = liter

From_unit(3)

Prefix Abbreviations for metric: "E" = 1E+18 = exa; "P" = 1E+15 = peta; "T" 1E+12 = tera; "G" = 1E+09 = giga; "M" = 1E+06 = mega; "k" = 1E+03 = kilo; "h" = 1E+02 = hecto; "e" = 1E+01 = dekao; "d" = 1E-01 = deci; "c"=1E-02 = centi; "m" = 1E-03 = milli; "u" = 1E-06 = micro; "n" = 1E-09 = nano; "p" = 1E-12 = pico; "f" = 1E-15 = femto; "a" = 1E-18 = atto

Full_precision

A logical value; FALSE = currency specific rounding rules, TRUE = full precision

Function_num

The number 1 to 11 that specifies which function to use in calculating subtotals within a list: 1 = AVERAGE; 2 = COUNT; 3 = COUNTA; 4 = MAX; 5 = MIN; 6 = PRODUCT; 7 = STDEV; 8 = STDEVP; 9 = SUM; 10 = VAR; 11 = VARP

FV

Future Value

Guess

A number that you guess is close to the result of IRR

Height

The height, in number of rows, that you want the returned reference to be. Height must be a positive number

Holidays

An optional range of one or more dates to exclude from the working calendar. The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates

Hour

A number from 0 to 32767 representing the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value

I_num

Imaginary coefficient of the complex number

I_number

A complex number

Index_num

Specifies which value argument is selected. Index_num must be a number between 1 and 29, or a formula or reference to a cell containing a number between 1 and 29

Info_type

A text value that specifies what type of cell information you want: "Address" = first cell in reference "Col" = First column number in reference "Color" Returns 1 for negative colored, 0 otherwise "Contents" = Value of upper left cell in reference "Format" = Text value corresponding to number format "Prentheses" = Returns 1 for parenthese for positive or all values, 0 otherwise "Prefix" = Text value corresponding to label prefix "Protect" = returns 0 if unlucked, 1 if locked "Row" = First row number in reference "Type" = Text value for type of data; b for blank, l for label, v for value "Width" = Column width rounded to nearest integer

Instance_num

Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text

Inumber

A complex number

INumber1

First Complex number

INumber2

Second Complex

INumber2…

The second of 1-29 complex numbers

Investment

The amount invested in the security

Issue

A security's issue date entered as an acceptable date in quotation marks or as a serial number or function returning a date serial number

Item1

One of up to 14 pairs of field names and item names that describe the data

Item2,…

Second of up to 14 pairs of field names and item names that describe the data

K

Is the position in the array or cell range of data to return

Known_x's

The independent array or range of data

Known_y's

The dependent array or range of data

Lambda

The parameter value

Last_interest

The security's last coupon date

Life

The number of periods over which the asset is being depreciated (sometimes called the useful life of the asset)

Link_location

The path and file name to the document to be opened as text

Logical

A value or expression that can be evaluated to TRUE or FALSE

Logical_test

Any value or expression that can be evaluated to TRUE or FALSE

Logical1

Condition to be tested. Argument must evaluate to a logical value or be arrays or references that contain logical values

Logical2,…

Second of up to 30 conditions you want to test that can be either TRUE or FALSE

Lookup_array

A contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference

Lookup_value

The value to be found

Lookup_vector

A range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values

Lower_limit

The lower bound for integrating ERF

M

The step by which to increase n for each term in the series

Match_type

The number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array: 1 = (default) largest value less than or equal to look_up value The security's maturity 0 = first value to exactlydate equal lookup value -1 = smallest value greater than or equal to look_value

Maturity Mean

The arithmetic mean

Mean (LOGNORMDIST and LOGINV)

The mean of ln(x).

Method

FALSE or omitted = US (NASD) 30/360 method; TRUE = European 30/360 method

Minute

A number from 0 to 32767 representing the minute. Any value greater than 59 will be converted to hours and minutes

Module_text

Quoted text specifying the name of the dynamic link library (DLL) that contains the procedure in Microsoft Excel for Windows

Month (For DB function)

The number of months in the first year. If month is omitted, it is assumed to be 12

Month (For DATE function)

A number representing the month of the year. If month is greater than 12, month adds that number of months to the first month in the year specified

Month (For DB function)

The number of months in the first year. If month is omitted, it is assumed to be 12

Months

The number of months before or after start_date

Multiple

The multiple to which you want to round number

N

The order of the function

New_text

The text that will replace characters in old_text

New_x's

new x-values for which you want GROWTH to return corresponding y-values

No_commas

A logical value that, if TRUE, prevents FIXED from including commas in the returned text

No_switch

A logical value specifying whether to switch to straight-line depreciation when depreciation is greater than the declining balance calculation.If TRUE, Excel does not switch to straight-line depreciation even when the depreciation is greater than the declining balance calculation. FALSE or omitted, Excel switches to straightline depreciation when depreciation is greater than the declining balance calculation

Nominal_rate

The nominal interest rate

Nper

The number of periods

Npery

The number of compounding periods per year

Num_bytes

The number of bytes you want to extract

Num_chars

The number of characters you want to extract

Num_digits

Specifies the number of digits to which you want to round number: 0 = Integer; -numbers = to left of decimal; +numbers = to right of decimal

Number

A number or expression that evaluates to a number

Number_chosen

The number of items

Number_f

The number of failures

Number_population

The population size

Number_s

The number of successes in trials

Number_sample

The size of the sample

Number_times

A positive number specifying the number of times to repeat text

Number1

The first number

Number2

The second number

Number2,…

Second of up to 30 numbers. You can also use a single array or a reference to an array instead of arguments separated by commas

Numerator

The dividend

Old_text

Text in which you want to replace some characters

Order

A number specifying how to rank number; 0 or omitted = descending; non-zero = ascending

Output_ref

A cell reference where you want the completed connection string placed

Par

A security's par value. (Default = $1,000)

Per

The period for which you want to find the interest

Percent

The fractional number of data points to exclude from the calculation

Period

The number of the accounting period

Pivot_table

A reference to any cell, range of cells, or named range of cells in a PivotTable report

Places

The number of characters to use. If places is omitted, Excel uses the minimum number of characters necessary

Pmt

The payment made each period

Population_s

The number of successes in the population

Power

The exponent to which the base number is raised

Pr

The security's price per $100 face value

Principal

The present value

Prob_range

A set of probabilities associated with values in x_range

Probability

A probability associated with the distribution

Probability_s

The probability of success on each trial

Procedure

Text specifying the name of the function in the DLL. You can also use the ordinal value of the function from the EXPORTS statement in the module-definition file (.DEF).

ProgID

The name of the ProgID of a registered COM automation add-in that has been installed on the local computer. Enclose the name in quotation marks

PV

The Present Value

Quart

Indicates which value to return: 0 = minimum value; 1 = 1st Quartile (25th Percentile); 2 = 2nd Quartile; 3 = 3rd Quartile; 4 = Maximum value

Query_text

The SQL statement that you want to execute on the data source

Range

A range

Range_lookup

A logical value that specifies whether you want to find an exact match or an approximate match. If TRUE = approximate match is returned FALSE = Exact

Rate (For amortization functions)

The interest rate per period

Rate (For Depreciation Functions)

The rate of depreciation

Rate (For NPV and XNPV)

The discount rate

Rate (For Securities Functions)

A security's annual coupon rate

Real_num

The real coefficient of the complex number

Redemption

The security's redemption value per $100 face value

Ref

An array of, or a reference to, a list of numbers

Ref_text

Refers to another workbook (an external reference),

Ref1

First of up to 29 ranges or references for which you want the subtotal

Ref2,…

Second of up to 29 ranges or references for which you want the subtotal

Reference

Reference to a cell or range of cells and can refer to multiple areas. If you want to specify several references as a single argument, then you must include extra sets of parentheses so that Microsoft Excel will not interpret the comma as a field separator

Register_id

The value returned by a previously executed REGISTER or REGISTER.ID function

Reinjvest_rate

The interest rate you receive on the cash flows as you reinvest them

Resource

The name of the code resource in Microsoft Excel for the Macintosh. You can also use the resource ID number. The resource ID number must not be in the form of text

Result_vector

A range that contains only one row or column. It must be the same size as lookup_vector

Return_type (WEEKDAY only)

A number that determines the type of return value: 1 (or omitted) 1 = Sunday; 2 = 1 = Monday; 3 = 0 = Monday

Return_type (WEEKNUM only)

A number that determines on which day the week begins; 1 (or omitted) = starts Sunday nums 1 to 7; 2 = starts Monday nums 1 to 7

Row_index_num

The row number in table_array from which the matching value will be returned

Row_num

Row number

Rows

The number of rows, up (-) or down (+), that you want the upper-left cell to refer to

Salvage

Salvage value at the end of the life of the asset

Sample_s

The number of successes in the sample

Schedule

An array of interest rates to apply

Second

A number from 0 to 32767 representing the second. Any value greater than 59 will be converted to hours, minutes, and seconds

Serial_num

An Excel date / time serial number. Dates should be entered by using the DATE function, or as results of other formulas or functions. The Excel date serial number varies according to setting of 1900 or 1904 date system in Tools > Options > Calculation.

Serial_Number

A date / time serial number. Dates should be entered by using the DATE function, or as results of other formulas or functions

Server

Name of the server where the add-in should be run. If there is no server, and the program is run locally, leave the argument blank. Otherwise, enter quotation marks ("") around the server name

Settlement

A security's settlement date, which is the date after the issue date when the security is traded to the buyer. Entered as an acceptable date in quotation marks or as a serial number or function returning a date serial number

Sheet_text

Text specifying the name of the worksheet to be used as the external reference. If sheet_text is omitted, no sheet name is used

Sigma

The population (known) standard deviation. If omitted, the sample standard deviation is used

Significance

The multiple to which you want to round

Size

The sample size

Source

A three-letter string, or reference to a cell containing the string, corresponding to the ISO code for the source currency Belgium - franc - BEF Luxembourg - franc - LUF Germany - deutche mark - DEM Spain - peseta - ESP France - franc - FRF Ireland - pound - IEP Italy - lira - ITL Netherlands - guilder - NLG Austria - schilling - ATS Portugal - escudo - PTE Finland - markka -FIM Euro member states - euro - EUR Others may be added later: Denmark - krone - DKK Greece - drachma - GRD Sweden - krona - SEK UK - pound sterling - GBP

Standard_dev

The population standard deviation for the data range

Standard_dev (LOGNORMDIST and The standard deviation of ln(x). LOGINV) Start_date

The first date as an acceptable date in inverted commas or as a serial number

Start_num

The character at which to start the search

Start_Period

First period number

Stats

A logical value specifying whether to return additional regression statistics TRUE returns additional statistics

Step

The threshold value. If you omit a value for step, GESTEP uses zero

Suffix

The suffix for the imaginary component of the complex number. If omitted, suffix is assumed to be "i"

Sum_range

The actual cells to sum

Table_array

A table of information in which data is looked up

Tails

Specifies the number of distribution tails to return. If tails = 1, TDIST returns the one-tailed distribution. If tails = 2, TDIST returns the two-tailed distribution

Target

A three-letter string, or reference to a cell containing the string, corresponding to the ISO code for the source currency Belgium - franc - BEF Luxembourg - franc - LUF Germany - deutche mark - DEM Spain - peseta - ESP France - franc - FRF Ireland - pound - IEP Italy - lira - ITL Netherlands - guilder - NLG Austria - schilling - ATS Portugal - escudo - PTE Finland - markka -FIM Euro member states - euro - EUR Others may be added later: Denmark - krone - DKK Greece - drachma - GRD Sweden - krona - SEK UK - pound sterling - GBP

Text

Text or a reference to a cell that contains the text you want to change

Text1

First item of text

Text2

Second item of text

Text2,…

Second of up to 30 items of text

Time_text

A text string enclosed in quotation marks that represents a time in any one of the Excel time formats

To_unit(1)

A unit of measurement

To_unit(2)

Weight & Mass: "g" = gram; "sg" = slug; "lbm" = pound (avoirdupois); "u" = Atomic mass; "ozm" = ounces (avoirdupois) Distance: "m" = meter; "mi" = Statute Mile; "Nmi" = Nautical mile; "in" = inch; "ft" = foot; "yd" = yard; "ang" = Angstrom; "pica" = pica (1/72in) Time: "yr" = year; "day" = day; "hr" = hour; "mn" = minute; "sec" = second Pressure: "pa" = Pascal; "atm" = atmosphere; "mmHg" = mm of mercury Force: "N" = Newton; "dyn" = dyne; "lbf" = pound force Energy: "J" = Joule; "e" = erg; "c" Thermodynamic calorie; "cal" = IT calorie; "eV" = electron volt; "HPh" = horsepower-hour; "Wh" = Watt-hour; "flb" = foot-pound; "BTU" = BTU Power: "HP" = horsepower; "W" = Watt Magnetism: "T" = Tesler; "ga" = Gauss Temperature: "C" = degrees Celsius; "F" = degrees Fahrenheit; "K" = Kelvin Liquid Measure: "tsp" = teaspoon; "tbs" = tablespoon; "oz" = fluid ounce; "cup" = cup; "pt" = US Pint; "UK_pt" = UK pint; "qt" = quart; "gal" = gallon; "l" = liter

To_unit(3)

Prefix Abbreviations for metric: "E" = 1E+18 = exa; "P" = 1E+15 = peta; "T" 1E+12 = tera; "G" = 1E+09 = giga; "M" = 1E+06 = mega; "k" = 1E+03 = kilo; "h" = 1E+02 = hecto; "e" = 1E+01 = dekao; "d" = 1E-01 = deci; "c"=1E-02 = centi; "m" = 1E-03 = milli; "u" = 1E-06 = micro; "n" = 1E-09 = nano; "p" = 1E-12 = pico; "f" = 1E-15 = femto; "a" = 1E-18 = atto

Top

The largest integer to be returned

Topic1

First of up to 28 parameters that together represent a unique piece of real-time data

Topic2,…

Second of up to 28 parameters that together represent a unique piece of real-time data

Trials

The number of independent trials

Triangulation_precision

An integer equal to or greater than 3 that specifies the number of significant digits to be used for the intermediate euro value when converting between two euro member currencies

Type (Amortization functions)

0 = Payment in arrears; 1 = payment in advance

Type (TTEST function)

The kind of t-Test to perform; 1 = Paired; 2 = Two-sample equal variance (homoscedastic); 3 = Two-sample unequal variance (heteroscedastic)

Type_text (CALL function)

Text specifying the data type of the return value and the data types of all arguments to the DLL or code resource

Type_text (INFO function)

Text that specifies what type of information you want returned: "directory" = Path of current directory of folder; "memavail" = Amount of currently available memory in bytes; "memused" = Amount of memory being used for data; "numfile" = number of active worksheets in open workbooks; "origin" = cell reference of top leftmost cell visible in window, based on current scrolling position; "osversion" = current operating system version, as text; "recalc" = current recalculation mode; "release" = version of Excel; "system" = name of the operating environment (Mac or pcdos); "totmem" = total memory available

Unit

"y" = years; "m" = months; "d" = days; "md" = days ignoring months and years; "ym" = years and months; "yd" = years and days

Upper_limit

The upper bound for integrating ERF. If omitted, ERF integrates between zero and lower_limit

Value

A value or an expression that evaluates to a value

Value_If_False

The value that is returned if logical_test is FALSE

Value_If_True

The value that is returned if logical_test is TRUE

Value1

A numeric value

Value2,…

Second of 1 to n possible values (n=30 for AVERAGEA; n=29 for CHOOSE, NPV)

Values

An array or a reference to cells that contain numbers

Values (IRR and MIRR)

An array or a reference to cells that contain numbers for which you want to calculate the internal rate of return. Values must contain at least one positive value and one negative value to calculate the internal rate of return. If an array or reference argument contains text, logical values, or empty cells, those values are ignored.

Values (XIRR and XNPV)

A series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive value and one negative value

Width

The width, in number of columns, that you want the returned reference to be. Width must be a positive number

Within_Text

The text containing the text you want to find

X

The value at which to evaluate the function

x_Num

The x-coordinate of the point

X_range

The range of numeric values of x with which there are associated probabilities

y_Num

The y-coordinate of the point

Year

The year number as one to four digits

Yld

The security's annual yield

Z

The value for which you want the distribution

OF FUNCTION ARGUMENTS

nterval of x A1 (TRUE) or R1C1 (FALSE) reference style

Col; 3 = Rel Row / Abs Col; 4 = Relative

observations to test against expected values

m which to return the intersection of row_num and column_num

the procedure

eference to a range of cells

s

lues

with SUMPRODUCT)

p to 30 with SUMPRODUCT)

o 30 with SUMPRODUCT)

nterval of y

se is omitted, it is assumed to be 10

se: 0 or omitted = US(NASD) 30/360 360; 3 = Actual / 365; 4 = European 30/360

se: 0; 1 = Actual / Actual; 2 = Actual / 360; 30/360

ctual; 2 = 365; 3 = 360 (European)

vals into which you want to group the values

ned

ch successive power of x is multiplied. The number of values in coefficients in the power series

ay from which the matching value must be returned

s are returned as the first row of the results. TRUE if the column names to e results. FALSE if column names not wanted. If column_names_logical is ot return column names

eft (-) or right (+), that you want the upper-left cell of the result to refer to

e data source name, user ID, and passwords, required by the driver being e and must follow the driver's format

er to force the constant b to equal 1: normally he m-values are adjusted so that y = m^x

te

the conditions you specify

he form of the function of values

n marks, for the data field that contains the data

p the list or database

ntered as an acceptable date in quotation marks or as a serial number or umber

rks that is in a date text form acceptable to Excel. Note that acceptable date el settings.

at corresponds to the cash flow payments. The first payment date indicates payments. All other dates must be later than this date, but they may occur

of the month. If day is greater than the number of days in the month onth and year arguments appropriately and day represents the balance

d nonholiday days before or after start_date. A positive value for days yields ields a past date

of the decimal point. If decimals is negative, number is rounded to the left of cimals, it is assumed to be 2

om

om

edom

to divide number box is displayed and which options are available

date in inverted commas or as a serial number

g number you want: ue!; 4 = #Ref!; 5 = #Name?; 6 = #N/A

the ratio of the product of row totals and column totals to the grand total

eclines. If factor is omitted, it is assumed to be 2 (the double-declining

n the function as text in inverted commas or as number of field

mes and item names that describe the data names and item names that describe the data

s the code resource in Microsoft Excel for the Macintosh money used in the cash flows

tered as an acceptable date in quotation marks or as a serial number or umber

d. Entered as an acceptable date in quotation marks or as a serial number al number

roman numeral you want; 0 (Default) = Classic; 1-4 Progressively simplified; Simplified

m in the Category box on the Number tab in the Format Cells dialog box

inator of the fraction

on

s per year for a security.

hat is displayed in the cell

" = slug; "lbm" = pound (avoirdupois); "u" = Atomic mass; "ozm" = ounces

atute Mile; "Nmi" = Nautical mile; "in" = inch; "ft" = foot; "yd" = yard; (1/72in) hr" = hour; "mn" = minute; "sec" = second = atmosphere; "mmHg" = mm of mercury ne; "lbf" = pound force c" Thermodynamic calorie; "cal" = IT calorie; "eV" = electron volt; = Watt-hour; "flb" = foot-pound; "BTU" = BTU = Watt Gauss elsius; "F" = degrees Fahrenheit; "K" = Kelvin on; "tbs" = tablespoon; "oz" = fluid ounce; "cup" = cup; t; "qt" = quart; "gal" = gallon; "l" = liter

c: "E" = 1E+18 = exa; "P" = 1E+15 = peta; "T" 1E+12 = tera; "G" = 1E+09 = 1E+03 = kilo; "h" = 1E+02 = hecto; "e" = 1E+01 = dekao; "d" = 1E-01 = E-03 = milli; "u" = 1E-06 = micro; "n" = 1E-09 = nano; "p" = 1E-12 = pico; "f" = o

cy specific rounding rules, TRUE = full precision

es which function to use in calculating subtotals within a list: COUNTA; 4 = MAX; 5 = MIN; 6 = PRODUCT; 7 = STDEV; 8 = STDEVP; 9

e to the result of IRR

hat you want the returned reference to be. Height must be a positive number

e dates to exclude from the working calendar. The list can be either a range r an array constant of the serial numbers that represent the dates

senting the hour. Any value greater than 23 will be divided by 24 and the hour value

plex number

is selected. Index_num must be a number between 1 and 29, or a formula a number between 1 and 29

ype of cell information you want: e eference olored, 0 otherwise cell in reference nding to number format renthese for positive or all values, 0 otherwise ing to label prefix 1 if locked erence ata; b for blank, l for label, v for value d to nearest integer

d_text you want to replace with new_text. If you specify instance_num, only ced. Otherwise, every occurrence of old_text in text is changed to new_text

mbers

urity

as an acceptable date in quotation marks or as a serial number or function

mes and item names that describe the data names and item names that describe the data

l range of data to return of data

f data

ch the asset is being depreciated (sometimes called the useful life of the

ocument to be opened as text

e evaluated to TRUE or FALSE

n be evaluated to TRUE or FALSE

nt must evaluate to a logical value or be arrays or references that contain

ou want to test that can be either TRUE or FALSE

aining possible lookup values. Lookup_array must be an array or an array

ow or one column. The values in lookup_vector can be text, numbers, or

ERF for each term in the series

ype specifies how Microsoft Excel matches lookup_value with values in

han or equal to look_up value okup value or equal to look_value

30/360 method; TRUE = European 30/360 method

senting the minute. Any value greater than 59 will be converted to hours

e of the dynamic link library (DLL) that contains the procedure in Microsoft

st year. If month is omitted, it is assumed to be 12

th of the year. If month is greater than 12, month adds that number of year specified

st year. If month is omitted, it is assumed to be 12

r after start_date

o round number

ters in old_text

t GROWTH to return corresponding y-values

events FIXED from including commas in the returned text

er to switch to straight-line depreciation when depreciation is greater than n.If TRUE, Excel does not switch to straight-line depreciation even when the declining balance calculation. FALSE or omitted, Excel switches to straighttion is greater than the declining balance calculation

riods per year

o extract

want to extract which you want to round number: 0 = Integer; -numbers = to left of decimal;

aluates to a number

ls

e number of times to repeat text

ou can also use a single array or a reference to an array instead of as

e some characters

k number; 0 or omitted = descending; non-zero = ascending the completed connection string placed

= $1,000)

o find the interest

oints to exclude from the calculation

eriod cells, or named range of cells in a PivotTable report

e. If places is omitted, Excel uses the minimum number of characters

population number is raised

ce value

with values in x_range distribution

ach trial

function in the DLL. You can also use the ordinal value of the function from module-definition file (.DEF).

gistered COM automation add-in that has been installed on the local quotation marks

0 = minimum value; 1 = 1st Quartile (25th Percentile); 2 = 2nd Quartile; 3 = e

nt to execute on the data source

ether you want to find an exact match or an approximate match. If TRUE = FALSE = Exact

ex number per $100 face value

list of numbers

external reference),

ences for which you want the subtotal

ferences for which you want the subtotal

ells and can refer to multiple areas. If you want to specify several references must include extra sets of parentheses so that Microsoft Excel will not eparator

sly executed REGISTER or REGISTER.ID function the cash flows as you reinvest them

in Microsoft Excel for the Macintosh. You can also use the resource ID er must not be in the form of text

ow or column. It must be the same size as lookup_vector

pe of return value: 1 (or omitted) 1 = Sunday; 2 = 1 = Monday; 3 = 0 =

hich day the week begins; 1 (or omitted) = starts Sunday nums 1 to 7; 2 =

rom which the matching value will be returned

own (+), that you want the upper-left cell to refer to

ife of the asset sample

y

senting the second. Any value greater than 59 will be converted to hours,

ber. Dates should be entered by using the DATE function, or as results of Excel date serial number varies according to setting of 1900 or 1904 date culation.

es should be entered by using the DATE function, or as results of other

dd-in should be run. If there is no server, and the program is run locally, wise, enter quotation marks ("") around the server name

ch is the date after the issue date when the security is traded to the buyer. n quotation marks or as a serial number or function returning a date serial

worksheet to be used as the external reference. If sheet_text is omitted, no

d deviation. If omitted, the sample standard deviation is used

o round

e to a cell containing the string, corresponding to the ISO code for the source

bourg - franc - LUF Germany - deutche mark - DEM - franc - FRF Ireland - pound - IEP Italy - lira - ITL ustria - schilling - ATS Portugal - escudo - PTE member states - euro - EUR

ce - drachma - GRD

Sweden - krona - SEK

on for the data range

date in inverted commas or as a serial number

he search

er to return additional regression statistics TRUE returns additional statistics

a value for step, GESTEP uses zero

ponent of the complex number. If omitted, suffix is assumed to be "i"

ata is looked up

ion tails to return. If tails = 1, TDIST returns the one-tailed distribution. If tails d distribution

e to a cell containing the string, corresponding to the ISO code for the source

bourg - franc - LUF Germany - deutche mark - DEM - franc - FRF Ireland - pound - IEP Italy - lira - ITL ustria - schilling - ATS Portugal - escudo - PTE member states - euro - EUR

ce - drachma - GRD

Sweden - krona - SEK

contains the text you want to change

on marks that represents a time in any one of the Excel time formats

" = slug; "lbm" = pound (avoirdupois); "u" = Atomic mass; "ozm" = ounces

atute Mile; "Nmi" = Nautical mile; "in" = inch; "ft" = foot; "yd" = yard; (1/72in) hr" = hour; "mn" = minute; "sec" = second = atmosphere; "mmHg" = mm of mercury ne; "lbf" = pound force c" Thermodynamic calorie; "cal" = IT calorie; "eV" = electron volt; = Watt-hour; "flb" = foot-pound; "BTU" = BTU = Watt Gauss elsius; "F" = degrees Fahrenheit; "K" = Kelvin on; "tbs" = tablespoon; "oz" = fluid ounce; "cup" = cup; t; "qt" = quart; "gal" = gallon; "l" = liter

c: "E" = 1E+18 = exa; "P" = 1E+15 = peta; "T" 1E+12 = tera; "G" = 1E+09 = 1E+03 = kilo; "h" = 1E+02 = hecto; "e" = 1E+01 = dekao; "d" = 1E-01 = E-03 = milli; "u" = 1E-06 = micro; "n" = 1E-09 = nano; "p" = 1E-12 = pico; "f" = o

d together represent a unique piece of real-time data

hat together represent a unique piece of real-time data

s

an 3 that specifies the number of significant digits to be used for the onverting between two euro member currencies

ment in advance

= Paired; 2 = Two-sample equal variance (homoscedastic); 3 = Two-sample tic)

the return value and the data types of all arguments to the DLL or code

nformation you want returned: ctory of folder; y available memory in bytes; y being used for data; rksheets in open workbooks; ftmost cell visible in window, based on current scrolling position; system version, as text; mode;

g environment (Mac or pcdos); ble

days; "md" = days ignoring months and years; "ym" = years and months; ERF. If omitted, ERF integrates between zero and lower_limit

aluates to a value

cal_test is FALSE

cal_test is TRUE

s (n=30 for AVERAGEA; n=29 for CHOOSE, NPV)

hat contain numbers

hat contain numbers for which you want to calculate the internal rate of east one positive value and one negative value to calculate the internal rate e argument contains text, logical values, or empty cells, those values are

sponds to a schedule of payments in dates. The first payment is optional yment that occurs at the beginning of the investment. If the first value is a gative value. All succeeding payments are discounted based on a 365-day contain at least one positive value and one negative value

s, that you want the returned reference to be. Width must be a positive

want to find

he function

x with which there are associated probabilities

digits

e distribution