Facilitator: Prof.

Eduard Gómez
THE FUNCTIONS

A function is a predefined formula by Excel (or by the user) that operates with one or more values and
returns a result that will appear directly in the cell or will be used to calculate the formula that it
contains.

Function Syntax: name(argument1;argument2;...;argumentN)

The following rules apply:

If the function starts at the beginning of a formula, it must begin with the sign =.
The arguments or input values always go in parentheses. Do not leave spaces before or
after each parenthesis.
Arguments can be constant values (number or text), formulas, or functions.
Arguments must be separated by a semicolon ';'.

Example: =SUM(A1:C8)

We have the SUM() function that returns the sum of its arguments. The operator ':'
it identifies a range of cells, so A1:C8 indicates all the cells included between cell A1 and C8, thus the
the previous function would be equivalent to:

=A1+A2+A3+A4+A5+A6+A7+A8+B1+B2+B3+B4+B5+B6+B7+B8+C1+C2+C3+C4+C5+C6+C7+C8

In this example, the advantage of using the function can be appreciated.

Formulas can contain more than one function, and functions can appear nested within.
formula.

=SUM(A1:B4)/SUM(C1:D4)

There are many types of functions depending on the type of operation or calculation they perform. Thus there are
mathematical and trigonometric functions, statistical, financial, text, date and time, logical, database
of data, of search and reference, and of information.

To enter a formula, it must be typed in any cell just as we enter any text.
always preceded by the sign =.

LIST OF FUNCTIONS

Financial functions

Function Description
Straight Line Amortization
Return the amortization of each of the accounting periods.
AMORTIZ.PROGRE Returns the depreciation of each accounting period by using a depreciation coefficient.
COUPON.DAYS Returns the number of days of the period (between two coupons) in which the settlement date is located.
COUPON.DAYS.L1 Returns the number of days from the beginning of the coupon period to the settlement date.
COUPON.DAYS.L2 Returns the number of days from the settlement date to the date of the next coupon.
COUPON.DATE.L1 Returns the coupon date prior to the settlement date.
COUPON.DATE.L2 Returns the date of the next coupon after the settlement date.
CUPON.NUM Returns the number of coupon payments between the settlement date and the maturity date.
DB Returns the depreciation of an asset over a specific period using the straight-line depreciation method.
DDB Returns the depreciation of an asset over a specific period using the double depreciation method.
decrease in balance or another method that is specified.
DVS Returns the depreciation of an asset over a specified period using the accelerated depreciation method
with a double rate and according to the specified coefficient.
DURATION Returns the annual duration of a security with periodic interest payments.
INT.ACCUM Returns the accumulated interest of a security with periodic interest payments.
INT.ACUM.V Returns the accumulated interest of a security with interest payments at maturity.
INT.CASH Return the effective annual interest rate.
 Facilitator: Prof. Eduard Gómez
INT.PAY.DIR Calculate the interest paid during a specific period of an investment. This function is included to provide
compatibility with Lotus 1-2-3.
CURRENCY.DEC Convert a stock price expressed in fractional form into a stock price
expressed in decimal form.
CURRENCY.FRAC Convert a stock price quoted in decimal form into a stock price.
expressed in fractional form.
NPER Returns the number of payments of an investment, based on constant and periodic payments and an interest rate.
constant.
PAYMENT. BETWEEN Returns the accumulated interest paid between two periods.
PRINCIPAL PAYMENT BETWEEN Returns the accumulated capital paid on a loan between two periods.
PAYMENT Returns the interest payment on an investment over a specified period.
PAGOPRIN It returns the payment of a capital from a given investment, based on constant and periodic payments and a rate.
of constant interest.
SYD Returns the depreciation by annuity method of an asset over a specific period.
RATE Returns the interest rate per period of a loan or an investment.
RATE.DESC Returns the discount rate of a market value
TASA.INT Return the interest rate for the total investment of a stock value
TIR Returns the internal rate of return of an investment for a series of cash values.
TIRM Returns the modified internal rate of return for a series of periodic cash flows, considering cost of the
investment and interest when reinvesting the cash.
VA Returns the current value of an investment. The current value is the value that the sum of a series currently has.
payments that are made in the future.
VF Returns the future value of an investment.
VF.PLAN Returns the future value of an initial capital after applying a series of compound interest rates.
VNA Returns the net present value of an investment based on a discount rate and a series of future payments.

2. Text functions

Function Description
CHARACTER Returns the character specified by the code number.
CODE Returns a numeric code of the first character of a text string.
CONCATENATE Concatenate several text elements into one.
DECIMAL Format a number as text with a fixed number of decimals.
RIGHT, RIGHTED Returns the right characters of a text value.
FIND, FINDB Searches for a text value within another (case-sensitive).
EXTRA, EXTRAED Returns a specific number of characters from a text string that starts at the position that
specify.
FIND, FOUND Find a text value within another (case insensitive).
SAME Check if two text values are identical.
LEFT, LEFT Returns the characters from the left side of a text value.
LONG, LONGER Returns the number of characters in a text string.
CLEAN Remove all non-printable characters from the text.
UPPERCASE / LOWERCASE Convert the text to uppercase or lowercase respectively.
CURRENCY Convert a number into text, in the currency format $ (dollar).
NOMPROPIO Capitalize The First Letter Of Each Word In A Text Value.
REPLACE, Replace text characters.
REPLACE
REPEAT Repeat the text a certain number of times.
SUBSTITUTE Replace new text with old text in a string.
T If the value is text, it returns it, and if not, it returns an empty string.
TEXTBAHT Convert a number into text, in the currency format ß (Baht).

