DATE AND TIME FUNCTIONS
TODAY
Returns the serial
number of the current date. The serial number is the date-time code used by
Microsoft Excel for date and time calculations. If the cell format was General
before the function was entered, the result is formatted as a date.
Syntax:
TODAY( )
NOW
Returns the serial
number of the current date and time. If the cell format was General before the function
was entered, the result is formatted as a date.
Syntax:
NOW( )
WEEKDAY
Returns the day of
the week corresponding to a date. The day is given as an integer, ranging from
1 (Sunday) to 7 (Saturday), by default.
Syntax:
WEEKDAY(serial_number,return_type)
Serial_number is a sequential number that
represents the date of the day you are trying to find. Return_type is
a number that determines the type of return value.
|
Return_type |
Number returned |
|
1 or
omitted |
Numbers
1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft
Excel. |
|
2 |
Numbers
1 (Monday) through 7 (Sunday). |
|
3 |
Numbers
0 (Monday) through 6 (Sunday). |
NETWORKDAYS
Returns the number of whole working days between start_date
and end_date. Working days exclude weekends and any
dates identified in holidays. Use NETWORKDAYS to calculate employee benefits
that accrue based on the number of days worked during a specific term.
Syntax
NETWORKDAYS(start_date,end_date,holidays)
Important Dates should be
entered by using the DATE function, or as results of other formulas or
functions. For example, use DATE(2008,5,23) for the
23rd day of May, 2008. Problems can occur if dates are entered as text.
Start_date is a date that
represents the start date.
End_date is a date that
represents the end date.
Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. 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.
FINANCIAL FUNCTIONS
PMT
Calculates the payment for a loan based on
constant payments and a constant interest rate.
Syntax
PMT(rate,nper,pv,fv,type)
·
For
a more complete description of the arguments in PMT, see the PV function.
·
Rate
is the interest rate for the loan.
·
Nper is the total number of payments for the loan.
·
Pv is the present value,
or the total amount that a series of future payments is worth now; also known
as the principal.
·
Fv
is the future value, or a cash balance you want to attain after the last
payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the
future value of a loan is 0.
·
Type
is the number 0 (zero) or 1 and indicates when payments are due.
|
Set type equal to |
If payments are due |
|
0 or
omitted |
At the
end of the period |
|
1 |
At the
beginning of the period |
PPMT
Returns the
payment on the principal for a given period for an investment based on
periodic, constant payments and a constant interest rate.
Syntax
PPMT(rate,per,nper,pv,fv,type)
·
Rate
is the interest rate per period.
·
Per
specifies the period and must be in the range 1 to nper.
·
Nper is the total number of payment periods in an annuity.
·
Pv is the present
value — the total amount that a series of future payments is worth now.
·
Fv
is the future value, or a cash balance you want to attain after the last
payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the
future value of a loan is 0.
·
Type
is the number 0 or 1 and indicates when payments are due.
|
Set type equal to |
If payments are due |
|
0 or
omitted |
At the
end of the period |
|
1 |
At the beginning
of the period |
IPMT
Returns the
interest payment for a given period for an investment based on periodic,
constant payments and a constant interest rate. For a more complete description
of the arguments in IPMT and for more information about annuity functions, see
PV.
Syntax
IPMT(rate,per,nper,pv,fv,type)
·
Rate
is the interest rate per period.
·
Per
is the period for which you want to find the interest and must be in the range
1 to nper.
·
Nper is the total number of payment periods in an annuity.
·
Pv is the present value,
or the lump-sum amount that a series of future payments is worth right now.
·
Fv
is the future value, or a cash balance you want to attain after the last
payment is made. If fv is omitted, it is assumed to be 0 (the future value of a
loan, for example, is 0).
·
Type
is the number 0 or 1 and indicates when payments are due. If type is omitted,
it is assumed to be 0.
|
Set type equal to |
If payments are due |
|
0 |
At the
end of the period |
|
1 |
At the
beginning of the period |
CUMIPMT
Returns the
cumulative interest paid on a loan between start_period
and end_period.
Syntax
CUMIPMT(rate,nper,pv,start_period,end_period,type)
·
Rate
is the interest rate.
·
Nper is the total number of payment periods.
·
Pv is the present value.
·
Start_period is the first period in the calculation.
Payment periods are numbered beginning with 1.
·
End_period is the last period in the calculation.
·
Type
is the timing of the payment.
|
Type |
Timing |
|
0 (zero) |
Payment
at the end of the period |
|
1 |
Payment
at the beginning of the period |
SLN
Returns the
straight-line depreciation of an asset for one period.
Syntax
SLN (cost,salvage,life)
·
Cost
is the initial cost of the asset.
·
Salvage
is the value at the end of the depreciation (sometimes called the salvage value
of the asset).
·
Life
is the number of periods over which the asset is depreciated (sometimes called
the useful life of the asset).
Example
The example may be
easier to understand if you copy it to a blank worksheet.
|
|
|
FV
Returns the future
value of an investment based on periodic, constant payments and a constant
interest rate.
Syntax
FV(rate,nper,pmt,pv,type)
·
Rate
is the interest rate per period.
·
Nper is the total number of payment periods in an annuity.
·
Pmt
is the payment made each period; it cannot change over the life of the annuity.
Typically, pmt contains principal and interest but no other fees or taxes. If
pmt is omitted, you must include the pv
argument.
·
Pv is the present value,
or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be
0 (zero), and you must include the pmt argument.
·
Type
is the number 0 or 1 and indicates when payments are due. If type is omitted,
it is assumed to be 0.
|
Set type equal to |
If payments are due |
|
0 |
At the
end of the period |
|
1 |
At the
beginning |
LOGICAL FUNCTIONS
COUNT
Counts the number of
cells that contain numbers and also numbers within the list of arguments. Use COUNT to get the number of entries in a number field that's in
a range or array of numbers.
Syntax
COUNT(value1,value2,...)
Value1, value2, ... are 1 to 30 arguments that can
contain or refer to a variety of different types of data, but only numbers are
counted.
Syntax
COUNTIF(range,criteria)
Range is the range
of cells from which you want to count cells.
Criteria is the criteria in the form of a
number, expression, or text that defines which cells will be counted. For
example, criteria can be expressed as 32, "32", ">32",
"apples".
IF
Returns one value if a condition you specify evaluates to TRUE and another
value if it evaluates to FALSE.
Use
IF to conduct conditional tests on values and formulas.
Syntax
IF(logical_test,value_if_true,value_if_false)
Logical_test is any value or
expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a
logical expression; if the value in cell A10 is equal to 100, the expression
evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument
can use any comparison
calculation operator.
Value_if_true is the value that is
returned if logical_test is TRUE. For example, if
this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, then the IF
function displays the text "Within budget". If logical_test
is TRUE and value_if_true is blank, this argument
returns 0 (zero). To display the word TRUE, use the logical value TRUE for this
argument. Value_if_true can be another formula.
Value_if_false is the value that is
returned if logical_test is FALSE. For example, if
this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, then the IF
function displays the text "Over budget". If logical_test
is FALSE and value_if_false is omitted, (that is,
after value_if_true, there is no comma), then the
logical value FALSE is returned. If logical_test is
FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing
parenthesis), then the value 0 (zero) is returned. Value_if_false
can be another formula.
CHOOSE
Uses index_num to return a value from the list of
value arguments. Use CHOOSE to select one of up to 29
values based on the index number. For example, if value1 through value7 are the
days of the week, CHOOSE returns one of the days when a number between 1 and 7
is used as index_num.
Syntax
CHOOSE(index_num,value1,value2,...)
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.
·
If
index_num is 1, CHOOSE returns value1; if it is 2,
CHOOSE returns value2; and so on.
·
If
index_num is less than 1 or greater than the number
of the last value in the list, CHOOSE returns the #VALUE! error
value.
·
If
index_num is a fraction, it is truncated to the
lowest integer before being used.
Value1,value2,... are 1 to 29 value arguments from
which CHOOSE selects a value or an action to perform based on index_num. The arguments can be numbers, cell references,
defined names, formulas, functions, or text.
ROUND
Rounds a number to a specified number of digits.
Syntax
ROUND(number,num_digits)
Number is the
number you want to round.
Num_digits specifies the number of
digits to which you want to round number.
ROUNDUP
Rounds a number up, away from 0 (zero).
Syntax
ROUNDUP(number,num_digits)
Number
is any real number that you want rounded up.
Num_digits is the number of
digits to which you want to round number.
ROUNDDOWN
Rounds a number down, toward zero.
Syntax
ROUNDDOWN(number,num_digits)
Number
is any real number that you want rounded down.
Num_digits is the number of
digits to which you want to round number.
STATISTICAL FUNCTIONS
PERCENTILE
Returns the k-th percentile of values in a range. You
can use this function to establish a threshold of acceptance. For example, you
can decide to examine candidates who score above the 90th percentile.
Syntax
PERCENTILE(array,k)
Array
is the array or range of data that defines relative standing.
K is
the percentile value in the range 0..1, inclusive.
PERCENTRANK
Returns the rank of a value in a data set as a percentage of the data set. This
function can be used to evaluate the relative standing of a value within a data
set. For example, you can use PERCENTRANK to evaluate the standing of an
aptitude test score among all scores for the test.
Syntax
PERCENTRANK(array,x,significance)
Array
is the array or range of data with numeric values that defines relative standing.
X is
the value for which you want to know the rank.
Significance
is an optional value that identifies the number of significant digits for the
returned percentage value. If omitted, PERCENTRANK uses three digits (0.xxx).
RANK
Returns the rank of a number in a list of numbers. The rank of a number is its
size relative to other values in a list. (If you were to sort the list, the
rank of the number would be its position.)
Syntax
RANK(number,ref,order)
Number is
the number whose rank you want to find.
Ref is an
array of, or a reference to, a list of numbers. Nonnumeric values in ref are
ignored.
Order is a
number specifying how to rank number.
LOOKUP AND REFERENCE FUNCTIONS
VLOOKUP
Searches for a value in the leftmost column of a table, and then returns a
value in the same row from a column you specify in the table. The V in VLOOKUP stands for
"Vertical."
Syntax
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value is the value to be found in
the first column of the array. Lookup_value can be a
value, a reference, or a text string.
Table_array is the table of information
in which data is looked up. Use a reference to a range or a range name, such as
Database or List.
Col_index_num is the column number
in table_array from which the matching value must be
returned. A col_index_num of 1 returns the value in
the first column in table_array; a col_index_num of 2 returns the value in the second column
in table_array, and so on. If col_index_num
is less than 1, VLOOKUP returns the #VALUE! error
value; if col_index_num is greater than the number of
columns in table_array, VLOOKUP returns the #REF! error value.
Range_lookup is a logical value
that specifies whether you want VLOOKUP to find an exact match or an
approximate match. If TRUE or omitted, an approximate match is returned. In
other words, if an exact match is not found, the next largest value that is
less than lookup_value is returned. If FALSE, VLOOKUP
will find an exact match. If one is not found, the error value #N/A is
returned.
HLOOKUP
Searches for a value in the top row of a table or an array of values, and then
returns a value in the same column from a row you specify in the table or
array. Use HLOOKUP when your comparison values are located in a row across the
top of a table of data, and you want to look down a specified number of
rows. The H in HLOOKUP stands for
"Horizontal."
Syntax
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
Lookup_value is the value to be
found in the first row of the table. Lookup_value can
be a value, a reference, or a text string.
Table_array is a table of
information in which data is looked up. Use a reference to a range or a range
name.
Row_index_num is the row number in table_array from which the matching value will be returned.
A row_index_num of 1 returns the first row value in table_array, a row_index_num of 2
returns the second row value in table_array, and so
on. If row_index_num is less than 1, HLOOKUP returns
the #VALUE! error value; if row_index_num
is greater than the number of rows on table_array,
HLOOKUP returns the #REF! error value.
Range_lookup is a logical value
that specifies whether you want HLOOKUP to find an exact match or an
approximate match. If TRUE or omitted, an approximate match is returned. In
other words, if an exact match is not found, the next largest value that is
less than lookup_value is returned. If FALSE, HLOOKUP
will find an exact match. If one is not found, the error value #N/A is
returned.
ENGINEERING FUNCTION
CONVERT
Converts a number from one measurement system to
another. For example, CONVERT can translate a table of distances in miles to a
table of distances in kilometers.
Syntax
CONVERT(number,from_unit,to_unit)
Number
is the value in from_units to convert.
From_unit is the units for
number.
To_unit is the units for the
result. CONVERT accepts the following text values (in quotation marks) for from_unit and to_unit.
TEXT FUNCTIONS
PROPER
Capitalizes the first letter in a text string and any
other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.
Syntax
PROPER(text)
Text is
text enclosed in quotation marks, a formula that returns text, or a reference
to a cell containing the text you want to partially capitalize.
UPPER
Converts text to uppercase.
Syntax
UPPER(text)
Text is the
text you want converted to uppercase. Text can be a reference or text string.
BCA I FUNCTIONS
Syntax
SUM(number1,number2, ...)
Number1, number2, ... are 1 to 30 arguments for
which you want the total value or sum.
Syntax
AVERAGE(number1,number2,...)
Number1, number2, ... are 1 to 30 numeric arguments
for which you want the average.
Syntax
MAX(number1,number2,...)
Number1, number2, ... are 1 to 30 numbers for which
you want to find the maximum value.
Syntax
MIN(number1,number2,...)
Number1, number2, ... are 1 to 30 numbers for which
you want to find the minimum value.