Workbook Functions O-S

 

ODD(any number)

Rounds the specified number up to the nearest odd integer.

See also: CEILING, EVEN, FLOOR, INT, ROUND, TRUNC

 

OFFSET(reference, rows, columns [,height] [,width])

Returns the contents of a range that is offset from a starting point, given by the reference, in the spreadsheet. E.g. OFFSET(A1, 2, 4, 3, 2) refers to the range E3:F5.

 

OR(logical list)

TRUE if at least one of a series of logical arguments in the list is true.

See also: AND, IF, NOT

 

PI()

Returns the value of PI.

See also: COS, SIN, TAN

 

PMT(interest rate, number of periods per annuity, pv [,fv] [,type])

Returns the periodic payment of an annuity, based on regular payments and a fixed periodic interest rate. Pv is the amount the annuity is currently worth and fv is the amount the annuity will be worth (default is 0). Type indicates when the payments are due: 0 refers to payments due at the end of

the period and 1 at the beginning.

See also: FV, IPMT, NPER, PPMT, PV, RATE

 

PPMT(interest rate, period, number of periods, pv [,fv] [,type])

Returns the principle paid on an annuity for a given period. Pv is the amount the annuity is currently worth and fv is the amount the annuity will be worth (default is 0). Type indicates when the payments are due: 0 refers to payments due at the end of the period and 1 at the beginning.

See also: FV, IPMT, NPER, PMT, PV, RATE

 

PRODUCT(number list)

Multiplies a list of numbers and returns the result.

See also: FACT, SUM

 

PROPER(text)

Returns the text string in proper-case format, i.e., the first alphabetic character in a word and alphabetic characters following a number, punctuation mark or space are capitalized.

See also: LOWER, UPPER

 

PV(interest rate, number of periods in the investment, pmt [,fv] [,type])

Returns the present value of an annuity, considering a series of constant payments made over a regular payment period. Pmt is the fixed payment made each period and fv is the amount the annuity will be worth (default is 0). Type indicates when the payments are due: 0 refers to payments due at the end of the period and 1 at the beginning.

See also: FV, IPMT, NPER, PMT, PPMT, RATE

 

RAND()

Returns a number selected randomly from a uniform distribution greater than

or equal to 0 and less than 1.

 

RATE(number of periods, pmt, pv [,fv] [,type] [,guess])

Returns the interest rate per period of an annuity, given a series of constant cash payments made over a regular payment period. Pmt is the fixed payment made each period, fv is the amount the annuity will be worth (default is 0) and guess is the estimate of the interest rate (default is 10%). Type indicates when the payments are due: 0 refers to payments due at the end of the period and 1 at the beginning.

See also: FV, IPMT, NPER, PMT, PPMT, PV

 

REPLACE(original text, start position, number of characters, replacement text)

Replaces part of a text string with another text string.

See also: MID, SEARCH, TRIM

 

REPT(text, number)

Repeats a text string the specified number of times.

 

RIGHT(text [,number of characters])

Returns the rightmost characters from the text string.

See also: LEFT, MID

 

ROUND(number, precision)

Rounds the given number to the supplied number of decimal places. A negative precision means that the digits to the right of the decimal point are dropped and the absolute number of significant digits specified by precision are replaced by zeros.

See also: CEILING, FLOOR, INT, MOD, TRUNC, ROUNDDOWN, ROUNDUP

 

ROUNDDOWN(number, decimal_places)

Rounds the given number down to the specified number of decimal places.

See also: CEILING, FLOOR, INT, MOD, TRUNC, ROUND, ROUNDUP

 

ROUNDUP(number, decimal_places)

Rounds the given number up to the specified number of decimal places.

See also: CEILING, FLOOR, INT, MOD, TRUNC, ROUND, ROUNDDOWN

 

ROW(reference)

Returns the row number of the supplied reference.

See also: COLUMN, ROWS

 

ROWS(range)

Returns the number of rows in a range reference.

See also: COLUMNS, ROW

 

SEARCH(search text, text [,start position])

Locates the position of the first character of a specified text string (search text) within another text string. Start position is the character position where the search begins (default is 1)

See also: FIND, MID, REPLACE, SUBSTITUTE

 

SECOND(serial number)

Returns the second that corresponds to the specified date.

See also: DAY, HOUR, MINUTE, MONTH, NOW, WEEKDAY, YEAR

 

SIGN(any number)

Sign of the specified number: 1 if the number is positive, -1 if it is negative and 0 if it is 0.

See also: ABS

 

SIN(angle in radians)

Sine of the specified angle (*PI/180 to convert the angle from degrees to radians).

See also: ASIN, PI

 

SINH(any number)

Hyperbolic sine of the specified number.

See also: ASINH, PI

 

SLN( initial cost, salvage value, number of periods of useful life)

Depreciation of an asset for a specific period of time using the straight line balance method.

See also: DDB, SYD, VDB

 

SQRT(positive number)

Square root of the specified number.

See also: SUMSQ

 

STDEV(number list)

Standard deviation of a population based on a sample of supplied values.

See also: STDEVP, VAR, VARP

 

STDEVP(number list)

Standard deviation of a population based on an entire population of values.

See also: STDEV, VAR, VARP

 

SUBSTITUTE(text, old text, new text [, instance])

Replaces a specified text string (old text), occurring within another text string (text), with a new text string. Instance specifies the occurrence of old text to replace (omission replaces every instance); e.g.: SUBSTITUTE ("Shipment 45, Bin 45", "45", "52", 2) returns Shipment 45, Bin 52.

See also: REPLACE, TRIM

 

SUM(number list)

Sum of the supplied numbers.

See also: AVERAGE, COUNT, COUNTA, PRODUCT, SUMSQ

 

SUMIF(range, criteria, [sum_range])

Sum of the specified cells that match the given criteria (e.g. ">15"). If sum_range is specified then cells in this range are summed if corresponding cells in range meet the given criteria. If sum_range is omitted then cells in the range that meet the criteria are summed.

See also: AVERAGE, COUNT, COUNTA, COUNTIF, PRODUCT, SUM

 

SUMSQ(number list)

Squares each of the supplied numbers and returns the sum of the squares.

See also: SUM

 

SYD(initial cost, salvage value, number of periods in useful life, period)

Depreciation of an asset for a specified period using the sum-of-years method. Period refers to the period for which to calculate the depreciation.

See also: DDB, SLN, VDB

 

 

Copyright © 2000-2016 StatsDirect Limited, all rights reserved. Download a free trial here.