3. Date and time functions

Function Description
NOW Returns the serial number corresponding to the current date and time.
YEAR Convert a serial number into a year value.
DAY Convert a serial number into a day of the month.
DIA.LAB Returns the serial number of the date that occurs before or after a specified number of working days.
DIA.LAB.INTL Returns the serial number of the date before or after a specified number of working days.
DIAS.LAB Returns the number of all working days existing between two dates.
DIAS360 Calculate the number of days between two dates based on a 360-day year.
DAY Convert a serial number into a day of the week value.
DATE Returns the serial number corresponding to a given date.
DATE.MONTH Returns the serial number of the date equivalent to the indicated number of months before or after the date.
 Prof. Eduard Gómez
initial.
FECHANUMERO Converts a date in text format into a serial number value.
FIN.MES Returns the serial number corresponding to the last day of the month preceding or following a number of months.
specified.
FRAC.YEAR Returns the fraction of the year that represents the total number of days between the initial date and the date.
final
HOUR Convert a serial number into a time value.
TODAY Returns the serial number corresponding to the current day.
MES Convert a serial number into a month value.
MINUTE Convert a serial number into a minute value.
NSHORA Returns the serial number corresponding to a specific time.
WEEKNUM Converts a serial number into a number that represents the numeric position corresponding to a week of a year
SECOND Convert a serial number into a value of seconds.
VALHORA Convert a time in text format into a serial number value.

4. Search functions

Function Description
AREAS Returns the number of areas of a reference.
SEARCH Search for values in a vector or a matrix.
SEARCHH Look in the top row of a matrix and return the value of the specified cell.
VLOOKUP Search in the first column of a matrix and move horizontally across the row to return the value of
a cell.
COINCIDE Search for values of a reference or matrix.
COLUMN Returns the column number of a reference.
COLUMNS Returns the number of columns in a reference.
DESREF Returns a reference offset with respect to a given reference.
ADDRESS Returns a reference as text to a single cell in a spreadsheet.
CHOOSE Choose a value from a list of values.
FILA Returns the row number of a reference.
ROWS Returns the number of rows of a reference.
HYPERLINK Create a shortcut or link that opens a document stored on a network server, in a
intranet or on the Internet.
IMPORTARDATOSDINAMICOS Returns the data stored in a pivot table report.
INDEX Use an index to choose a value from a reference or array.
INDIRECT Returns a reference indicated by a text value.
transpose Returns the transpose of a matrix.

5. Other functions

Function Description
Mathematical and trigonometric functions
ABS Returns the absolute value of a number.
RANDOM Returns a number between 0 and 1.
RANDOM.BETWEEN Returns a random number between the specified numbers.
COMBINE Returns the number of combinations for a given number of elements.
ENTERO Round a number down to the nearest lower integer.
EXP Calculate raising 'e' to the power of a certain number.
FACT Returns the factorial of a number.
ROMAN.NUMBER Convert the given number in decimal format to Roman numeral.
PI Returns the value of the constant pi.
POWER Calculate raising a number to the indicated power.
PRODUCT Returns the result of multiplying all the numbers passed as arguments.
ROOT Return the square root of the indicated number.
RESIDUE Return the remainder of the division.
SUM Sum all the numbers in a range of cells.
Statistical functions
COUNTIF Count the cells in the range that match the given condition.
MAX Returns the maximum value of the list of values.
MIN Returns the minimum value of the list of values.
MEDIAN Returns the median of the list of values.
FASHION Returns the value that appears most frequently in the list of values.
AVERAGE Return the arithmetic mean of the list of values.
VAR Returns the variance of a list of values.
Kth Largest Returns the k-th largest value of a data set.
 Facilitator: Prof. Eduard Gómez

K-th smallest Returns the k-th smallest value from a dataset.

Logical functions
FALSE Returns the logical value False.
TRUE Returns the logical value True
YES Returns one value or another, depending on whether a condition is met or not.
NO Invert the provided logical value.
Y Check if all values are true.
O Check if any logical value is true and return TRUE.
Information functions
IN WHITE Check if it refers to an empty cell.
ESERR Check if a value is an error.
IT IS LOGICAL Check if a value is logical
ISNOTTEXT Check if a value is not of type text.
THIS TEXT Check if a value is of type text.
ISNUMBER Check if a value is of numeric type.
TYPE Returns a number that represents the data type of the value.

Consulted Source:

http://www.aulaclic.es/excel2007/index.htm