Formula Reference

Formula Overview

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 built-in functions and extensive capability of formulas:

What is a Formula?

Formulas can consist of values, operators, and functions. Data can be from other cells, a combination of data in another cell and hard-coded data (for example, A1 + 2), or simply hardcoded data (for example, SUM(4,5)). Formulas can perform mathematical operations, such as addition and multiplication, on values in other cells or they can compare values in other cells. Formulas can refer to cells in the same sheet by their absolute cell location or relative to the cell with the formula in it; they can refer to individual cells or a range of contiguous cells. If the values in the referenced cells change, then the value of the formula cell changes.
Formulas can be made up of:
cell references and cell ranges (notation indicating address of cell or cells) operators (that act on one or two values) built-in functions (predefined formulas) constants or array of constants (values you enter that do not change) See the Sample Formula topic for more information.

Sample Formula

Use the setFormula method in the Worksheet class for specifying the formula. Returning the value of the formula property provides a string containing the written expression of the formula, for example,
SUM(A1:B1).

In code the setting of a formula would look something like this in JavaScript (for illustration purposes only):

sheet.getCell(2, 0).formula = "SUM(A1:A10)"

and if added in the cell by the end user:

=SUM(A1:A10)

In this documentation, where examples are shown, the formula appears as:
SUM(A1:A10)

or

SUM(3,4,5) gives the result 12 to express that the result of the formula would display the value of 12 in the cell.

Cell References in a Formula

A formula can refer to constant values or cell references. If a value in any of the referenced cells changes, the result of the formula changes. If you use constant values in the formula instead of references to the cells, the result changes only if you modify the formula (or values in the formula).

If a new row is added right before or after a cell range in a formula then the range does not include the new row.

The following topics describe the different notation styles and reference types:

· A1 Notation
· R1C1 Notation
· Relative and Absolute

Each cell can be referenced by a combination of its column letter (A through Z, then AA to ZZ, AAA to ZZZ, etc.) and row number (1 and beyond) for a total of 2,147,483,648 rows and columns. For example, D50 refers to the cell at the intersection of column D and row 50. To refer to a range of cells, enter the reference for the cell in the upper-left corner of the range, a colon (:), and then the reference to the cell in the lower-right corner of the range.

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 (Letter-Number) notation for the cell reference, but the same would be valid for R1C1 (Number-Number) 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 cross-sheet referencing.

An example of cross-sheet 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 cross-sheet 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 double-precision 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.

Order of Precedence from Highest to Lowest

                                                     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 built-in functions that come with Spread or user-defined 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 two-dimensional array organized into rows and columns. Or it can be thought of as a one-dimensional 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 non-leap 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 x-axis 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 Formula

4.2      Spreadsheet Formula Functions

Spreadsheet provides these built-in 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(14-24) 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 y-coordinates.


ATAN2(x,y)

Arguments

This function can take real numbers as arguments.

Remarks

The arctangent is the angle from the x-axis 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 double-precision floating-point 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 double-precision floating-point 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 double-precision floating-point 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 double-precision floating-point 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 double-precision floating-point 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 non-numeric 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 non-numeric 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 one-tailed probability of the chi-squared 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 one-tailed probability of the chi-squared distribution.

Syntax

CHIINV(prob,deg)

Arguments

This function has these arguments:

                          Argument                                                         Description

prob Probability of the chi-squared 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 chi-squared distribution.

Syntax

CHISQ.DIST(value,deg,cumulative)

Arguments

This function has these arguments:

Argu

Description

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 right-tailed probability of the chi-squared 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 left-tailed probability of the chi-squared distribution.

Syntax

CHISQ.INV(prob,deg)

Arguments

This function has these arguments:

                      Argument                                                         Description

prob Probability of the chi-squared 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 right-tailed probability of the chi-squared distribution.

Syntax

CHISQ.INV.RT(prob,deg)

Arguments

This function has these arguments:

                      Argument                                                         Description

prob Probability of the chi-squared 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 chi-squared 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 chi-squared 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 non-printable characters from text.

Syntax

CLEAN(text)

Arguments

The text argument is any data from which you want to remove non-printable 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 low-level 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 non-printable 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 non-numeric.

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 (1-alpha)%

                   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 (1-alpha)%

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 (1-alpha)%

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,from-unit,to-unit)

Arguments

This function has these arguments:

                    Argument                                                         Description

                   number      Numeric value to convert

from-unit       Convertible units (see table below) of numeric value to convert to-unit          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 (from-unit and to-unit) 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"

 

Horsepower-hour

 

"Hph"

 

Watt-hour

 

"Wh"

 

Foot-pound

 

"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 non-empty 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 360-day 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 360-day year (twelve 30-day 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 fixed-declining 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 fixed-declining 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 non-blank 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 double-declining 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 non-numeric or outside the range, or if the places value is non-numeric, 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 non-numeric or outside the range, or if the places value is non-numeric, 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 non-numeric or outside the range, or if the places value is non-numeric, 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 double-precision floating-point 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