Formulas in Spread.Sheets include operators and functions that follow certain syntax rules and allow you to perform a range of calculations. These topics introduce the concepts you need to make full use of the builtin functions and extensive capability of formulas:
4.1.2.2 R1C1 Notation
Each cell can be
referenced by its row and column number by preceding each by the letter
"R" for row and the letter "C" for column. For example R1C3
is the cell in the first row and third column.
A1
Cell R1C1 Cell
Description Ref. Ref.
B12 R12C2 Cell in the second column (column B) and
twelfth row (row 12)
R14C4: D14:D48 R48C4 
The range of cells in the fourth column (column D) and in rows 14
through 48 
R16C5: E16:H16 R16C8 
The range of cells in the sixteenth row (row 16) in the fifth through
the eighth column (columns E through H) 
R25C1: A25:E70 R70C5 
The range of cells in the first five columns (column A through E) and rows
25 through 70 
4.1.2.3 Relative and Absolute
A relative cell
reference is a reference to a cell relative to the position of the cell with
the formula. An absolute reference is a cell reference that always refers to a
cell by its exact location in the sheet and not with reference to the present
cell.
Relative references
automatically adjust when you copy them and absolute references do not. The
widget can use absolute or relative cell references. You can define the cell
reference style for each sheet by using the ReferenceStyle property. The
formula does not support a range reference that contains both absolute and
relative row or column references. In other words, the start and end rows in a
range reference have to match (both absolute or both relative). The following
table contains examples of valid relative cell references in formulas.
Function Description
SUM(A1:A10) Sums
rows 1 through 10 in the first column
PI( )*C6 Multiplies
pi times the value in cell C6
Adds the values in the first two cells and
multiplies the result by the value in the
(A1 + B1) * C1
third cell
IF(A1>5,
A1*2,Checks if the contents of cell A1 are greater than 5, and if so,
multiplies the A1*3) contents of cell A1 by 2, or else multiplies the contents
of cell A1 by 3
For A1 notation, use
a dollar sign ($) preceding the row or column (or both) to indicate an absolute
reference. For example
$A$1 absolute
first column, absolute first row
$A1 absolute
first column, relative row plus one
A$1 relative
column plus one, absolute first row
A1 relative
column plus one, relative row plus one
For R1C1 notation, use brackets [ ] around the row or column number (or both) to indicate a relative reference. For example
R1C1 
absolute first row, absolute
first column 
R1C[1] 
absolute first row, relative
column plus one 
R[1]C1 
relative row plus one,
absolute first column 
R[1]C[1] 
relative row plus one,
relative column plus one 
R[1]C[1] 
relative row minus one, relative column minus one 
In this notation,
the number inside the brackets is an offset from the current cell. This number
may be a negative or positive integer or zero. Leaving off the offset entirely
is short hand way of indicating a zero offset. So,
RC2 is equivalent to
R[0]C2
R[3]C is equivalent
to R[3]C[0]
4.1.3 Sheet
References in a Formula
A formula can have
references to cells on the same sheet or to cells on other sheets, as well as
ranges of cells on sheets.
In the examples shown below, we use A1
(LetterNumber) notation for the cell reference, but the same would be valid for
R1C1 (NumberNumber) notation. Simply precede the cell reference, regardless of
the style, with the sheet name as described here.
When a reference to
a cell includes a reference to a cell on another sheet, this is called
crosssheet referencing.
An example of
crosssheet referencing in a formula that uses the addition operator would be:
(FirstRoundData!A2 +
SecondRoundData!A2)
where the name of
one sheet is "FirstRoundData" and the name of another sheet is
"SecondRoundData". Sheet names
precede the cell reference with the name of the sheet followed by an
exclamation point (!). This formula could be on any sheet in the Spread since
it explicitly names the sheets of each of the cells as operands. This example
adds the values in the cell A2 on two different sheets. By making the sheet
name explicit there is no confusion as to which cell A2 is meant. If you do not
include the sheet name, the current sheet (in which the formula exists) is
used. If the formula in the above example was on the SecondRoundData page, then
the formula could be written as:
(FirstRoundData!A2 +
A2)
It might be less
confusing to put the cell on the current page first, as in:
(A2 +
FirstRoundData!A2)
As long as
the sheet name conforms to normal variable name rules (with the first character
being a letter or an underscore and the remaining characters being letters,
digits, or underscores) then the formula can use just the sheet name followed
by the exclamation point. Otherwise, the sheet name needs to be enclosed in
single quotes. If the sheet name itself contains a single quote, then use two
single quotes in the formula. For example, if the name of the sheet includes a
single quote (or apostrophe) as in these names for sales of a given month, then
a reference to the sheet would look like this in a formula:
('November''s
Sales'!A2 + 'December''s Sales'!A2)
with two single
quotes (or apostrophes) before the s. If the sheet name has a space, use single
quotes around the sheet name. In the following example the sheet name is East
Coast Sales.
('East Coast
Sales'!A2 + 'West Coast Sales'!A1)
If you have a
quote in the name of the sheet, you need to add the delimiter that is required
for that language. For instance, in C#, if the sheet name is "Zippy"
Sales, where the quotes are part of the sheet name, a formula that includes a
reference to this sheet might look like this:
('/"Zippy/"
Sales'!A2 + 'West Coast Sales'!A1)
where a single
quotes surrounds the entire sheet name and the backslash (/) delimiter precedes
the quotes. For Visual Basic, you would use two double quote characters as in:
('""Zippy""
Sales'!A2 + 'West Coast Sales'!A1)
For crosssheet
referencing of a range of cells in another page, precede the range with the
sheet name. For example:
SUM(SecondRoundData!A2:A10)
This adds the values in cells A2 to A10 of the
sheet named SecondRoundData. There is no reason to include the sheet name in
the second half of the range reference since the cells are on the same sheet.
You cannot specify two different sheets in a range; a range of cells is only on
a particular sheet, not between sheets.
4.1.4 Operators
in a Formula
The following table lists the available
operators. For each operator, an example is given of the syntax of using a
literal value as well as a cell reference. The type of value returned is given
for each type of operator.
Type of Operator 
Example Syntax 

Result 

Operator Binary Operators 
Description 
Literal & Literal 
Cell Ref & Literal 
Type
Returned 
+ 
Add 
5 + 3 
A1 + 3 
double 
– 
Subtract 
5 – 3 
A1 – 3 
double 
* 
Multiply 
5 * 3 
A1 * 3 
double 
/ 
Divide 
5 / 3 
A1 / 3 
double 
^ 
Exponent 
5 ^ 3 
A1 ^ 3 
double 
& 
Concatenate 
"F" & "p" 
A1 & "p" 
string 
= 
Equal 

A1 <> 3 
boolean 
<> 
Not Equal 

A1 = 3 
boolean 
< 
Less Than 

A1 <3 
boolean 
> 
Greater Than 

A1 > 3 
boolean 
<= 
Less Than Or Equal 

A1 <= 3 
boolean 
>= Unary Operators 
Greater Than Or Equal 

A1 >= 3 
boolean 
 
Negate 
(5/3) 
(A1/3) 
double 
+ 
Plus 
+(5/3) 
+(A1/3) 
double 
% 
Percent 
(5/3)% 
(A1/3)% 
double 
Operators
specify the type of calculation that you want to perform on the elements of a formula.
Most of the operators return doubleprecision floating point values for
mathematical operations and boolean (or logical) values for comparison
operators.
In
Spread.Sheets, all arithmetic operators (including the unary +) check their
arguments and return a #VALUE error if any of the arguments are strings that
cannot be converted to a number. This is mathematically correct behavior and
cannot be overridden. For example, the three formulas +B5 and 0+B5 and B5
should all produce the same result and, in Spread.Sheets, they do.
For more information
about operators, see Order of Precedence and Operators with Dates and Times.
4.1.4.1 Order of Precedence
When there
are several operators in a formula, the formula performs the operations in a
specific order. The formula is parsed from left to right, according to a
specific order for each operator or function in the formula. You can prioritize
the order of operations by using parentheses in the formula.
If you
combine several operators in a single formula, the operations are performed in
the order shown in the following table. Unary operations precede binary
operations. If a formula contains operators with the same precedence, the
operators are evaluated from left to right. To change the order of evaluation, enclose
the part of the formula to be calculated first in parentheses; this has the
highest precedence. Where the order of precedence is the same for two
operators, the formula is evaluated from left to right.
Operator Description
left to right Direction
( ) Parentheses
(for grouping)
– Negate
+ Plus
% Percent
^ Exponent
* and / 
Multiply
and Divide 
+ and  
Add and Subtract 
& 
Concatenate 
=, <, >, <=, >=,
<> 
Compare 
4.1.4.2 Operators with Dates and Times
You can use several
of the operators with dates and times as summarized here:
Operator 
Type of Operation 
Result 
Plus 
+ TimeSpan 
TimeSpan 
Negate 
 TimeSpan 
TimeSpan 
Add 
DateTime + TimeSpan 
DateTime 
Add 
TimeSpan + DateTime 
DateTime 
Add 
TimeSpan + TimeSpan 
TimeSpan 
Subtract 
DateTime  DateTime 
TimeSpan 
Subtract 
DateTime  TimeSpan 
DateTime 
Subtract 
TimeSpan  TimeSpan 
DateTime 
The same order of
precedence applies, including use of parentheses, as described in Order
of Precedence.
If a DateTime or
TimeSpan calculation results in an exception (for example, an
OverflowException), the operator returns the #NUM! error.
4.1.5 Wildcard
Characters in a Formula
You can use the
wildcard character in formulas to search for values. The wildcard character can
be used in formulas that have a criteria argument.
The following
wildcard characters can be used as comparison criteria for functions when
searching.
Character Finds Example
? (question mark) Any single character sm?th finds "smith" and "smyth"
*east finds "Northeast" and
* (asterisk) Any
number of characters
"Southeast"
A question mark,
asterisk, or
~ (tilde) followed by ?, *, or ~ fy91~?
finds "fy91?"
tilde
The following
formulas support the wildcard character:
· AVERAGEIF
· AVERAGEIFS
· COUNTIF
· COUNTIFS
· DAVERAGE
· DGET
· DMAX
· DMIN
· DPRODUCT
· DSTDEV
· DSTDEVP
· DSUM
· DVAR
· DVARP
· HLOOKUP
· MATCH
· SEARCH
· SUMIF
· SUMIFS
· VLOOKUP
The wildcard
character can only be used with an equal string.
Using Code
This example uses
the wildcard character to search for items that contain an "a".
JavaScript Copy
Code
activeSheet.setValue(0, 0, 'abc');
activeSheet.setValue(1, 0, 'ac'); activeSheet.setValue(2, 0, 'a*');
activeSheet.getCell(0,
1).formula('COUNTIF(A1:A3,"a*")'); //start with a activeSheet.getCell(1,
1).formula('COUNTIF(A1:A3,"a?")'); //"a" and only one other
character activeSheet.getCell(2, 1).formula('COUNTIF(A1:A3,"a~*")');
// should be "a*"
4.1.6 Functions
in a Formula
Functions are code
segments that perform calculations by using specific values, called arguments,
in a particular order that can be used in formulas. For example, the SUM
function adds values or ranges of cells and the PMT function calculates the
loan payments based on an interest rate, the length of the loan, and the
principal amount of the loan. Functions may be either builtin functions that
come with Spread or userdefined functions that you create.
Arguments can
be numbers, text, logical values, arrays, cell ranges, cell references, or
error values. The value you use for an argument must be valid for the given
function. Arguments can also be constants, formulas, or other functions. Using
a function as an argument for another function is known as nesting a function.
Some arguments are optional; this reference displays "[Optional]"
before the description of the argument for those arguments that are not
required.
The structure
of a function begins with the function name, followed by an opening
parenthesis, the arguments for the function separated by commas, and a closing
parenthesis. If you are entering the function into a cell directly, type an
equal sign (=) before the function name.
This widget has the
following types of functions:
· Database
Functions
· Date
and Time
· Engineering
Functions
· Financial
Functions
· Information
Functions
· Logical
Functions
· Lookup
Functions
· Math
and Trigonometry
· Statistical
Functions
· Text
Functions
· Volatile
Functions
For more
information, see the following topics:
· Database
Functions
· Date
and Time Functions
· Complex
Numbers
· Day
Count Basis
· Optional
and Missing Arguments
· Volatile
Functions
4.1.6.1 Database Functions
Database functions
apply a mathematical or statistical operation to a subset of values in a range
of cells treated as a database. The database table can be thought of as a
twodimensional array organized into rows and columns. Or it can be thought of
as a onedimensional array of records where each record is a structure that has
one or more fields. In the context of database tables, the terms
"row" and "record" mean the same thing and the terms
"column" and "field" mean the same thing. Database refers
to a range of cells where the first row in the range represents field labels.
The remaining rows in the range represent records. The columns in the range
represent fields.
4.1.6.2 Date and Time Functions
For most of these
functions you can specify the date argument as a DateTime object, as in the
result of a function such as DATE(2003,7,4), or a TimeSpan object, as in the
result of a function such as TIME(12,0,0). For compatibility with Excel, it
also allows dates to be specified as a number (as in 37806.5) or as a string
(as in "7/4/2003 12:00"). The numbers and strings are converted to
instances of the DateTime class.
Dates as numeric
values are in the form x.y, where x is the "number of days since December
30, 1899" and y is the fraction of day. Numbers to the left represent the
date. Times as numeric values are decimal fractions ranging from 0 to
0.99999999, representing the times from 0:00:00 (12:00:00 A.M.) to 23:59:59
(11:59:59 P.M.).
The following three
formulas produce the same result:
YEAR(DATE(2004,8,9))
YEAR(38208)
YEAR("8/9/2004")
In Excel, dates can
range from 01/01/1900 to 12/31/9999, and in the .NET framework, instances of
the DateTime class can range from 01/01/0001 to 12/31/9999. In Spread.Sheets,
we generally support the larger range found in the .NET framework. For Excel
compatibility there are a few cases where the function allows only the smaller
range (for example, the DATE function can only be used to enter dates since
01/01/1900).
Both Excel
and OLE automation use doubles to represent dates and times, with the integer
portion of the double representing the number of days from a base date. In
Excel, the base date that is used is 01/01/1900 and the year 1900 is treated as
a leap year. In OLE automation, Microsoft corrected this by using the base date
of 12/31/1899. As OLE automation does, our spreadsheets treat 1900 as a
nonleap year and thus use the base date of 12/31/1899.
4.1.6.3 Complex Numbers
Many of the
engineering functions involve complex numbers. A complex number consists of two
parts, a real part and an imaginary part. Consider a complex number as being a
point (x,y) in a plane. The real number is similar to a point (x,0) on the
xaxis of the plane. Note that real numbers are a subset of complex numbers
with zero for the coefficient of the imaginary part.
There is not a
complex number data type. Instead, complex numbers are represented using
strings of the form "x+yi" where x and y are real numbers and x is
the real part and yi is the imaginary part. For example:
"2+3i"
"1.23E4+5.67E8i"
Note that if either
the real part or the imaginary part is zero then the zero part can be
optionally omitted from the text representation. For example:
"3" is
equivalent to "3+0i"
"4i" is
equivalent to "0+4i"
Since real numbers
are a subset of complex numbers, a real number can be used in place of a string
of the form "x+yi". For example:
3 is equivalent to
"3+0i"
The functions that return
a complex number return a string of the form "x+yi". For example:
COMPLEX(3,5) returns
"3+5i"
The functions that
accept a complex number can accept either a number or a string of the form
"x +yi". For example:
IMSUM("1+2i",
"3+4i") returns "4+6i"
IMSUM(1, 3) returns
"4"
When a string cannot
be converted to a number Spread returns a #VALUE error. For example:
COS("abc")
returns #VALUE!
IMCOS("abc")
returns #VALUE!
Spread allows either
suffix "j" or the suffix "i" to denote the imaginary part.
For example:
"3+4j" is
equivalent to "3+4i"
Spread allows mixed
suffixes in the a given formula and always returns the "i" suffix.
For example:
IMSUM("1+2i","3+4i")
returns "4+6i"
IMSUM("1+2j","3+4j")
returns "4+6i"
IMSUM("1+2i","3+4j")
returns "4+6i"
Spread does not
allow spaces before the real part or before the imaginary part. For example:
IMABS("3+4i")
returns 5
IMABS("
3+4i") returns #VALUE!
IMABS("3
+4i") returns #VALUE!
IMABS("3+4i
") returns #VALUE!
4.1.6.4 Day Count Basis
For many of the
financial functions, the day count basis is used:
Basis Number Day Count Basis
0
(or omitted) United States
of America (NASD) 30/360
1 Actual/Actual
2 Actual/360
3 Actual/365
4 European 30/360
[NASD is the
National Association of Securities Dealers.]
4.1.6.5 Optional and Missing Arguments
Some
functions have a variable number of arguments with some (typically the last)
arguments being optional. These are displayed in this reference with the word
Optional in brackets "[Optional]" before the argument in the table of
arguments. For example, consider the payment function (PMT) which has five
arguments with the last two being optional. In Spread.Sheets, you can make any
of the following calls:
PMT(rate,nper,pv,fv,type)
PMT(rate,nper,pv,fv)
PMT(rate,nper,pv,fv,)
PMT(rate,nper,pv,,type)
PMT(rate,nper,pv,,)
PMT(rate,nper,pv)
The
optional arguments may be omitted. Any missing optional argument is handled
with the default value being passed. For example FIXED(1234.5678,,FALSE)
evaluates the same
as
FIXED(1234.5678,2,FALSE)
since the default value
for the number of decimal places is 2.
Missing arguments
are intended to be used with functions that have optional arguments. If a
missing argument is passed in for a required argument then the function will
evaluate to the #N/A error.
4.1.6.6 Volatile Functions
Formulas that
contain volatile functions are recalculated every time any other function is
recalculated or a cell value is changed.
4.1.7 Arrays
in a Formula
Formulas may
include functions that operate on arrays. Spread.Sheets supports array constants
in formulas. Use curly brackets { } to enclose the array elements. Use a comma
to separate elements within a row. Use a semicolon to separate rows within the
array. Individual elements can be number values, text values, logical values,
or error values. Some examples of arrays are:
CORREL({5,10,15,20,25},{4,8,16,32,64})
CORREL({73000,45000,40360},{42,70,40})
ROWS({1,2,3;4,5,6})
4.1.8 Resultant
Error Values
The values that can
be displayed in a cell as a result of an invalid entry or invalid formula are
as follows:
Value Description
This displays when a formula includes a
division by zero or when a formula uses, in the #DIV/0!
divisor, a cell
reference to a blank cell or to a cell that contains zero.
#N/A This
displays when a value is not available to a function or formula.
This displays when
text in a formula is not recognized or when the name of a function is
#NAME?
misspelled, or when including text without using double quotation marks. This
can also happen when you omit a colon (:) in a cell range reference.
This displays when
you specify an intersection of two areas that do not intersect. Possible
#NULL!
causes include a
mistyped reference operator or a mistyped cell reference.
This displays when a
number in a formula or function can not be calculated, when a formula produces
a number that is too large or too small to represent, or when using an
#NUM! unacceptable argument in a function that requires a number. If you
are using a function that iterates, such as IRR or RATE, and the function cannot
find a result, this value is displayed.
This displays when a
cell reference is not valid or when you deleted cells referred to by a
#REF!
formula.
This displays when
the wrong type of argument or operand is used, such as using text
#VALUE!
when the formula requires a number or a logical value, or using a range instead
of a single value.
Spreadsheet provides
these builtin functions, listed alphabetically.
ABS 
ACCRINT 
ACCRINTM 
ACOS 
ACOSH 
ADDRESS 
AMORDEGRC 
AMORLINC 
AND 
ASIN 
ASINH 
ATAN 
ATAN2 
ATANH 
AVEDEV 
AVERAGE 
AVERAGEA 
AVERAGEIF 
AVERAGEIFS 
BESSELI 
BESSELJ 
BESSELK 
BESSELY 
BETADIST 
BETA.DIST 
BETAINV 
BETA.INV 
BIN2DEC 
BIN2HEX 
BIN2OCT 
BINOMDIST 
BINOM.DIST 
BINOM.INV 
CEILING 
CEILING.PRECISE 
CHAR 
CHIDIST 
CHIINV 
CHISQ.DIST 
CHISQ.DIST.RT 
CHISQ.INV 
CHISQ.INV.RT 
CHISQ.TEST 
CHITEST 
CHOOSE 
CLEAN 
CODE 
COLUMN 
COLUMNS 
COMBIN 
COMPLEX 
CONCATENATE 
CONFIDENCE 
CONFIDENCE.NORM 
CONFIDENCE.T 
CONVERT 
CORREL 
COS 
COSH 
COUNT 
COUNTA 
COUNTBLANK 
COUNTIF 
COUNTIFS 
COUPDAYBS 
COUPDAYS 
COUPDAYSNC 
COUPNCD 
COUPNUM 
COUPPCD 
COVAR 
COVARIANCE.P 
COVARIANCE.S 
CRITBINOM 
CUMIPMT 
CUMPRINC 


DATE 
DATEDIF 
DATEVALUE 
DAVERAGE 
DAY 
DAYS360 
DB 
DCOUNT 
DCOUNTA 
DDB 
DEC2BIN 
DEC2HEX 
DEC2OCT 
DEGREES 
DELTA 
DEVSQ 
DGET 
DISC 
DMAX 
DMIN 
DOLLAR 
DOLLARDE 
DOLLARFR 
DPRODUCT 
DSTDEV 
DSTDEVP 
DSUM 
DURATION 
DVAR 
DVARP 
EDATE 
EFFECT 
EOMONTH 
ERF 
ERF.PRECISE 
ERFC 
ERFC.PRECISE 
ERROR.TYPE 
EURO 
EUROCONVERT 
EVEN 
EXACT 
EXP 
EXPONDIST 
EXPON.DIST 
FACT 
FACTDOUBLE 
FALSE 
FDIST 
F.DIST 
F.DIST.RT 
FIND 
FINV 
F.INV 
F.INV.RT 
FISHER 
FISHERINV 
FIXED 
FLOOR 
FLOOR.PRECISE 
FORECAST 
FREQUENCY 
FTEST 
F.TEST 
FV 
FVSCHEDULE 
GAMMADIST 
GAMMA.DIST 
GAMMAINV 
GAMMA.INV 
GAMMALN 
GAMMALN.PRECISE 
GCD 
GEOMEAN 
GESTEP 
GROWTH 
HARMEAN 
HEX2BIN 
HEX2DEC 
HEX2OCT 
HLOOKUP 
HOUR 
HYPGEOMDIST 
HYPGEOM.DIST 
IF 
IFERROR 
IMABS 
IMAGINARY 
IMARGUMENT 
IMCONJUGATE 
IMCOS 
IMDIV 
IMEXP 
IMLN 
IMLOG2 
IMLOG10 
IMPOWER 
IMPRODUCT 
IMREAL 
IMSIN 
IMSQRT 
IMSUB 
IMSUM 
INDEX 
INDIRECT 
INT 
INTERCEPT 
INTRATE 
IPMT 
IRR 
ISBLANK 
ISERR 
ISERROR 
ISEVEN 
ISLOGICAL 
ISNA 
ISNONTEXT 
ISNUMBER 
ISO.CEILING 
ISODD 
ISPMT 
ISREF 
ISTEXT 
KURT 
LARGE 
LCM 
LEFT 
LEN 
LINEST 
LN 
LOG 
LOG10 
LOGEST 
LOGINV 
LOGNORMDIST 
LOGNORM.DIST 
LOGNORM.INV 
LOOKUP 
LOWER 


MATCH 
MAX 
MAXA 
MDETERM 
MDURATION 
MEDIAN 
MID 
MIN 
MINA 
MINUTE 
MINVERSE 
MIRR 
MMULT 
MOD 
MODE 
MODE.MULT 
MODE.SNGL 
MONTH 
MROUND 
MULTINOMIAL 
NEGBINOMDIST 
NEGBINOM.DIST 
N 
NA 
NETWORKDAYS 
NETWORKDAYS.INTL 
NOMINAL 

NORMDIST 
NORM.DIST 
NORMINV 

NORM.INV 
NORMSDIST 
NORM.S.DIST 

NORMSINV 
NORM.S.INV 
NOT 

NOW 
NPER 
NPV 

OCT2BIN 
OCT2DEC 
OCT2HEX 

ODD 
ODDFPRICE 
ODDFYIELD 

ODDLPRICE 
ODDLYIELD 
OFFSET 

OR 
OFFSET 


PEARSON 
PERCENTILE 
PERCENTILE.EXC 

PERCENTILE.INC 
PERCENTRANK 
PERCENTRANK.EXC 

PERCENTRANK.INC 
PERMUT 
PI 

PMT 
POISSON 
POISSON.DIST 

POWER 
PPMT 
PRICE 

PRICEDISC 
PRICEMAT 
PROB 

PRODUCT 
PROPER 
PV 

QUARTILE 
QUARTILE.EXC 
QUARTILE.INC 

QUOTIENT 
RADIANS 
RAND 

RANDBETWEEN 
RANK 
RANK.AVG 

RANK.EQ 
RATE 
RECEIVED 

REFRESH 
REPLACE 
REPT 

RIGHT 
ROMAN 
ROUND 

ROUNDDOWN 
ROUNDUP 
ROW 

ROWS 
RSQ 


SEARCH 
SECOND 
SERIESSUM 

SIGN 
SIN 
SINH 

SKEW 
SLN 
SLOPE 

SMALL 
SQRT 
SQRTPI 

STANDARDIZE 
STDEV 
STDEVA 

STDEVP 
STDEV.P 
STDEVPA 

STDEV.S 
STEYX 
SUBSTITUTE 

SUBTOTAL 
SUM 
SUMIF 

SUMIFS 
SUMPRODUCT 
SUMSQ 

SUMX2MY2 
SUMX2PY2 
SUMXMY2 

SYD 
T 
TAN 

TANH 
TBILLEQ 
TBILLPRICE 

TBILLYIELD 
TDIST 
T.DIST 

T.DIST.2T 
T.DIST.RT 
TEXT 

TIME 
TIMEVALUE 
TINV 

T.INV 
T.INV.2T 
TODAY 

TRANSPOSE 
TREND 
TRIM 

TRIMMEAN 
TRUE 
TRUNC 

TTEST 
T.TEST 
TYPE 

UPPER 
VALUE 
VAR 

VARA 
VARP 
VAR.P 

VARPA 
VAR.S 
VDB 

VLOOKUP 
WEEKDAY 
WEEKNUM 

WEIBULL 
WEIBULL.DIST 
WORKDAY 

WORKDAY.INTL 
XIRR 
XNPV 

YEAR 
YEARFRAC 
YIELD 

YIELDDISC 
YIELDMAT 
ZTEST 

Z.TEST 



4.2.1 ABS
SmartSpreadsheet Documentation
ABS
Formula Reference>Formula Functions> ABS
This function
calculates the absolute value of the specified value.
Syntax
ABS(value)
ABS(expression)
Arguments
This function can
take either a value or an expression as an argument.
Remarks
This function turns
negative values into positive values.
Data Types
Accepts numeric
data. Returns numeric data.
Examples
ABS(R3C2)
ABS(B3)
ABS(4) gives the
result 4
ABS(1424) gives the
result 10
ABS(4) gives the
result 4
See Also
SIGN
Copyright © Envision
Ltd. All rights reserved.
Send comments on this topic.
4.2.2 ACCRINT
This function
calculates the accrued interest for a security that pays periodic interest.
Syntax
ACCRINT(issue,first,settle,rate,par,frequency,basis)
Arguments
This function has
these arguments:
Argument Description
issue Date that the security is issued first First date for calculating the interest
for the security settle Settlement date for the security rate Annual
interest rate for the security par [Optional] Par value for the
security; if omitted, the calculation uses a value of $1,000 frequency Frequency of payment, number
of payments per year basis [Optional] Integer representing the basis
for day count (Refer to Day Count Basis.) Remarks
This function
requires that the issue is less than the settlement (otherwise a #NUM! error is
returned). If the rate or par is less than or equal to 0, then a #NUM! error is
returned. If the frequency is a number other than 1, 2, or 4, then a #NUM!
error is returned. If the basis is less than 0 or greater than 4, a #NUM! error
is returned.
Data Types
Accepts numeric and
DateTime object data. Returns numeric data.
Examples
ACCRINT(A1,A2,A3,B4,D9,E9,0)
ACCRINT(DATE(2003,1,1),DATE(2003,1,7),DATE(2005,1,7),0.5,1000,2)
gives the result
1008.33333
See Also
ACCRINTM
INTRATE
4.2.3 ACCRINTM
This function
calculates the accrued interest at maturity for a security that pays periodic
interest.
Syntax
ACCRINTM(issue,maturity,rate,par,basis)
Arguments
This function has
these arguments:
Argument Description
issue Date that the security is issued maturity Maturity date for security rate Annual interest rate for the
security par [Optional] Par value for the security; if omitted, the
calculation uses a value of $1,000 basis [Optional] Integer representing the
basis for day count (Refer to Day Count Basis.)
Remarks
This function
requires that issue is a valid date (otherwise a #Value! error is returned). If
the rate or par is less than or equal to 0, then a #NUM! error is returned. If
the basis is less than 0 or greater than 4, a #NUM! error is returned. If the
issue is less than the settlement, then a #NUM! error is returned.
Data Types
Accepts numeric and
DateTime object data. Returns numeric data.
Examples
ACCRINTM(A2,A3,B4,D9,3)
ACCRINTM(R1C1,R2C3,R4C2,R9C4,3)
See Also
ACCRINT
INTRATE
4.2.4 ACOS
This function
calculates the arccosine, that is, the angle whose cosine is the specified
value.
Syntax
ACOS(value)
Arguments
For the argument,
you can specify the cosine of the angle you want to return, which must be a
value between –1 and 1.
Remarks
The result angle is
in radians between 0 (zero) and PI (pi). If you want to convert the result to
degrees, multiply the result by 180/PI.
Data Types
Accepts numeric
data. Returns numeric data.
Examples
ACOS(B3)
ACOS(R3C2)
ACOS(0.5) gives the
result 1.0471975512
See Also
ACOSH
ASIN
COS
COSH
4.2.5 ACOSH
This function
calculates the inverse hyperbolic cosine of the specified value.
Syntax
ACOSH(value)
Arguments
For the argument,
you can specify any real number greater than or equal to 1.
Remarks
This function is the
inverse of the hyperbolic cosine, so ACOSH(COSH(n)) gives the result n.
Data Types
Accepts numeric
data. Returns numeric data.
Examples
ACOSH(B3)
ACOSH(R3C2)
ACOSH(1) gives the
result 0
ACOSH(10) gives the
result 2.9932228461
ACOS(R3C2)
See Also
ACOS
ASINH
4.2.6 ADDRESS
This function uses
the row and column numbers to create a cell address in text.
Syntax
ADDRESS(row,column,absnum,a1style,sheettext)
Arguments
This function has
these arguments:
Argument Description
row 
Row number in the cell
reference 
column8 
Column number in the cell
reference 
absnum 
[Optional] Type of reference to return; can be any of: 
Value  Type of Cell Reference Returned
1 or omitted  Absolute
2  Absolute row, relative column
3  Relative row, absolute column
4  Relative
[Optional] Logical
value that indicates whether the reference style is A1; if TRUE or
a1style omitted, the style is A1; if FALSE, then the style is R1C1
[Optional] Name of
the sheet to use as an external reference; if omitted, no sheet
sheettext
name is used
Data Types
Accepts numeric and
string data. Returns string data.
Examples
ADDRESS(2,4,2,FALSE)
See Also
COLUMNS
INDEX
ROWS
4.2.7 AMORDEGRC
This function
returns the depreciation for an accounting period, taking into consideration
prorated depreciation, and applies a depreciation coefficient in the
calculation based on the life of the assets.
Syntax
AMORDEGRC(cost,datepurchased,firstperiod,salvage,period,drate,basis)
Arguments
This function has
these arguments:
Argument Description
cost Cost
of the asset
datepurchase
Purchase date of
the asset
d
firstperiod End date of the first period salvage Salvage value at the end of the life of
the asset
period Accounting period drate Rate of depreciation
basis [Optional]
Integer representing the basis for day count (Refer to Day
Count Basis)
Remarks
This function returns the depreciation until the last period of the asset life or until the total value of depreciation is greater than the cost of the assets minus the salvage value. The depreciation coefficients are:
Life of assets 

Depreciation Coefficient 
Between 3 and 4 years 
1.5 

Between 5 and 6 years 
2 

More than 6 years 
2.5 

The depreciation
rate will grow to 50 percent for the period proceeding the last period and will
grow to 100 percent for the last period. If the life of assets is between 0
(zero) and 1, 1 and 2, 2 and 3, or 4 and 5, the #NUM! error value is returned.
This function
differs from AMORLINC, which does not apply a depreciation coefficient in the calculation
depending on the life of the assets.
Data Types
Accepts numeric and
DateTime object data. Returns numeric data.
Examples
AMORDEGRC(B1,B2,B3,B4,B5,B6,B7)
AMORDEGRC(2800,DATE(2003,9,4),DATE(2006,12,31),200,1,0.02,1)
gives the result 117
4.2.8 AMORLINC
This function
calculates the depreciation for an accounting period, taking into account
prorated depreciation.
Syntax
AMORLINC(cost,datepurchased,firstperiod,salvage,period,drate,basis)
Arguments
This function has
these arguments:
Argument Description
cost Cost of the asset
datepurchased
Purchase date of the asset firstperiod End date of the first period
salvage Salvage value at the end of
the life of the asset
period Accounting period drate Rate of depreciation
basis [Optional]
Integer representing the basis for day count (Refer to Day
Count Basis)
Remarks
This function differs
from AMORDEGRC, which
applies a depreciation coefficient in the calculation depending on the life of
the assets.
Data Types
Accepts numeric and
DateTime object data. Returns numeric data.
Examples
AMORLINC(B1,B2,B3,B4,B5,B6,B7)
4.2.9 AND
This function calculates
logical AND.
Syntax
AND(bool1,bool2,...)
AND(array)
AND(array1,array2,...)
AND(expression)
AND(expression1,expression2,...)
Arguments
For the arguments of this function, provide
numeric (0 or 1) or logical values (TRUE or FALSE) for up to 255 arguments. You
can also specify a single array instead of listing the values separately, or up
to 255 arrays. You can also specify the logical argument as an expression.
Remarks
This function
returns TRUE if all its arguments are true; otherwise, returns FALSE if at
least one argument is false.
Data Types
Accepts logical data
(Boolean values of TRUE or FALSE) or numerical values (0 or 1). Returns logical
data (Boolean values of TRUE or FALSE).
Examples
AND(D12,E12)
AND(R12C42,R12C5,R12C1)
AND(D2:D12)
AND(R12C1:R12C9)
AND(true,true,true)
gives the result TRUE
AND(TRUE(),FALSE())
gives the result FALSE
AND(5+3=8,5+1=6)
gives the result TRUE
See Also
NOT
OR
4.2.10 ASIN
This function
calculates the arcsine, that is, the angle whose sine is the specified value.
Syntax
ASIN(value)
Arguments
For the argument,
specify the sine of the angle you want to return, which must be a value between
– 1 and 1.
Remarks
The result angle is
in radians between –PI/2 and PI/2. If you want to convert the result to
degrees, multiply the result by 180/PI.
Data Types
Accepts numeric
data. Returns numeric data.
Examples
ASIN(B3)
ASIN(R3C2)
ASIN(0.5) gives the
result 0.5235987756
See Also
ACOS
SIN
SINH
4.2.11 ASINH
This function
calculates the inverse hyperbolic sine of a number.
Syntax
ASINH(value)
Arguments
For the argument,
you can specify any real number.
Remarks
This function is the inverse of the hyperbolic
sine, so ASINH(SINH(n)) gives the
result n. Data Types
Accepts numeric
data. Returns numeric data.
Examples
ASINH(E4)
ASINH(R4C5)
ASINH(5.5) gives
the result 2.40606
ASINH(100) gives the
result 5.2983423656
See Also
ACOSH
ASIN
SIN
4.2.12 ATAN
This function
calculates the arctangent, that is, the angle whose tangent is the specified
value.
Syntax
ATAN(value)
Arguments
For the argument,
specify the tangent of the angle you want to return, which must be a value
between –1 and 1.
Remarks
The result angle is
in radians between –PI/2 and PI/2. If you want to convert the result to
degrees, multiply the result by 180/PI.
Data Types
Accepts numeric
data. Returns numeric data.
Examples
ATAN(B3)
ATAN(R3C2)
ATAN(1) gives the
result 0.7853981634
See Also
ACOS
ASIN
TAN
4.2.13 ATAN2
This function
calculates the arctangent of the specified x and ycoordinates.
ATAN2(x,y)
Arguments
This function can
take real numbers as arguments.
Remarks
The arctangent is
the angle from the xaxis to a line containing the origin (0, 0) and a point
with coordinates (x, y).
The result is given
in radians between –PI and PI, excluding –PI. If you want to convert the result
to degrees, multiply the result by 180/PI.
Data Types
Accepts numeric
data. Returns numeric data.
Examples
ATAN2(A1,E3)
ATAN2(R1C1,R3C5)
ATAN2(1,1) gives the
result 0.7853981634
See Also
ACOS
ASIN
ATAN
TAN
4.2.14 ATANH
This function
calculates the inverse hyperbolic tangent of a number.
ATANH(value)
Arguments
For the argument,
you can specify any real number between 1 and –1, excluding –1 and 1.
Remarks
This function is the inverse of the hyperbolic
tangent, so ATANH(TANH(n)) gives the
result n. Data Types
Accepts numeric
data. Returns numeric data.
Examples
ATANH(B5)
ATANH(R5C2)
ATANH(0.55) gives
the resultlt 0.6183813136
ATANH(0.2) gives
the result 0.2027325541
See Also
ACOSH
ASINH
ATAN
TAN
4.2.15 AVEDEV
This function
calculates the average of the absolute deviations of the specified values from their
mean.
AVEDEV(value1,value2,...)
AVEDEV(array)
AVEDEV(array1,array2,...)
Arguments
Each argument
can be a doubleprecision floatingpoint value, an integer value, or an array
(cell range) of these. Up to 255 arguments may be included. You can use a single
array (cell range) instead of a list of values. You can use multiple arrays
(cell ranges) as well.
Remarks
This is a measure of
the variability in a data set.
Data Types
Accepts numeric data
for all arguments. Returns numeric data.
Examples
AVEDEV(B5,L32,N25,D17)
AVEDEV(B1:B5)
AVEDEV(B1:B17,L1:L17,N2:N8)
AVEDEV(R5C2,R32C12,R25C15)
AVEDEV(R1C2:R1C7)
AVEDEV(98,79,85)
gives the result 7.1111111111
See Also
AVERAGE
DEVSQ
4.2.16 AVERAGE
This function
calculates the average of the specified numeric values.
AVERAGE(value1,value2,...)
AVERAGE(array)
AVERAGE(array1,array2,...)
Arguments
Each argument
can be a doubleprecision floatingpoint value, an integer value, or an array
(cell range) of these. Up to 255 arguments may be included. You can use a
single array (cell range) instead of a list of values. You can use multiple
arrays (cell ranges) as well.
Remarks
This is a measure of
the variability in a data set.
This function
differs from AVERAGEA, which accepts text or logical values as well as numeric values.
Data Types
Accepts numeric data
for all arguments. Returns numeric data.
Examples
AVERAGE(A1,B3,D5,E9,L8,L9)
AVERAGE(R1C1,R3C2)
AVERAGE(A1:A9)
AVERAGE(A1:A9,B1:B9,D5:D8)
AVERAGE(98,72,85)
gives the result 85
See Also
AVEDEV
AVERAGEA
CONFIDENCE
DEVSQ
MEDIAN
VAR
4.2.17 AVERAGEA
This function
calculates the average of the specified values, including text or logical
values as well as numeric values.
Syntax
AVERAGEA(value1,value2,...)
AVERAGEA(array)
AVERAGEA(array1,array2,...)
Arguments
Each argument
can be a doubleprecision floatingpoint value, an integer value, or an array
(cell range). Up to 255 arguments may be included. You can use a single array
(cell range) instead of a list of values. You can use multiple arrays (cell
ranges) as well.
Remarks
This is a measure of
the variability in a data set.
This function
differs from AVERAGE
because it allows text or logical values as well as numeric values.
Data Types
Accepts numeric,
logical, or text data for all arguments. Returns numeric data.
Examples
AVERAGEA(A1,B3,D5,E9,L8,L9)
AVERAGEA(R1C1,R3C2)
AVERAGEA(A1:A9)
AVERAGEA(A1:A9,B1:B9,D5:D8)
AVERAGEA(98,72,85)
gives the result 85
AVEDEV
DEVSQ
MEDIAN
VAR
4.2.18 AVERAGEIF
This function
calculates the average of the specified numeric values provided that they meet
the specified criteria.
Syntax
AVERAGEIF(value1,value2,...,condition)
AVERAGEIF(array,condition)
AVERAGEIF(array1,array2,...,condition)
Arguments
Each argument
can be a doubleprecision floatingpoint value, an integer value, or an array
(cell range). Up to 255 arguments may be included. You can use a single array
(cell range) instead of a list of values. You can use multiple arrays (cell
ranges) as well.
Remarks
This is a measure of
the variability in a data set.
Data Types
Accepts numeric
data. The condition accepts text, numeric, or expression data. Returns numeric
data.
Examples
AVERAGEIF(A1,B3,D5,E9,L8,L9,"<5000")
AVERAGEIF(R1C1,R3C2,"<>0")
AVERAGE
AVEDEV
DEVSQ
MEDIAN
VAR
4.2.19 AVERAGEIFS
This function calculates
the average of all cells that meet multiple specified criteria.
Syntax
AVERAGEIFS(value1,condition1,value2,...,condition2...)
AVERAGEIFS(array,condition)
AVERAGEIFS(array1,array2,...,condition)
Arguments
Each argument
can be a doubleprecision floatingpoint value, an integer value, or an array
(cell range). Up to 255 arguments may be included. You can use a single array
(cell range) instead of a list of values. You can use multiple arrays (cell
ranges) as well. You can have up to 127 arguments for the conditions.
Remarks
This is a measure of
the variability in a data set.
Data Types
Accepts numeric
data. The condition accepts text, numeric, or expression data. Returns numeric
data.
Examples
AVERAGEIFS(B2:B5,B2:B5,">90",B2:B5,"<100")
AVERAGEIFS(R1C1,R3C2,"<>0")
AVEDEV
AVERAGE
DEVSQ
MEDIAN
VAR
4.2.20 BESSELI
This function
calculates the modified Bessel function of the first kind evaluated for purely
imaginary arguments.
Syntax
BESSELI(value,order)
Arguments
This function has
these arguments:
Argument Description
value Value at which to evaluate the function order Number representing the order of
the function; if it is not an integer, it is truncated
Remarks
If value or order is
nonnumeric then a #Value! error is returned. If order is less than 0 then the
#NUM! error is returned.
Data Types
Accepts numeric data
for both arguments. Returns numeric data.
Examples
BESSELI(A4,D5)
BESSELI(R4C1,R5C4)
BESSELI(1.8,2) gives
the result 0.5260402117
See Also
BESSELJ
BESSELY
4.2.21 BESSELJ
This function
calculates the Bessel function of the first kind.
Syntax
BESSELJ(value,order)
Arguments
This function has
these arguments:
Argument Description
value Value at which to evaluate the function order Number representing the order of
the function; if it is not an integer, it is truncated
Remarks
If value or order is
nonnumeric then a #Value! error is returned. If order is less than 0 then the
#NUM! error is returned.
Data Types
Accepts numeric data
for both arguments. Returns numeric data.
Examples
BESSELJ(A4,D5)
BESSELJ(R4C1,R5C4)
BESSELJ(1.85,2)
gives the result 0.31812827879
See Also
BESSELI
BESSELK
4.2.22 BESSELK
This function
calculates the modified Bessel function of the second kind evaluated for purely
imaginary arguments.
Syntax
BESSELK(value,order)
Arguments
This function has
these arguments:
Argument Description
value Value at which to evaluate the function order Number representing the order of
the function; if it is not an integer, it is truncated
Remarks
This function is
also called the Neumann function. If value or order is nonnumeric then a
#Value! error is returned. If order is less than 0 then the #NUM! error is
returned.
Data Types
Accepts numeric data
for both arguments. Returns numeric data.
Examples
BESSELK(A4,D5)
BESSELK(R4C1,R5C4)
BESSELK(1.85,2)
gives the result 0.32165379
See Also
BESSELJ
BESSELY
4.2.23 BESSELY
This function
calculates the Bessel function of the second kind.
Syntax
BESSELY(value,order)
Arguments
This function has
these arguments:
Argument Description
value Value at which to evaluate the function order Number representing the order of
the function; if it is not an integer, it is truncated
Remarks
If value or order is
nonnumeric then a #Value! error is returned. If order is less than 0 then the
#NUM! error is returned.
Data Types
Accepts numeric data
for both arguments. Returns numeric data.
Examples
BESSELY(A4,D5)
BESSELY(R4C1,R5C4)
BESSELY(2.85,1)
gives the result 0.2801918953
See Also
BESSELJ
BESSELK
4.2.24 BETADIST
This function
calculates the cumulative beta distribution function.
Syntax
BETADIST(x,alpha,beta,lower,upper)
Arguments
This function has
these arguments:
Argument Description
x Value at which to evaluate the function,
between the values of lower and upper alpha
Alpha parameter of the distribution beta
Beta parameter of the distribution lower
[Optional] Lower bound of the interval for x; 0 if omitted upper [Optional] Upper bound of the
interval for x; 1 if omitted
Remarks
If you omit values
for upper and lower, the calculation
uses the standard cumulative beta distribution, so that lower is zero and upper
is one.
Data Types
Accepts numeric data
for all arguments. Returns numeric data.
Examples
BETADIST(3,B3,C3,2,4)
BETADIST(3,R3C2,R3C3,2,4)
BETADIST(3,6,9,2,4)
gives the result 0.7880249023
See Also
BETAINV
4.2.25 BETA.DIST
This function
calculates the cumulative beta distribution function.
Syntax
BETA.DIST(x,alpha,beta,cumulative,lower,upper)
Arguments
This function has
these arguments:
Argument Description
x 
Value at which to evaluate
the function, between the values of lower and upper 
alpha 
Alpha parameter of the
distribution 
beta 
Beta parameter of the
distribution 
cumulative 
A logical value that
determines the function form. The function returns the cumulative
distribution function if this argument is true. It returns the probability
density function, if the argument is false 
lower 
[Optional] Lower bound of
the interval for x; 0 if omitted 
upper Remarks 
[Optional] Upper bound of
the interval for x; 1 if omitted 
If you omit values
for upper and lower, the calculation
uses the standard cumulative beta distribution, so that lower is zero and upper
is one.
Data Types
Accepts numeric data
for all arguments except cumulative.
Accepts TRUE or FALSE for cumulative.
Returns numeric data.
Examples
BETA.DIST(A1,A3,B4,TRUE,1,3)
BETA.DIST(2,8,10,TRUE,1,3)
gives the result 0.6854705810546875
4.2.26 BETAINV
This function
calculates the inverse of the cumulative beta distribution function.
Syntax
BETAINV(prob,alpha,beta,lower,upper)
Arguments
This function has
these arguments:
Argument Description
prob Probability of the distribution alpha Alpha parameter of the
distribution beta Beta parameter of
the distribution lower [Optional]
Lower bound of the interval for x; 0 if omitted upper [Optional] Upper bound of the interval for x; 1 if omitted
Remarks
If you omit values
for upper and lower, the calculation uses the standard cumulative beta
distribution, so that lower is zero
and upper is one.
Data Types
Accepts numeric data
for all arguments. Returns numeric data.
Examples
BETAINV(0.75,B3,C3,2,4)
BETAINV(0.75,R3C2,R3C3,2,4)
BETAINV(0.75,9,12,2,4)
gives the result 3.0011968805
See Also
BETADIST
4.2.27 BETA.INV
This function
calculates the inverse of the cumulative beta density function.
Syntax
BETA.INV(prob,alpha,beta,lower,upper)
Arguments
This function has
these arguments:
Argument Description
prob Probability of the distribution alpha Alpha parameter of the
distribution beta Beta parameter of
the distribution lower [Optional]
Lower bound of the interval for x; 0 if omitted upper [Optional] Upper bound of the interval for x; 1 if omitted
Remarks
If you omit values
for upper and lower, the calculation uses the standard cumulative beta
distribution, so that lower is zero
and upper is one. This function
returns the #VALUE! error value if any argument is nonnumeric.
Data Types
Accepts numeric data
for all arguments. Returns numeric data.
Examples
BETA.INV(0.75,B3,C3,2,4)
BETA.INV(0.75,R3C2,R3C3,2,4)
BETA.INV(0.75,9,12,2,4)
gives the result 3.0011968805340232
4.2.28 BIN2DEC
This function
converts a binary number to a decimal number.
Syntax
BIN2DEC(number)
Arguments
For the argument of
this function, specify the binary numeric value to convert.
Remarks
An error value is
returned if the number contains more than 10 digits or is invalid.
Data Types
Accepts numeric
data. Returns numeric data.
Examples
BIN2DEC(1111111)
See Also
BIN2HEX
BIN2OCT
DEC2BIN
OCT2DEC
4.2.29 BIN2HEX
This function
converts a binary number to a hexadecimal number.
Syntax
BIN2HEX(number,places)
Arguments
This function has
these arguments:
Argument Description
number Binary
numeric value to convert
[Optional] Number
of characters to return; if not an integer, the number is
places
truncated
Remarks
An error value is
returned if the number contains more
than 10 digits or is invalid, or if the value of places is nonnumeric or negative. If places is omitted, the calculation uses the minimum number of
characters necessary. This argument is useful for adding leading zeros to the
result.
Data Types
Accepts numeric
data. Returns numeric data in hexadecimal format.
Examples
BIN2HEX(1110)
See Also
BIN2DEC
BIN2OCT
DEC2HEX
OCT2HEX
4.2.30 BIN2OCT
This function
converts a binary number to an octal number.
Syntax
BIN2OCT(number,places)
Arguments
This function has
these arguments:
Argument Description
number
Binary numeric value to
convert places [Optional] Number of
characters to return; if not an integer, the number is truncated
Remarks
An error value is
returned if the number contains more
than 10 digits or is invalid, or if the value of places is nonnumeric or negative. If places is omitted, the calculation uses the minimum number of
characters necessary. This argument is useful for adding leading zeros to the
result.
Data Types
Accepts numeric
data. Returns numeric data.
Examples
BIN2OCT(1001,2)
See Also
BIN2DEC
BIN2HEX
DEC2OCT
OCT2BIN
4.2.31 BINOMDIST
This function
calculates the individual term binomial distribution probability.
Syntax
BINOMDIST(x,n,p,cumulative)
Arguments
This function has
these arguments:
Argument Description
Number representing the number of successes in
trials; if not an integer, the number
x is truncated
Number representing the number of independent
trials; if not an integer, the number
n
is truncated
p Probability of success on each trial; number
between 0 and 1
Logical value that determines the form of the function; if TRUE, then
this function returns the cumulative distribution function, which is the
probability that there are at
cumulative most x successes; if FALSE, it returns the
probability mass function, which is the probability that there are x successes
Remarks
Use this
function in problems with a fixed number of tests or trials, when there are two
mutually exclusive possible outcomes (a "success" and a
"failure"), when trials are independent, and when the probability of
one outcome is constant throughout the experiment. This function can, for
example, calculate the probability that two of the next three babies born are
male.
The binomial
probability mass function is calculated as follows:
where x is the number of successes, n is the number of trials, and p is the probability of success on any one trial. The cumulative binomial distribution is calculated as follows:
where n is the number of trials, x is the number of successes, and p is the possibility of success on any
one trial.
Data Types
Accepts numeric data
for all arguments, except cumulative, which accepts logical data. Returns
numeric data.
Example
A baby can be either
male or female; for the sake of this example, assume the odds are 50/50 that a
baby is either male or female. If female equals TRUE, we can use the following
to determine the probability of the next 5 babies in 10 born being female. The
probability of the first baby being female is 0.5, and the probability of
exactly 5 of 10 babies born being female is:
BINOMDIST(5,10,0.5,FALSE)
gives the result 0.2460937500
See Also
BETADIST
CRITBINOM
EXPONDIST
GAMMADIST
NEGBINOMDIST
WEIBULL
4.2.32 BINOM.DIST
This function
calculates the individual term binomial distribution probability.
Syntax
BINOM.DIST(x,n,p,cumulative)
Arguments
This function has
these arguments:
Argument Description
Number representing the number of successes in
trials; if not an integer, the number is
x truncated
Number representing the number of independent
trials; if not an integer, the number is
n truncated
p Probability of success on each trial; number
between 0 and 1
Logical value that
determines the form of the function; if TRUE, then this function
cumulative
returns the cumulative distribution function,
which is the probability that there are at
most x successes;
if FALSE, it returns the probability mass function, which is the probability
that there are x successes
Remarks
Use this
function in problems with a fixed number of tests or trials, when there are two
mutually exclusive possible outcomes (a "success" and a
"failure"), when trials are independent, and when the probability of
one outcome is constant throughout the experiment. This function can, for
example, calculate the probability that two of the next three babies born are
male.
The binomial
probability mass function is calculated as follows:
where x is the number of successes, n is the number of trials, and p is the probability of success on any one trial. The cumulative binomial distribution is calculated as follows:
where n is the number of trials, x is the number of successes, and p is the possibility of success on any
one trial.
Data Types
Accepts numeric data
for all arguments, except cumulative, which accepts logical data. Returns
numeric data.
Example
A baby can be
either male or female; for this example, assume the odds are 50/50 that a baby
is either male or female. If female equals TRUE, we can use the following to
determine the probability of the next 5 babies in 10 born being female. The
probability of the first baby being female is 0.5, and the probability of
exactly 5 of 10 babies born being female is:
BINOM.DIST(5,10,0.5,FALSE)
gives the result 0.24609375
4.2.33 BINOM.INV
This function
returns the criterion binomial, the smallest value for which the cumulative
binomial distribution is greater than or equal to a criterion value.
Syntax
BINOM.INV(n,p,alpha)
Arguments
This function has
these arguments:
Argument Description
n Number of trials; if not an integer, the number
is truncated p Probability of success
on each trial; number between 0 and 1 alpha
Alpha, value for the criterion
Data Types
Accepts numeric data
for all arguments. Returns numeric data.
Examples
BINOM.INV(B5,0.75,0.92)
BINOM.INV(R5C2,R8C14,0.75)
BINOM.INV(14,0.75,0.85)
gives the result 14
4.2.34 CEILING
This function rounds
a number up to the nearest multiple of a specified value.
Syntax
CEILING(value,signif)
Arguments
This function has
these arguments:
Argument Description
value Number to round
signif Number
representing the rounding factor
Use either both
positive or both negative numbers for the arguments. Regardless of the sign of
the numbers, the value is rounded away from zero.
Data Types
Accepts numeric data
for both arguments. Returns numeric data.
Examples
CEILING(C4,B2)
CEILING(B3,0.05)
CEILING(R4C3,1)
CEILING(4.65,2)
gives the result 6
CEILING(2.78,1)
gives the result 3
See Also
EVEN
FLOOR
ODD
TRUNC
4.2.35 CEILING.PRECISE
This function rounds
a number up to the nearest multiple of a specified value or the nearest
integer.
Syntax
CEILING.PRECISE(value,signif)
Arguments
This function has
these arguments:
Argument Description
value Number
to round
signif Number
representing the rounding factor
Use either both
positive or both negative numbers for the arguments. Regardless of the sign of
the numbers, the value is rounded away from zero.
Data Types
Accepts numeric data
for both arguments. Returns numeric data.
Examples
CEILING.PRECISE(C4,B2)
CEILING.PRECISE(B3,0.05)
CEILING.PRECISE(R4C3,1)
CEILING.PRECISE(4.65,2)
gives the result 6
CEILING.PRECISE(2.78,1)
gives the result 3
4.2.36 CHAR
This function
returns the character specified by a number.
Syntax
CHAR(value)
Arguments
For the argument,
use a number between 1 and 255 specifying which character you want from the
Windows character set (ANSI).
Data Types
Accepts numeric
data. Returns string data.
Examples
CHAR(B2)
CHAR(R2C2)
CHAR(66) gives the
result B
CHAR(218) gives the
result Ú
See Also
CODE
CONCATENATE
LOWER
PROPER
UPPER
4.2.37 CHIDIST
This function
calculates the onetailed probability of the chisquared distribution.
Syntax
CHIDIST(value,deg)
Arguments
This function has
these arguments:
Argument Description
value Value at which to evaluate the function deg Number of degrees of freedom; if not
an integer, the number is truncated Data
Types
Accepts numeric data
for both arguments. Returns numeric data.
Examples
CHIDIST(B5,D7)
CHIDIST(R5C2,R7C4)
CHIDIST(6.7,4) gives
the result 0.1526169403
See Also
CHIINV
CHITEST
4.2.38 CHIINV
This function
calculates the inverse of the onetailed probability of the chisquared
distribution.
Syntax
CHIINV(prob,deg)
Arguments
This function has
these arguments:
Argument Description
prob Probability of the chisquared distribution deg Number of degrees of freedom; if not
an integer, the number is truncated Data
Types
Accepts numeric data
for both arguments. Returns numeric data.
Examples
CHIINV(B5,D7)
CHIINV(R5C2,R7C4)
CHIINV(0.75,4) gives
the result 1.9225575262
See Also
CHIDIST
CHITEST
4.2.39 CHISQ.DIST
This function
calculates the chisquared distribution.
Syntax
CHISQ.DIST(value,deg,cumulative)
Arguments
This function has
these arguments:
Argu
ment
value Value at which to evaluate the function deg Number of degrees of freedom; if not
an integer, the number is truncated
A logical value that determines the form of the
function. If cumulative is TRUE, this function
cumul
returns the
cumulative distribution function; if FALSE, it returns the probability density
ative function
Data Types
Accepts numeric data
for value and deg arguments. Returns numeric data.
Examples
CHISQ.DIST(B5,D7,TRUE)
CHISQ.DIST(R5C2,R7C4,TRUE)
CHISQ.DIST(6.7,4,TRUE)
gives the result 0.8473830596613241
4.2.40 CHISQ.DIST.RT
This function
calculates the righttailed probability of the chisquared distribution.
Syntax
CHISQ.DIST.RT(value,deg)
Arguments
This function has
these arguments:
Argument Description
value Value at which to evaluate the function deg Number of degrees of freedom; if not
an integer, the number is truncated Remarks
The #VALUE! error
value is returned if either argument is nonnumeric.
Data Types
Accepts numeric data
for both arguments. Returns numeric data.
Examples
CHISQ.DIST.RT(B5,D7)
CHISQ.DIST.RT(R5C2,R7C4)
CHISQ.DIST.RT(6.7,4)
gives the result 0.15261694033867584
4.2.41 CHISQ.INV
This function
calculates the inverse of the lefttailed probability of the chisquared
distribution.
Syntax
CHISQ.INV(prob,deg)
Arguments
This function has these
arguments:
Argument Description
prob Probability of the chisquared distribution deg Number of degrees of freedom; if not
an integer, the number is truncated
Remarks
If the argument is
nonnumeric, the function returns the #VALUE! error value.
Data Types
Accepts numeric data
for both arguments. Returns numeric data.
Examples
CHISQ.INV(B5,D7)
CHISQ.INV(R5C2,R7C4)
CHISQ.INV(0.75,4)
gives the result 5.385269057779394
4.2.42 CHISQ.INV.RT
This function
calculates the inverse of the righttailed probability of the chisquared
distribution.
Syntax
CHISQ.INV.RT(prob,deg)
Arguments
This function has
these arguments:
Argument Description
prob Probability of the chisquared distribution deg Number of degrees of freedom; if not
an integer, the number is truncated
Remarks
The #VALUE! error
value is returned if either argument is nonnumeric.
Data Types
Accepts numeric data
for both arguments. Returns numeric data.
Examples
CHISQ.INV.RT(B5,D7)
CHISQ.INV.RT(R5C2,R7C4)
CHISQ.INV.RT(0.75,4)
gives the result 1.9225575262293264
4.2.43 CHISQ.TEST
This function
calculates the test for independence from the chisquared distribution.
Syntax
CHISQ.TEST(obs_array,exp_array)
Arguments
This function has
these arguments:
Argument Description
obs_array Array of observed values to test against expected values exp_array Array
of expected values against which to test observed values
Remarks
The arrays in the
arguments must be of the same size.
Data Types
Accepts arrays of
numeric data for both arguments. Returns numeric data.
Examples
CHISQ.TEST(B1:C8,B12:C19)
CHISQ.TEST(R1C2:R8C3,R12C2:R19C3)
4.2.44 CHITEST
This function
calculates the test for independence from the chisquared distribution.
Syntax
CHITEST(obs_array,exp_array)
Arguments
This function has these
arguments:
Argument Description
obs_array Array of observed values to test against expected values exp_array Array
of expected values against which to test observed values
The arrays in the
arguments must be of the same size.
Data Types
Accepts arrays of
numeric data for both arguments. Returns numeric data.
Examples
CHITEST(B1:C8,B12:C19)
CHITEST(R1C2:R8C3,R12C2:R19C3)
See Also
CHIDIST
CHIINV
4.2.45 CHOOSE
This function
returns a value from a list of values.
Syntax
CHOOSE(index,value1,value2,...)
Arguments
This function has
these arguments:
Argument Description
index Index of the
specified values to return; an integer value between 1 and 255
Values from which
to choose; can have up to 255 values; can be numbers, cell
value1, etc. references, cell ranges, defined names,
formulas, functions, or text
The value arguments
can be range references as well as single values. For example, the formula:
SUM(CHOOSE(2,A1:A25,B1:B10,C1:C5))
evaluates to:
SUM(B1:B10)
which then returns a
value based on the values in the range B1:B10.
Remarks
This function is evaluated first, returning the reference B1:B10. The SUM function is then evaluated using B1:B10. Data Types
The index argument
accepts numeric data. The value arguments accept any data. Returns the type of
data of the specified value.
Examples
CHOOSE(3,A1,B1,C1,D1,E1)
gives the result C1
CHOOSE(3,R1C1,R1C2,R1C3,R1C4,R1C5)
gives the result R1C3
CHOOSE(2,"dogs","birds","fish","cats","mice")
gives the result birds
See Also
INDEX
SUM
4.2.46 CLEAN
This function removes
all nonprintable characters from text.
Syntax
CLEAN(text)
Arguments
The text argument is
any data from which you want to remove nonprintable characters.
Remarks
Use this function to remove text that contains
characters that might not print with your operating system. For example, you
can use this function to remove some lowlevel computer code, which is
frequently at the beginning and end of data files and cannot be printed
Data Types
Accepts string data.
Returns string data.
Example
In this example,
CHR(7) returns a nonprintable character
CLEAN(CHAR(7)&"text"&CHAR(7))
gives the result text
See Also
SUBSTITUTE
TRIM
4.2.47 CODE
This function
returns a numeric code to represent the first character in a text string. The
returned code corresponds to the Windows character set (ANSI).
Syntax
CODE(text)
Arguments
The argument is the
text from which you want to determine the code of the first character.
Data Types
Accepts string data.
Returns string data.
Examples
CODE(H6)
CODE(R6C8)
CODE(""B"")
gives the result 66
CODE(""Buffalo"")
gives the result 66
See Also
CHAR
4.2.48 COLUMN
This function
returns the column number of a reference.
Syntax
COLUMN(reference)
Arguments
The argument is a
cell or a single area.
Remarks
If the reference is
omitted, the reference of the cell that the function is in is used.
Data Types
Accepts cell
references. Returns numeric data.
Examples
COLUMN(A9) gives the
result 1
COLUMN(A1:A5) gives
the result 1
See Also
INDEX
ROWS
4.2.49 COLUMNS
This function
returns the number of columns in an array.
Syntax
COLUMNS(array)
Arguments
The argument is an
array, an array formula, or a range of cells.
Data Types
Accepts cell
references or array. Returns numeric data.
Examples
COLUMNS(B6:D12)
gives the result 3
COLUMNS(R6C2:R12C4)
gives the result 3
COLUMNS($B$8:$H$8)
gives the result 7
COLUMNS(R[2]C[1]:R[3]C[8])
gives the result 8
See Also
INDEX
ROWS
4.2.50 COMBIN
This function
calculates the number of possible combinations for a specified number of items.
Syntax
COMBIN(k,n)
Arguments
This function has
these arguments:
Argument Description
Number representing the number of items; if not
an integer, the number is truncated;
k must be positive and greater than or equal to n
Number of items in
each possible permutation; if not an integer, the number is
n
truncated; must be
positive
Remarks
A combination is any
set or subset of items, regardless of the internal order of the items. Contrast
with permutation (the PERMUT function).
The number of
combinations is calculated as follows:
Data Types
Accepts numeric data
for both arguments. Returns numeric data.
Examples
COMBIN(C4,B2)
COMBIN(B3,5)
COMBIN(R1C2,2)
COMBIN(8,2) gives
the result 28
COMBIN(100,3) gives
the result 161700
4.2.51 COMPLEX
This function
converts real and imaginary coefficients into a complex number.
Syntax
COMPLEX(realcoeff,imagcoeff,suffix)
Arguments
This function has
these arguments:
Argument Description
realcoeff
Coefficient of the real part
of the complex number imagcoeff Coefficient of the imaginary part of the
complex number
(Optional) Suffix of the imaginary part of the
complex number, may be either "i "or "j".
suffix
If omitted,
"i" is used.
Remarks
For the suffix, use
lowercase for "i" and "j" to prevent errors.
An error is returned
if the real or imaginary coefficients are nonnumeric.
Data Types
Accepts number and
string data. Returns string data.
Examples
COMPLEX(3,5)
COMPLEX(3,5,"j")
See Also
IMAGINARY
IMREAL
4.2.52 CONCATENATE
This function
combines multiple text strings or numbers into one text string.
Syntax
CONCATENATE(text1,text2,...)
Arguments
The arguments can be
strings, formulas that return a string, or references to cells containing a
string. Up to 255 arguments may be included.
Data Types
Accepts string data
for both arguments. Returns string data.
Examples
CONCATENATE(B4,D5)
CONCATENATE(R4C2,R5C4)
CONCATENATE(""Gold
"", ""Medal"") gives the result Gold Medal
See Also
CHAR
EXACT
4.2.53 CONFIDENCE
This function
returns confidence interval for a population mean.
Syntax
CONFIDENCE(alpha,stdev,size)
Arguments
This function has
these arguments:
Argument Description
Alpha, significance level used in calculating
confidence level, where confidence
alpha
level is 100 times
(1alpha)%
stdev Population
standard deviation for the range
Number representing the size of the sample; if
not an integer, the number is
size
truncated
Data Types
Accepts numeric data
for all arguments. Returns numeric data.
Examples
CONFIDENCE(0.5,B4,D5)
CONFIDENCE(0.5,R4C2,R5C4)
CONFIDENCE(0.05,3.5,150)
gives the result 0.560106363
See Also
AVERAGE
CHITEST
4.2.54 CONFIDENCE.NORM
This function
returns confidence interval for a population mean.
Syntax
CONFIDENCE.NORM(alpha,stdev,size)
Arguments
This function has
these arguments:
Argument Description
Alpha, significance level used in calculating
confidence level, where confidence level is
alpha
100 times (1alpha)%
stdev Population standard deviation for the range size Number
representing the size of the sample; if not an integer, the number is truncated
Remarks
The #VALUE! error
value is returned if any argument is nonnumeric. The #NUM! error value is
returned if size < 1.
Data Types
Accepts numeric data
for all arguments. Returns numeric data.
Examples
CONFIDENCE.NORM(0.5,B4,D5)
CONFIDENCE.NORM(0.5,R4C2,R5C4)
CONFIDENCE.NORM(0.05,3.5,150)
gives the result 0.5601063629983405
4.2.55 CONFIDENCE.T
This function
returns the confidence interval for a population mean.
Syntax
CONFIDENCE.T(alpha,stdev,size)
Arguments
This function has
these arguments:
Argument Description
Alpha, significance level used in calculating
confidence level, where confidence level is
alpha
100 times (1alpha)%
stdev Population standard deviation for the range size Number
representing the size of the sample; if not an integer, the number is truncated
Remarks
The function uses a
Student's t distribution. If size =
1, the function returns a #DIV/0! error value. If any argument is nonnumeric,
the function returns the #VALUE! error value.
Data Types
Accepts numeric data
for all arguments. Returns numeric data.
Examples
CONFIDENCE.T(0.5,B4,D5)
CONFIDENCE.T(0.5,R4C2,R5C4)
CONFIDENCE.T(0.05,3.5,150)
gives the result 0.5646928012079743
4.2.56 CONVERT
This function
converts a number from one measurement system to its equivalent in another
measurement system.
Syntax
CONVERT(number,fromunit,tounit)
Arguments
This function has
these arguments:
Argument Description
number Numeric value to convert
fromunit Convertible units (see table below) of numeric value to convert tounit Convertible
units (see table below) of desired result
Remarks
In this context a
measurement system is a set of units for different types of measurements. This
function converts a number with one set of units to a number in different set
of units.
An error value is
returned if the convertible units (fromunit
and tounit) are invalid or are from
different categories of unit types (different tables below).
The following tables
list the convertible units by their unit type:
Weight
and Mass Unit Type Convertible
Units
Gram "g"
Slug "sg"
Pound Mass "lbm"
U "u"
Ounce
Mass "ozm"
Distance
Unit Type Convertible
Units
Meter "m"
Statute mile "mi"
Nautical mile "Nmi"
Inch "in"
Foot "ft"
Yard "yd"
Angstrom "ang"
Pica
(1/72 in.) "Pica"
Time
Unit Type Convertible
Units
Year "yr"
Day "day"
Hour "hr"
Minute "mn"
Second "sec"
Pressure
Unit Type Convertible
Units
Pascal "Pa"
Atmosphere "atm" mm of Mercury "mmHg"
Force Unit
Type Newton "N" Dyne "dyn" Pound force "lbf" 
Convertible Units 

Energy Unit Type 

Convertible Units 

Joule 

"J" 

Erg 

"e" 

Thermodynamic calorie 

"c" 

IT calorie 

"cal" 

Electron volt 

"eV" 

Horsepowerhour 

"Hph" 

Watthour 

"Wh" 

Footpound 

"flb" 

BTU 

"BTU" 

Power Unit Type 

Convertible Units 

Horsepower 
"HP" 


Watt 
"W" 


Magnetism Unit Type 
Convertible Units 

Tesla 
"T" 
Gauss "ga"
Temperature Unit Type Convertible Units
Degree Celsius "C"
Degree Fahrenheit "F"
Degree Kelvin "K"
Liquid
Measure Unit Type Convertible
Units
Teaspoon "tsp"
Tablespoon "tbs"
Fluid
ounce "oz"
Cup "cup"
U.S.
pint "pt"
U.K.
pint "uk_pt"
Quart "qt"
Gallon "gal"
Liter "l"
Data Types
Accepts numeric and
string data. Returns numeric data.
Examples
CONVERT(68,"F","C")
See Also
DEC2OCT
HEX2OCT
OCT2BIN
4.2.57 CORREL
This function
returns the correlation coefficient of the two sets of data.
Syntax
CORREL(array1,array2)
Arguments
The two arrays of
data in the arguments of this function should meet these criteria:
· The
data should contain numbers, names, ranges, or references that are numeric. If
some cells do not contain numeric data, they are ignored.
· The
arrays should be the same size, with the same number of data points.
· The arrays should not be empty, nor should the
standard deviation of their values equal zero.
Data Types
Accepts arrays of numeric
data for both arguments. Returns numeric data.
Examples
CORREL(C1:C10,D1:D10)
CORREL(R1C3:R10C3,R1C4:R10C4)
CORREL({5,10,15,20,25},{4,8,16,32,64})
gives the result 0.9332565253
CORREL({73000,45000,40360},{42,70,40})
gives the result 0.3261046660
See Also
COVAR
4.2.58 COS
This function
returns the cosine of the specified angle.
Syntax
COS(angle)
Arguments
This function can
take any real number as an argument. The angle
argument is the angle in radians for which you want the cosine.
Remarks
If the angle is in
degrees, multiply it by PI/180 to convert it to radians.
Data Types
Accepts numeric
data. Returns numeric data.
Examples
COS(B2)
COS(R1C3)
COS(45*PI()/180)
gives the result 0.7071067812
COS(RADIANS(30))
See Also
ACOS
ACOSH
COSH
4.2.59 COSH
This function
returns the hyperbolic cosine of the specified value.
Syntax
COSH(value)
Arguments
This function can
take any real number as an argument.
Data Types
Accepts numeric
data. Returns numeric data.
Examples
COSH(B3)
COSH(R1C2)
COSH(4) gives the result
27.3082328360
See Also
ACOSHCOS
4.2.60 COUNT
This function
returns the number of cells that contain numbers.
Syntax
COUNT(value1,value2,...)
COUNT(array)
Arguments
The arguments may be
separate values or an array of values. Up to 255 arguments of individual cells
may be included.
Remarks
This function counts
the number of cells that contain numbers in the specified cell range.
This function differs from COUNTA which also includes text or logical values as
well as numbers. Data Types
Accepts cell references.
Returns numeric data.
Examples
COUNT(B2,B5,B8,D5,D8)
COUNT(A1:G5)
COUNT(R6C3:R9C4,2)
See Also
COUNTA
4.2.61 COUNTA
This function
returns the number of number of cells that contain numbers, text, or logical
values.
Syntax
COUNTA(value1,value2,...)
COUNTA(array)
Arguments
The arguments may be
separate values or an array of values. Up to 255 arguments of individual cells
may be included.
Remarks
This function counts
the number of nonempty cells in the specified cell range.
This function
differs from COUNT
because it includes text or logical values as well as numbers.
Accepts cell
references. Returns numeric data.
Examples
COUNTA(B2,D2,E4,E5,E6)
COUNTA(A1:G5)
COUNTA(R6C3:R9C4)
See Also
COUNT
4.2.62 COUNTBLANK
This function
returns the number of empty (or blank) cells in a range of cells on a sheet.
Syntax
COUNTBLANK(cellrange)
Arguments
This function takes
a cell range reference as an argument.
Remarks
This function counts the number of empty or
blank cells in the specified cell range on one sheet. This function does not
count cells containing an empty string "". A cell is empty if the
cell's Value is null (Nothing in VB). Note that there is a difference being a
cell's Value being null and a cell's Value being the empty string "".
For example, consider the following Spread code in C#:
spread.Sheets[0].Cells[0,0].Value = null; //
empty spread.Sheets[0].Cells[1,0].Value = ""; // string
spread.Sheets[0].Cells[2,0].Value = "abc"; // string
spread.Sheets[0].Cells[3,0].Value = 123.0; // number
spread.Sheets[0].Cells[4,0].Formula = "COUNTBLANK(A1:A4)";
The formula in cell
A5 evaluates to 1 because cell A1 is the only cell in the range A1:A4 that is
empty.
Note: Function implementation generally tries to follow the behavior found in
popular spreadsheet applications; however, not all these applications agree
whether the empty string "" should be treated the same as an empty
cell. In GC.Spread, both the COUNTBLANK and ISBLANK functions consistently treat the empty string
"" differently than an empty cell.
Data Types
Accepts cell range
reference. Returns numeric data.
Examples
COUNTBLANK(A1:G5)
COUNTBLANK(R6C3:R9C4)
See Also
COUNTIF
TYPE
4.2.63 COUNTIF
This function returns
the number of cells that meet a certain condition.
Syntax
COUNTIF(cellrange,condition) Arguments
This function has
these arguments:
Argument Description
cellrange Range of cells to count; cell range reference condition Condition
that determines which cells are counted, as a text, number, or expression
Accepts cell range reference. Returns numeric data.
Examples
COUNTIF(A1:G5,"test")
COUNTIF(R6C3:R9C4,"<2")
See Also
COUNT
COUNTA
COUNTBLANK
SUMIF
4.2.64 COUNTIFS
This function
returns the number of cells that meet multiple conditions.
Syntax
COUNTIFS(cellrange,condition)
Arguments
This function has
these arguments:
Argument Description
cellrange
Range of cells to count; cell
range reference condition Condition
that determines which cells are counted, as a text, number, or expression Data Types
Accepts cell range
reference. Returns numeric data.
Examples
COUNTIFS(A1:G5,"test",B3:D3,"=Yes")
COUNTIFS(R6C3:R9C4,"<2")
See Also
COUNT
COUNTA
COUNTBLANK
SUMIF
4.2.65 COUPDAYBS
This function
calculates the number of days from the beginning of the coupon period to the
settlement date.
Syntax
COUPDAYBS(settlement,maturity,frequency,basis)
Arguments
This function has
these arguments:
Argument Description
settlement
Settlement date for the
security maturity Maturity date for
the security frequency Frequency of
payment, number of coupon payments per year; must be 1, 2, or 4 basis [Optional] Integer representing
the basis for day count (Refer to Day Count Basis.)
Remarks
This function
returns an error if settlement or maturity is invalid (#VALUE!), or if frequency is a number other than 1, 2,
or 4 (#NUM!). All arguments are truncated to integers. If basis is greater than
4 or less than 0, a #NUM! error is returned. If settlement is greater than or
equal to maturity, a #NUM! error is returned.
Accepts numeric and
DateTime object data. Returns numeric data.
Examples
COUPDAYBS(A1,A2,A3,A4)
See Also
COUPDAYS
4.2.66 COUPDAYS
This function
returns the number of days in the coupon period that contains the settlement
date.
Syntax
COUPDAYS(settlement,maturity,frequency,basis)
Arguments
This function has
these arguments:
Argument Description
settlement
Settlement date for the
security maturity Maturity date for
the security frequency Frequency of
payment, number of coupon payments per year; must be 1, 2, or 4 basis [Optional] Integer representing
the basis for day count (Refer to Day Count Basis.)
Remarks
This function
returns an error if settlement or maturity is invalid (#VALUE!), or if frequency is a number other than 1, 2,
or 4 (#NUM!). All arguments are truncated to integers. If basis is greater than
4 or less than 0, a #NUM! error is returned. If settlement is greater than or
equal to maturity, a #NUM! error is returned.
Data Types
Accepts numeric and
DateTime object data. Returns numeric data.
Examples
COUPDAYS(A1,A2,A3,A4)
See Also
COUPDAYBS
DURATION
4.2.67 COUPDAYSNC
This function
calculates the number of days from the settlement date to the next coupon date.
Syntax
COUPDAYSNC(settlement,maturity,frequency,basis)
Arguments
This function has
these arguments:
Argument Description
settlement
Settlement date for the
security maturity Maturity date for
the security frequency Frequency of
payment, number of coupon payments per year; must be 1, 2, or 4 basis [Optional] Integer representing
the basis for day count (Refer to Day Count Basis.)
Remarks
This function
returns an error if settlement or maturity is invalid (#VALUE!), or if frequency is a number other than 1, 2,
or 4 (#NUM!). All arguments are truncated to integers. If basis is greater than
4 or less than 0, a #NUM! error is returned. If settlement is greater than or
equal to maturity, a
#NUM! error is
returned.
Data Types
Accepts numeric and
DateTime object data. Returns numeric data.
Examples
COUPDAYSNC(A1,A2,A3,A4)
See Also
COUPDAYBS
COUPDAYS
4.2.68 COUPNCD
This function
returns a date number of the next coupon date after the settlement date.
Syntax
COUPNCD(settlement,maturity,frequency,basis)
Arguments
This function has
these arguments:
Argument Description
settlement
Settlement date for the
security maturity Maturity date for
the security frequency Frequency of
payment, number of coupon payments per year; must be 1, 2, or 4 basis [Optional] Integer representing
the basis for day count (Refer to Day Count Basis.)
Remarks
This function
returns an error if settlement or maturity is invalid (#VALUE!), or if frequency is a number other than 1, 2,
or 4 (#NUM!). All arguments are truncated to integers. If basis is greater than
4 or less than 0, a #NUM! error is returned. If settlement is greater than or
equal to maturity, a #NUM! error is returned.
Data Types
Accepts numeric and
DateTime object data. Returns numeric data.
Examples
COUPNCD(A1,A2,A3,A4) COUPNCD(A1,A2,A3,A4)
See Also
COUPPCD
4.2.69 COUPNUM
This function
returns the number of coupons due between the settlement date and maturity
date.
Syntax
COUPNUM(settlement,maturity,frequency,basis)
Arguments
This function has
these arguments:
Argument Description
settlement
Settlement date for the
security maturity Maturity date for
the security frequency Frequency of
payment, number of coupon payments per year; must be 1, 2, or 4 basis [Optional] Integer representing
the basis for day count (Refer to Day Count Basis.)
Remarks
This function
returns an error if settlement or maturity is invalid (#VALUE!), or if frequency is a
number other than 1,
2, or 4 (#NUM!). All arguments are truncated to integers. If basis is greater
than 4 or less than 0, a #NUM! error is returned. If settlement is greater than
or equal to maturity, a #NUM! error is returned.
Data Types
Accepts numeric and
DateTime object data. Returns numeric data.
Examples
COUPNUM(A1,A2,A3,A4)
COUPNUM(R6C3:R9C4)
See Also
COUPDAYS
4.2.70 COUPPCD
This function
returns a date number of the previous coupon date before the settlement date.
Syntax
COUPPCD(settlement,maturity,frequency,basis)
Arguments
This function has
these arguments:
Argument Description
settlement Settlement date for the security maturity Maturity date for the security frequency Frequency of payment, number
of coupon payments per year; must be 1, 2, or 4 basis [Optional] Integer representing the basis for day count
(Refer to Day Count Basis.) Remarks
This function
returns an error if settlement or maturity is invalid (#VALUE!), or if frequency is a number other than 1, 2,
or 4 (#NUM!). All arguments are truncated to integers. If basis is greater than
4 or less than 0, a #NUM! error is returned. If settlement is greater than or
equal to maturity, a #NUM! error is returned.
Data Types
Accepts numeric and
DateTime object data. Returns numeric data.
Examples
COUPPCD(B1,B2,B3,B4)
COUPPCD(R6C3,R9C4,R1C1,R2C2)
See Also
COUPNCD
4.2.71 COVAR
This function
returns the covariance, which is the average of the products of deviations for
each data point pair in two sets of numbers.
Syntax
COVAR(array1,array2)
Arguments
The two arrays of
data in the arguments of this function should meet these criteria:
· The
data should contain numbers, names, arrays, or references that are numeric. If
some cells do not contain numeric data, they are ignored.
· The data sets should be the same size, with the
same number of data points.
· The
data sets should not be empty, nor should the standard deviation of their
values equal zero.
Remarks
Use this covariance
function to determine the relationship between two sets of data. For example,
you can examine whether greater income accompanies greater levels of education
in a population.
The covariance is
calculated as follows, where n is the
size of the arrays and mu is the mean.
Data Types
Accepts arrays of
numeric data for both arguments. Returns numeric data.
Examples
COVAR(J2:J5,L2:L5)
COVAR(R2C12:R15C12,R2C14:R15C14)
COVAR({7,5,6},{7,4,4})
gives the result 1
COVAR({5,10,15,20,25},{4,8,16,32,64})
gives the result 144
See Also
CORREL
VAR
.
4.2.72 COVARIANCE.P
This function
returns the population covariance, which is the average of the products of
deviations for each data point pair in two sets of numbers.
Syntax
COVARIANCE.P(array1,array2)
Arguments
The two arrays of
data in the arguments of this function should meet these criteria:
· The
data should contain numbers, names, arrays, or references that are numeric. If
some cells do not contain numeric data, they are ignored.
· The
data sets should be the same size, with the same number of data points.
· The
data sets should not be empty, nor should the standard deviation of their
values equal zero.
Remarks
Use this covariance
function to determine the relationship between two sets of data. For example,
you can determine whether greater income accompanies greater levels of
education in a population.
The covariance is
calculated as follows, where n and y are the sample means, AVERAGE(array1)
and AVERAGE(array2), and n is the sample size.
COVARIANCE.P
Equation
Data Types
Accepts arrays of
numeric data for both arguments. Returns numeric data.
Examples
COVARIANCE.P(J2:J5,L2:L5)
COVARIANCE.P(R2C12:R15C12,R2C14:R15C14)
COVARIANCE.P({7,5,6},{7,4,4})
gives the result 1
COVARIANCE.P({5,10,15,20,25},{4,8,16,32,64})
gives the result 144
4.2.73 COVARIANCE.S
This function
returns the sample covariance, which is the average of the products of
deviations for each data point pair in two sets of numbers.
Syntax
COVARIANCE.S(array1,array2)
Arguments
The two arrays of
data in the arguments of this function should meet these criteria:
· The
data should contain numbers, names, arrays, or references that are numeric. If
some cells do not contain numeric data, they are ignored.
· The data sets should be the same size, with the
same number of data points.
· The
data sets should not be empty, nor should the standard deviation of their
values equal zero.
Remarks
Use
this covariance function to determine the relationship between two sets of
data. For example, you can determine whether greater income accompanies greater
levels of education. Data Types
Accepts arrays of
numeric data for both arguments. Returns numeric data.
Examples
COVARIANCE.S(J2:J5,L2:L5)
COVARIANCE.S(R2C12:R15C12,R2C14:R15C14)
COVARIANCE.S({7,5,6},{7,4,4})
gives the result 1.5
COVARIANCE.S({5,10,15,20,25},{4,8,16,32,64})
gives the result 180
4.2.74 CRITBINOM
This function
returns the criterion binomial, the smallest value for which the cumulative
binomial distribution is greater than or equal to a criterion value.
Syntax
CRITBINOM(n,p,alpha)
Arguments
This function has these
arguments:
Argument Description
n Number of trials; if not an integer, the number
is truncated p Probability of success
on each trial; number between 0 and 1 alpha
Alpha, value for the criterion
Data Types
Accepts numeric data
for all arguments. Returns numeric data.
Examples
CRITBINOM(B5,0.75,0.92)
CRITBINOM(R5C2,R8C14,0.75)
CRITBINOM(14,0.75,0.85)
gives the result 12
See Also
BINOMDIST
4.2.75 CUMIPMT
This function
returns the cumulative interest paid on a loan between the starting and ending periods.
Syntax
CUMIPMT(rate,nper,pval,startperiod,endperiod,paytype)
Arguments
This function has
these arguments:
Argument 
Description 
rate 
Interest rate 
nper 
Total number of payment
periods 
pval 
Present value 
startperiod 
Starting period 
endperiod 
Ending period 
paytype Remarks 
Type of payment timing; can be any of: 0  Payment at end of the period 1  Payment at beginning of the period 
This functions
returns a #NUM! error when rate, nper, or pval is negative or zero. Nper, startperiod, endperiod, and paytype
are truncated to integers. If startperiod or endperiod is less than 1 or
startperiod is greater than endperiod, a #NUM! error is returned. If paytype is
a number other than 0 or 1, a #NUM! error is returned.
Data Types
Accepts numeric and
DateTime object data. Returns numeric data.
Examples
CUMIPMT(B2/12,B4*12,C4,14,20,0) CUMIPMT(B2/12,B4*12,C4,14,20,0)
See Also
CUMPRINC
INTRATE
4.2.76 CUMPRINC
This function
returns the cumulative principal paid on a loan between the start and end
periods.
Syntax
CUMPRINC(rate,nper,pval,startperiod,endperiod,paytype)
Arguments
This function has
these arguments:
Argument 
Description 
rate 
Interest rate 
nper 
Total number of payment
periods 
pval 
Present value 
startperiod 
Starting period 
endperiod 
Ending period 
paytype Remarks 
Type of payment timing; can be any of: 0  Payment at end of the period 1  Payment at beginning of the period 
This functions
returns a #NUM! error when rate, nper, or pval is negative or zero. Nper, startperiod, endperiod, and paytype
are truncated to integers. If startperiod or endperiod is less than 1 or
startperiod is greater than endperiod, a #NUM! error is returned. If paytype is
a number other than 0 or 1, a #NUM! error is returned.
Data Types
Accepts numeric and
DateTime object data. Returns numeric data.
Examples
CUMPRINC(B2/12,B4*12,C4,14,20,0)
CUMPRINC(B2/12,B4*12,C4,14,20,0)
See Also
CUMIPMT
IPMT
.
4.2.77 DATE
This function
returns the DateTime object for a particular date, specified by the year,
month, and day.
Syntax
DATE(year,month,day)
Arguments
This function has
these arguments:
Argument Description
Number representing the year, from 1 to 9999,
using four digits; if not integer, number
year is truncated
month Number representing the month of the year; if not integer, number is
truncated day Number representing the day of the month; if not
integer, number is truncated
If month is
greater than 12, then month increments by the number of months over 12 and the
year advances, if needed. For example, DATE(2003,16,2) returns the DateTime
object representing April 2, 2004.
If day is
greater than the number of days in the specified month, then day increments
that number of days from the first day of the next month. For example,
DATE(2004,1,35) returns the DateTime object representing February 4, 2004.
If values for
the arguments are not integers, any decimal places are truncated. Negative
values for months are taken from the year into previous years. Negative values
for days are taken from the month into previous months.
Data Types
Accepts numeric
data. Returns a DateTime object.
Examples
DATE(A1,B1,C1)
DATE(R1C1,R1C2,R1C3)
DATE(2003,1,1) gives
the result January 1, 2003
DATE(2004,2,10)
gives the result February 10, 2004
See Also
DATEVALUE
TIME
.
4.2.78 DATEDIF
This function
returns the number of days, months, or years between two dates.
Syntax
DATEDIF(date1,date2,outputcode)
Arguments
The first two
arguments are any dates, as strings, numeric values, or DateTime objects.
The output codes
are:
Code Description
"D" The number of days between date1 and date2
"M" The number of complete months between date1 and date2
"Y" The number of complete years between date1 and date2
"YD" The number of days
between date1 and date2 as if they were in the same year
"YM" The number of
months between date1 and date2 as if they were in the same year
"MD" The number of days
between date1 and date2 as if they were in the same month and year
Data Types
Accepts strings,
numeric values, and DateTime objects. Strings and numbers are converted to
DateTime objects.
Examples
DATEDIF(A1,B1,C1)
DATEDIF(R1C1,R1C2,R1C3)
DATEDIF("2001/1/1","2003/1/1","Y")
See Also
DATEVALUE
TIME
4.2.79 DATEVALUE
This function
returns a DateTime object of the specified date.
Syntax
DATEVALUE(date_string)
Arguments
The argument for
this function is a date as a string.
Remarks
Use this function to
convert a date represented by text to a DateTime object in standard format.
Data Types
Accepts string data.
Returns a DateTime object.
Examples
DATEVALUE(B18)
DATEVALUE(R18C2)
DATEVALUE("2004/10/6")
gives the result 10/6/2004 12:00:00 AM
See Also
DATE
TIMEVALUE
4.2.80 DAVERAGE
This function
calculates the average of values in a column of a list or database that match the
specified conditions.
Syntax
DAVERAGE(database, field, criteria)
Arguments
This function has
these arguments:
Argument Description
database Range of cells that make up the database; cell range reference field Column
in the database, referred to by label or index criteria Range of cells that
specify which rows in the database are used; cell range reference
The database argument is a range of cells
that make up the database. Each column represents a field. The first row
represents the field labels. Each remaining row represents a record of data.
The field argument determines which column
in the database to use. The field
argument can be a
string (field label)
or a number (field index).
The criteria argument is a range of cells
that specify which rows in the database contain the conditions that select a
subset of the data in the database. The first row represents field labels. The
remaining rows represent conditions. Conditions in the same row are combined
using an AND operation. Conditions in different rows are combined using an OR
operation. Each condition can be a number or a string. The string can include a
comparison operator (=, <>, <, >, <=, >=). If no operator is
included then the equal operator (=) is assumed.
Wild card characters
are not supported in the criteria
argument.
Remarks
This is one of
several database or list functions that treat a range of cells as if they were
a database.
Data Types
Accepts cell ranges
for database and criteria. Accepts a string or a number for field. Returns
numeric data.
Examples
DAVERAGE(A4:E10,3,A4:E10)
DAVERAGE(A1:A9,"Income",D5:D8)
See Also
AVERAGE
DVAR
DVARP
VAR
VARP
4.2.81 DAY
This function
returns the day number of the month (integer 1 to 31) that corresponds to the
specified date.
Syntax
DAY(date)
Arguments
Specify the date
argument as a number (as in 37806.5) a string (as in "7/4/2003
12:00"), or a DateTime object, as in DATE(2003,7,4).
Data Types
Accepts numeric,
string, or DateTime object data. Returns numeric data.
Examples
DAY(A2)
DAY(R2C1)
DAY(366778) gives
the result 14
DAY(33239) gives the
result 1 (because 33239 is the value for January 1, 1991)
DAY("7/4/2003
12:00")
DAY(DATE(2003,7,4))
See Also
DATE
DATEVALUE
MONTH
WEEKDAY
4.2.82 DAYS360
This function
returns the number of days between two dates based on a 360day year.
Syntax
DAYS360(startdate,enddate,method)
Arguments
This function has
these arguments:
Argument Description
startdate
Date from which to calculate
days enddate Date to which to
calculate days
[Optional] Method for calculating days; if FALSE or omitted, uses U.S.
(NASD) method method; if TRUE, uses
European method.
Specify the date
argument as a number (as in 37806.5) a string (as in "7/4/2003
12:00"), or a DateTime object, as in DATE(2003,7,4).
The methods for
calculating the number of days can vary. The U.S. or NASD method works as
follows:
· If the starting date is the 31st of a month, it
becomes equal to the 30th of the same month.
· If the
ending date is the 31st of a month and the starting date is earlier than the
30th of a month, the ending date becomes equal to the 1st of the next month.
· If the
ending date is the 31st of a month and the starting date is the 30th or 31st of
a month, the ending date becomes equal to the 30th of the ending date month.
The European method
considers starting dates or ending dates that occur on the 31st of a month to
be equal to the 30th of the same month.
Remarks
Use this function to
help compute payments if your accounting system is based on a 360day year (twelve
30day months).
Data Types
Accepts numeric,
string, or DateTime object data for the two date arguments and boolean for the
method argument. Returns numeric data.
Examples
DAYS360(B8,C8)
DAYS360(R8C2,R8C3)
DAYS360("7/15/2004","12/25/2004")
gives the result 160
See Also
DATEVALUE
DAY
.
4.2.83 DB
This function
calculates the depreciation of an asset for a specified period using the
fixeddeclining balance method.
Syntax
DB(cost,salvage,life,period,month)
Arguments
This functions has
these arguments:
Argument Description
cost Initial cost of the asset salvage Value at the end of the depreciation period life Number of periods over which the
asset is being depreciated
Period for which you want to calculate the
depreciation; use the same units as the life
period argument
[Optional] Number of months in the first year;
if omitted, the calculation uses 12
month
months
Remarks
The fixeddeclining
balance method computes depreciation at a fixed rate. This function uses the
following equation to calculate depreciation for a period:
(cost – total
depreciation from prior periods) x rate
where:
rate = 1 –
((salvage/cost)^(1/life)), rounded to three decimal places
Depreciation for the
first and last periods is a special case. For the first period, the function uses
this equation:
dep = cost x rate x
month/12
For the last period,
the function uses this equation:
dep = ((cost – total
dep. from prior periods) x rate x (12 – month))/12.
Data Types
Accepts numeric data
for all arguments. Returns numeric data.
Examples
DB(B1,1000,10,1)
DB(R1C2,10000,10,1)
DB(500000,5000,5,1,10)
gives the result $25,0833.3333333333
See Also
DDB
SLN
SYD
4.2.84 DCOUNT
This function counts
the cells that contain numbers in a column of a list or database that match the
specified conditions.
Syntax
DCOUNT(database, field, criteria)
Arguments
This function has
these arguments:
Argument Description
database Range of cells that make up the database; cell range reference field [Optional]
Column in the database, referred to by label or index criteria Range of cells that
specify which rows in the database are used; cell range reference
The database argument is a range of cells
that make up the database. Each column represents a field. The first row
represents the field labels. Each remaining row represents a record of data.
The field argument determines which column
in the database to use. The field
argument can be a string (field label) or a number (field index). The field
argument is optional. If omitted the function counts all the records that meet
the criteria.
The criteria argument is a range of cells
that specify which rows in the database contain the conditions that select a
subset of the data in the database. The first row represents field labels. The
remaining rows represent conditions. Conditions in the same row are combined
using an AND operation. Conditions in different rows are combined using an OR
operation. Each condition can be a number or a string. The string can include a
comparison operator (=, <>, <, >, <=, >=). If no operator is
included then the equal operator (=) is assumed.
Wild card characters
are not supported in the criteria
argument.
Remarks
This is one of
several database or list functions that treat a range of cells as if they were
a database.
Data Types
Accepts cell ranges
for database and criteria. Accepts a string or a number for field. Returns
numeric data.
Examples
DCOUNT(A4:E10,"Type",A4:E10)
DCOUNT(A1:A9,3,D5:D8)
See Also
COUNT
COUNTA
DCOUNTA
4.2.85 DCOUNTA
This function counts
the nonblank cells in a column of a list or database that match the specified
conditions.
Syntax
DCOUNTA(database, field, criteria)
Arguments
This function has
these arguments:
Argument Description
database Range of cells that make up the database; cell range reference field [Optional]
Column in the database, referred to by label or index criteria Range of cells that
specify which rows in the database are used; cell range reference
The database argument is a range of cells
that make up the database. Each column represents a field. The first row
represents the field labels. Each remaining row represents a record of data.
The field argument determines which column
in the database to use. The field
argument can be a string (field label) or a number (field index). The field
argument is optional. If omitted the function counts all the records that meet
the criteria.
The criteria argument is a range of cells
that specify which rows in the database contain the conditions that select a
subset of the data in the database. The first row represents field labels. The
remaining rows represent conditions. Conditions in the same row are combined
using an AND operation. Conditions in different rows are combined using an OR
operation. Each condition can be a number or a string. The string can include a
comparison operator (=, <>, <, >, <=, >=). If no operator is
included then the equal operator (=) is assumed.
Wild card characters
are not supported in the criteria
argument.
Remarks
This is one of
several database or list functions that treat a range of cells as if they were
a database.
Data Types
Accepts cell ranges
for database and criteria. Accepts a string or a number for field. Returns
numeric data.
Examples
DCOUNTA(A4:E10,"Type",A4:E10)
DCOUNTA(A1:A9,3,D5:D8)
See Also
COUNT
COUNTA
DAVERAGE
DCOUNT
4.2.86 DDB
This function
calculates the depreciation of an asset for a specified period using the
doubledeclining balance method or another method you specify.
Syntax
DDB(cost,salvage,life,period,factor)
Arguments
This function has
these arguments:
Argument Description
cost Initial
cost of the asset
salvage Value at the end of depreciation life Number of periods over which the asset is
being depreciated
Period for which you want to calculate the
depreciation in the same units as the life
period argument
[Optional] Rate at which the value declines; if
omitted, the calculation uses 2 (double
factor declining method)
All arguments must
be positive numbers.
Remarks
This function uses
the following calculation for depreciation for a period:
cost – salvage(total
depreciation from prior periods) x factor/life
Data Types
Accepts numeric data
for all arguments. Returns numeric data.
Examples
DDB(B1,1000,10,1)
DDB(R1C2,10000,10,1)
DDB(500000,5000,5,1,4)
gives the result $40,0000
See Also
DB
SYD
4.2.87 DEC2BIN
This function
converts a decimal number to a binary number.
Syntax
DEC2BIN(number,places)
Arguments
This function has
these arguments:
Argument Description
number
Decimal numeric value to
convert in the range of 512 to 511 places
[Optional] Number of characters to return; if not an integer, the number is
truncated
If places argument is omitted, the
calculation uses the minimum number of characters necessary. This argument is
useful for adding leading zeros to the result.
Remarks
An error value is
returned if the number is nonnumeric
or outside the range, or if the places
value is nonnumeric, negative, or too small.
Data Types
Accepts numeric
data. Returns numeric data.
Examples
DEC2BIN(3,3)
See Also
BIN2DEC
DEC2HEX
DEC2OCT
OCT2BIN
4.2.88 DEC2HEX
This function
converts a decimal number to a hexadecimal number.
Syntax
DEC2HEX(number,places)
Arguments
This function has
these arguments:
Argument Description
Decimal numeric
value to convert in the range of 549,755,813,888 to
number
549,755,813,887
places [Optional]
Number of characters to return; if not an integer, the number is truncated
If places argument is omitted, the
calculation uses the minimum number of characters necessary. This argument is
useful for adding leading zeros to the result.
Remarks
An error value is
returned if the number is nonnumeric
or outside the range, or if the places
value is nonnumeric, negative, or too small.
Data Types
Accepts numeric
data. Returns numeric data.
Examples
DEC2HEX(103,4)
See Also
BIN2HEX
DEC2BIN
DEC2OCT
OCT2HEX
4.2.89 DEC2OCT
This function
converts a decimal number to an octal number.
Syntax
DEC2OCT(number,places)
Arguments
This function has
these arguments:
Argument Description
number Decimal numeric value to convert in the range of 536,870,912 and
536,870,911 places [Optional] Number of characters to return; if not an
integer, the number is truncated
If places argument is omitted, the
calculation uses the minimum number of characters necessary. This argument is
useful for adding leading zeros to the result.
Remarks
An error value is
returned if the number is nonnumeric
or outside the range, or if the places
value is nonnumeric, negative, or too small.
Data Types
Accepts numeric
data. Returns numeric data.
Examples
DEC2OCT(99)
See Also
BIN2OCT
DEC2BIN
DEC2HEX
OCT2BIN
4.2.90 DEGREES
This function
converts the specified value from radians to degrees.
Syntax
DEGREES(angle)
Arguments
This function takes
any real number angle value as the argument.
Remarks
This function
converts angle in radians to angle in degrees.
Data Types
Accepts numeric
data. Returns numeric data.
Examples
DEGREES(B3)
DEGREES(R1C2)
DEGREES(PI()) gives
the result 180
See Also
PI
RADIANS
4.2.91 DELTA
This function
identifies whether two values are equal. Returns 1 if they are equal; returns 0
otherwise.
Syntax
DELTA(value1,value2)
Arguments
This function takes
two values as arguments.
Remarks
Also called the
Kronecker Delta function. This is a discrete version of the Dirac delta
function.
Data Types
Accepts numeric data.
Returns numeric data (0 or 1).
Examples
DELTA(A1,5)
DELTA(R1C4,R2C5)
DELTA(3,3) gives the
result 1
DELTA(3,2) gives the
result 0
DELTA(3,2.99999)
gives the result 0
DELTA(3,QUOTIENT(6,2))
gives the result 1
See Also
GESTEP
4.2.92 DEVSQ
This function calculates
the sum of the squares of deviations of data points (or of an array of data
points) from their sample mean.
Syntax
DEVSQ(value1,value2, ...)
DEVSQ(array)
DEVSQ(array1,array2,...)
Arguments
Each argument
can be a doubleprecision floatingpoint value, an integer value, or an array
(cell range) of these. Up to 255 arguments may be included. You can use a
single array (cell range) instead of a list of values. You can use multiple
arrays (cell ranges) as well.
Remarks
This is a measure of
the variability in a data set.
The sum of squared
deviations is calculated as follows, where n
is the number of values.
DEVSQ
Equation
If an array or cell
reference argument contains text, logical values, or empty cells, the function
ignores those values; however, the function includes in calculations cells with
the value zero.
Data Types
Accepts numeric data
for all arguments or array of numeric data. Returns numeric data.
Examples
DEVSQ(B3,B5,B9,B10)
DEVSQ(B3:B14)
DEVSQ(R3C2,R5C2,R9C2)
DEVSQ(R3C2:R3C12)
DEVSQ(35,31,47,51,37,31,58,39)
gives the result 680.875
See Also
AVEDEV
AVERAGE
4.2.93 DGET
This function
extracts a single value from a column of a list or database that matches the
specified conditions.
Syntax
DGET(database, field, criteria)
Arguments
Argument Description
database Range of cells that make up the database; cell range reference field Column
in the database, referred to by label or index criteria Range of
cells that specify which rows in the database are used; cell range reference
The database argument is a range of cells
that make up the database. Each column represents a field. The first row
represents the field labels. Each remaining row represents a record of data.
The field argument determines which column
in the database to use. The field
argument can be a string (field label) or a number (field index).
The criteria argument is a range of cells
that specify which rows in the database contain the conditions that select a
subset of the data in the database. The first row represents field labels. The
remaining rows represent conditions. Conditions in the same row are combined
using an AND operation. Conditions in different rows are combined using an OR
operation. Each condition can be a number or a string. The string can include a
comparison operator (=, <>, <, >, <=, >=). If no operator is
included then the equal operator (=) is assumed.
Wild card characters
are not supported in the criteria
argument.
Remarks
If no value matches
the criteria argument, a #VALUE! error is returned. A #NUM! error is returned
if more than one match is found.
This is one of
several database or list functions that treat a range of cells as if they were
a database.
Data Types
Accepts cell ranges
for database and criteria. Accepts a string or a number for field. Returns numeric
data.
Examples
DGET(A4:E10,"Type",A4:E10)
DGET(A1:A9,3,D5:D8)
See Also
DAVERAGE
DCOUNT
4.2.94 DISC
This function
calculates the discount rate for a security.
Syntax
DISC(settle,mature,pricep,redeem,basis)
Arguments
This function has
these arguments:
Argument Description
settle
Settlement date for the
security mature Maturity date for the
security pricep Amount invested in
the security redeem Amount to be
received at maturity basis [Optional]
Integer representing the basis for day count (Refer to Day
Count Basis.)
Remarks
Settle,
mature, and basis are truncated to integers. If settle or mature is not a valid
serial date number, a #VALUE! error is returned. If pricep or redeem is less
than or equal to 0, a #NUM! error is returned. If basis is less than 0 or
greater than 4, a #NUM! error is returned. If settle is greater than or equal
to mature, a #NUM! error is returned.
Data Types
Accepts numeric and
DateTime object data. Returns numeric data.
Examples
DISC(A1,B1,C4,100,2)
DISC("3/15/2003","5/15/2003",R3C4,R5C5,4)
DISC("5/15/2004","9/1/2004",98.2,100,3)
gives the result 0.0602752294
See Also
INTRATE
PRICEDISC
RATE
4.2.95 DMAX
This function
returns the largest number in a column of a list or database that matches the
specified conditions.
Syntax
DMAX(database, field, criteria)
Arguments
Argument Description
database Range of cells that make up the database; cell range reference field Column
in the database, referred to by label or index criteria Range of
cells that specify which rows in the database are used; cell range reference
The database argument is a range of cells
that make up the database. Each column represents a field. The first row
represents the field labels. Each remaining row represents a record of data.
The field argument determines which column
in the database to use. The field
argument can be a
string (field label)
or a number (field index).
The criteria argument is a range of cells
that specify which rows in the database contain the conditions that select a
subset of the data in the database. The first row represents field labels. The
remaining rows represent conditions. Conditions in the same row are combined
using an AND operation. Conditions in different rows are combined using an OR
operation. Each condition can be a number or a string. The string can include a
comparison operator (=, <>, <, >, <=, >=). If no operator is
included then the equal operator (=) is assumed.
Wild card characters
are not supported in the criteria
argument.
Remarks
This is one of
several database or list functions that treat a range of cells as if they were
a database.
Data Types
Accepts cell ranges
for database and criteria. Accepts a string or a number for field. Returns
numeric data.
Examples
DMAX(A4:E10,"Type",A4:E10)
DMAX(A1:A9,3,D5:D8)
See Also
DAVERAGE
DCOUNT
DMIN
MAX
MIN
4.2.96 DMIN
This function
returns the smallest number in a column of a list or database that matches the
specified conditions.
Syntax
DMIN(database, field, criteria)
Arguments
Argument Description
database Range of cells that make up the database; cell range reference field Column
in the database, referred to by label or index criteria Range of cells
that specify which rows in the database are used; cell range reference
The database argument is a range of cells
that make up the database. Each column represents a field. The first row
represents the field labels. Each remaining row represents a record of data.
The field argument determines which column
in the database to use. The field
argument can be a string (field label) or a number (field index).
The criteria argument is a range of cells
that specify which rows in the database contain the conditions that select a
subset of the data in the database. The first row represents field labels. The
remaining rows represent conditions. Conditions in the same row are combined
using an AND operation. Conditions in different rows are combined using an OR operation.
Each condition can be a number or a string. The string can include a comparison
operator (=, <>, <, >, <=, >=). If no operator is included
then the equal operator (=) is assumed.
Wild card characters
are not supported in the criteria
argument.
Remarks
This is one of
several database or list functions that treat a range of cells as if they were
a database.
Data Types
Accepts cell ranges
for database and criteria. Accepts a string or a number for field. Returns
numeric data.
Examples
DMIN(A4:E10,"Type",A4:E10)
DMIN(A1:A9,3,D5:D8)
See Also
DAVERAGE
DCOUNT
DMAX
MAX
MIN
4.2.97 DOLLAR
This function
converts a number to text using currency format, with the decimals rounded to
the specified place.
Syntax
DOLLAR(value,digits)
Arguments
This function has these
arguments:
Argument Description
value Numeric value
to convert to text using the currency format
[Optional] Number of decimal places to
maintain; if negative, the value is rounded to the
digits left of the decimal point; if omitted, the
function rounds to two decimal places
Remarks
This function uses
the current regional Windows settings to determine the format of the returned
string.
Data Types
Accepts numeric data
for both arguments. Returns string data.
Examples
DOLLAR(B5,D2)
DOLLAR(R5C2,R2C4)
DOLLAR(1234.5678,3)
gives the result $1,234.568
DOLLAR(123.45,1)
gives the result $123.5
See Also
DOLLARDE
DOLLARFR
FIXED
4.2.98 DOLLARDE
This function
converts a fraction dollar price to a decimal dollar price.
Syntax
DOLLARDE(fractionaldollar,fraction)
Arguments
This function has
these arguments:
Argument Description
fractionaldollar
Numeric value expressed as a
fraction fraction Denominator of the fraction; if not an integer, the number
is truncated
Remarks
If fraction is not
an integer, it is truncated. If fraction is less than 0, a #NUM! error is
returned. If fraction is 0, a #DIV/0! error is returned.
Data Types
Accepts numeric data
for both arguments. Returns numeric data.
Examples
DOLLARDE(1.10,17)
DOLLARDE(R5C2,R2C4)
See Also
DOLLAR
DOLLARFR
4.2.99 DOLLARFR
This function
converts a decimal number dollar price to a fraction dollar price.
Syntax
DOLLARFR(decimaldollar,fraction)
Arguments
This function has
these arguments:
Argument Description
decimaldollar
Decimal number fraction Denominator
of the fraction; if not an integer, the number is truncated
Remarks
If fraction is not
an integer, it is truncated. If fraction is less than 0, a #NUM! error is
returned. If fraction is 0, a #DIV/0! error is returned.
Data Types
Accepts numeric data
for both arguments. Returns numeric data.
Examples
DOLLARFR(B5,D2)
DOLLARFR(R5C2,R2C4)
DOLLARFR(1.125,16)
gives the result 1.02
See Also
DOLLAR
DOLLARDE
4.2.100 DPRODUCT
This function
multiplies the values in a column of a list or database that match the specified
conditions.
Syntax
DPRODUCT(database, field, criteria)
Arguments
Argument Description
database Range of cells that make up the database; cell range reference field Column
in the database, referred to by label or index criteria Range of
cells that specify which rows in the database are used; cell range reference
The database argument is a range of cells
that make up the database. Each column represents a field. The first row
represents the field labels. Each remaining row represents a record of data.
The field argument determines which column
in the database to use. The field
argument can be a string (field label) or a number (field index).
The criteria argument is a range of cells
that specify which rows in the database contain the conditions that select a
subset of the data in the database. The first row represents field labels. The
remaining rows represent conditions. Conditions in the same row are combined
using an AND operation. Conditions in different rows are combined using an OR
operation. Each condition can be a number or a string. The string can include a
comparison operator (=, <>, <, >, <=, >=). If no operator is
included then the equal operator (=) is assumed.
Wild card characters
are not supported in the criteria
argument.
Remarks
This is one of
several database or list functions that treat a range of cells as if they were
a database.
Data Types
Accepts cell ranges
for database and criteria. Accepts a string or a number for field. Returns
numeric data.
Examples
DPRODUCT(A4:E10,"Type",A4:E10)
DPRODUCT(A1:A9,3,D5:D8)
See Also
DCOUNT
DSUM
PRODUCT
SUM
4.2.101 DSTDEV
This function
estimates the standard deviation of a population based on a sample by using the
numbers in a column of a list or database that match the specified conditions.
Syntax
DSTDEV(database, field, criteria)
Arguments
Argument Description
database Range of cells
that make up the database; cell range reference
field Column in the database, referred to by label or index criteria Range
of cells that specify which rows in the database are used; cell range reference
The database argument is a range of cells
that make up the database. Each column represents a field. The first row
represents the field labels. Each remaining row represents a record of data.
The field argument determines which column
in the database to use. The field
argument can be a string (field label) or a number (field index).
The criteria argument is a range of cells
that specify which rows in the database contain the conditions that select a
subset of the data in the database. The first row represents field labels. The
remaining rows represent conditions. Conditions in the same row are combined
using an AND operation. Conditions in different rows are combined using an OR
operation. Each condition can be a number or a string. The string can include a
comparison operator (=, <>, <, >, <=, >=). If no operator is
included then the equal operator (=) is assumed.
Wild card characters
are not supported in the criteria
argument.
Remarks
This is one of
several database or list functions that treat a range of cells as if they were
a database.
Data Types
Accepts cell ranges
for database and criteria. Accepts a string or a number for field. Returns
numeric data.
Examples
DSTDEV(A4:E10,"Type",A4:E10)
DSTDEV(A1:A9,3,D5:D8)
See Also
DAVERAGE
DSTDEVP
STDEV
4.2.102 DSTDEVP
This function
calculates the standard deviation of a population based on the entire population
using the numbers in a column of a list or database that match the specified
conditions.
Syntax
DSTDEVP(database, field, criteria)
Arguments
Argument Description
database Range of cells that make up the database; cell range reference field Column
in the database, referred to by label or index criteria Range of cells that
specify which rows in the database are used; cell range reference
The database argument is a range of cells
that make up the database. Each column represents a field. The first row
represents the field labels. Each remaining row represents a record of data.
The field argument determines which column
in the database to use. The field
argument can be a string (field label) or a number (field index).
The criteria argument is a range of cells
that specify which rows in the database contain the conditions that select a
subset of the data in the database. The first row represents field labels. The
remaining rows represent conditions. Conditions in the same row are combined
using an AND operation. Conditions in different rows are combined using an OR
operation. Each condition can be a number or a string. The string can include a
comparison operator (=, <>, <, >, <=, >=). If no operator is
included then the equal operator (=) is assumed.
Wild card characters
are not supported in the criteria
argument.
Remarks
This is one of
several database or list functions that treat a range of cells as if they were
a database.
Data Types
Accepts cell ranges
for database and criteria. Accepts a string or a number for field. Returns
numeric data.
Examples
DSTDEVP(A4:E10,"Type",A4:E10)
DSTDEVP(A1:A9,3,D5:D8)
See Also
DAVERAGE
DSTDEV
STDEV
4.2.103 DSUM
This function adds
the numbers in a column of a list or database that match the specified
conditions.
Syntax
DSUM(database, field, criteria)
Arguments
Argument Description
database Range of cells that make up the database; cell range reference field Column
in the database, referred to by label or index criteria Range of
cells that specify which rows in the database are used; cell range reference
The database argument is a range of cells
that make up the database. Each column represents a field. The first row
represents the field labels. Each remaining row represents a record of data.
The field argument determines which column
in the database to use. The field
argument can be a string (field label) or a number (field index).
The criteria argument is a range of cells
that specify which rows in the database contain the conditions that select a
subset of the data in the database. The first row represents field labels. The
remaining rows represent conditions. Conditions in the same row are combined
using an AND operation. Conditions in different rows are combined using an OR
operation. Each condition can be a number or a string. The string can include a
comparison operator (=, <>, <, >, <=, >=). If no operator is
included then the equal operator (=) is assumed.
Wild card characters
are not supported in the criteria
argument.
Remarks
This is one of
several database or list functions that treat a range of cells as if they were
a database.
Data Types
Accepts cell ranges
for database and criteria. Accepts a string or a number for field. Returns
numeric data.
Examples
DSUM(A4:E10,"Type",A4:E10)
DSUM(A1:A9,3,D5:D8)
See Also
DCOUNT
DPRODUCT
PRODUCT
SUM