0% found this document useful (0 votes)
49 views21 pagesWoG - Built in Functions
WoG - Built in Functions
Uploaded by
qrzdx?Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
0% found this document useful (0 votes)
49 views21 pagesWoG - Built in Functions
WoG - Built in Functions
Uploaded by
qrzdx?Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
/ 21
Built-In
Functions 3 1 SYNTAX OF FUNCTIONS
‘The MBA’s Built-In Functions are formulas that have already been
constructed; they need only the right type of data in order to be used.
In the following description of the Built-In Functions, the terms
Formula, Range, and Rate have been used to represent the different
kinds of data the user can enter for each funetion.
Formula can be
© asingle value
© acell address
¢ a Built-In Function
a formula (which may contain any of the 3 preceding kinds
of data)
Range can be
‘one or more cell addresses, separated by commas
‘one or more ranges of cell addresses
‘* a combination of addresses and ranges
Rate can be
‘a value, with or without a pereent sign
‘a cell address containing a value or a formula that will
yield a value
a formula that will yield a value
102 Formula:
@SQRT (1005)
@SQRT (A2)
@SQRT (@SUM(A1...D6))
e @SQRT (@SQRT(@SUMIAL...D6)))
Range:
@AVG(A1,B3,C7,M9)
@AVG(P1...P26) or @AVG(A1...C1,F1L..QU)
@AVG(B4,A1...A17,M22)
Rate:
@NPV(.87,A1...A12)
@NPV(B1AL..A12)
@NPV(@SUM(BL..£1),A1..A12)
103, 3.2 THE MBA’S BUILT-IN FUNCTIONS
@ABS (formula)
@ABS calculates the Absolute value of the result of the formula—
that is, it returns the numeric portion of the value but ignores the plus
or minus sign.
@ACS (formuta)
@ACS calculates the Arceosine of the formula, which must be a real
number between 1 and -1. This and the other trigonometric functions
accept values and return results in radians (not degrees).
@AND (range)
@AND accepts a range or ranges of cell addresses, separated by
commas. Each of the individual cells contains a boolean value. The
function links all these cell contents into a single boolean value. If all
the individual data items are TRUE, @AND returns TRUE in the
cell; if not, it returns FALSE.
@ASN (formula)
@ASN calculates the Arcsine of the formula, which must be a real
number between 1 and -1, and returns the result in radians.
@ATN (formula)
@ATN calculates the Aretangent of the formula, which can be any
real number, and returns the result in radians.
@ANG (range)
@AVG calculates the Average (mean) of all the numeric values in a
range. It will ignore blank cells and text cells.
@CHZ (formula, range)
@CHZ searches from the beginning of the specified range to the
position indicated by the value of ; it then Chooses and
returns the value located at that position. ‘The value of
must be a positive integer.
104 SS
Absolute Value
@ABSI10) = 10
(@ABS(-299.65) = 299.65
‘Arccosine
@ACS(.25) = -1.828
@ACS(-.95) =-0.818
And
Workspace contents include:
Au2 — Bt:3 ors
‘2: sA1>1 BZ sBIDAT C2: (AI+B1)>C1_ DZ @SUM(AI..C1}<0
@AND(A2..C2) returns TRUE
@AND(A2..02)retums FALSE
@AND(A2,C2) returns TRUE
Aresine
@ASN(4) = 0.4115
@ASN(-.75) = -0.848,
Arctangent
@ATN(S) = 1.249
@ATN(-360) = -1.568
Average
Workspace contents include
AS: 1674 BS:2 C5: 34870 —_—D5:-1.705
@AVG(AS...05) = 9136.1
@AVG(B5,05) = 0.1475
Choose
Cell range A1...04 includes:
12 27 670 34500
200 04 761 233
a7 900 45 2
001 5 2006717
@CHZ(2A1..04) returns 27
@CHZIS.A...C2) returns 04
@CHZ(10,A2..04) returns 5
105 Perform @CPYsin what
106
@CNT (range)
@CNT Counts the number of numeric entries in a range.
@COS (formula)
@COS calculates the Cosine of the formula.
@CPY (folder, document, markernamet...markername2)
@CPY specifies a range of cells in an MBA Folder and Document so
that those eells can be Copied into the workspace. The range must be
indicated by Marker names at the beginning and end; these Markers
must have already been saved as part of the Document to be copied.
You may specify a Box name instead of a Marker name for the range
of cell to be Copied.
When first entered, @CPY displays only a 0 in the current cell. To
execute the @CPY function, you must type the Copy option of the
Storage command:
sc
then answer the prompt
rent cell _n Rows/Columns (range of cells)
by indicating that the @CPY functions in the current cell, a specified
number of Rows or Columns, or a range of cells are to be executed.
When the cells containing @CPY have been indicated, the program
will find the specified Folder (if it is on-line), locate the cell range in
the specified Document, and copy them into the workspace starting at
the cell containing @CPY. The @CPY function remains in the cell,
and can be re-executed to update the cell contents whenever the source
Document has been modified.
Note: The /SC command can copy only up to 502 characters from
each cell. Count
Cell range A10...£13 contains
‘September 32 454648
October 4 420 «6183
November 81 87) 92
December 101 104110121
e @CNTIAIO.£19) returns 16
Cosine
@COS\1) = 0.5403,
@COSi.01) = 1
COPYING WITH @cPY
voLUME2
WORKSPACE2
BEFORE TYPING ‘SC
‘COPY FORMULA:
@CPY(VOLUME2, WORKSPACE? START. . FINISH)
voLuMe2
WORKSPACEZ
WORKSPACE
PROJECTIONS, 19821084
AFTER TYPING SC
107 @ERROR
@ERROR produces an ERROR value in the cell where it is used; this
ERROR value is propagated through later calculations. @ERROR.
may be used as a possible value to be returned by the @IF and @IFV
functions. The value ERROR is also returned by most functions when
the wrong types of data have been entered.
@EXP (formula)
@EXP calculates e (approximately 2.71828) to the power .
@FALSE
@FALSE produces a logical (boolean) FALSE value. This value can
be put in a single cell, or used as part of the formula for an @IF
funetion.
@IF (fH, 12, 13)
@IF evaluates the boolean formula fl. If fl is TRUE, @IF returns
£2 (which may be any type of formula); if fl is FALSE, it returns £3.
@IFV (11, 12, 13, 14, 15)
@IFV compares the values contained
£2, with the following results:
If f1 <2, @IFV returns the value of (or the result of calculating) £3.
If f1 = £2, the function returns the value of £4.
Uff1 > £2, it returns the value of £5.
or yielded by formulas f1 and
@INT (formula)
@INT yields the Integer portion of the result of the formula—that is,
the portion of a number to the left of the decimal point.
108 LS
Error
Cell range A6...C7 contains the following data and formulas:
‘Ag: 100 B8:-200 8:50
AT: ERROR E+AG*BE>C8 C7: (AG+C6)-BE
@IF(B7,C7,A7) returns ERROR
+A1/0 returns ERROR
@SQRT(B6) retums ERROR (with message on prompt line)
Exponent
@EXP(1) = 2.7183
(@EXP(.05) = 1.0513,
@EXP(-3) = 0.0498
False
300<1 returns FALSE
(A242)< 0 returns FALSE
@IF(2>3,5>4,0>1) returns FALSE
Cells A20...822 contain the following numbers and formulas:
‘A20: 1000 1820: 1200 C20: 1450
‘A21: (A20+B20)>C20__ B21: (A20+B20)-C20 C21: 0
@IF(A21,821,C21) returns 750
@IF(A21,621,821) returns 0
jalue
Cells A22...622 contain the following values:
250 400 600 900 400
@IFV(A22,822,022,022,£22) returns 600
@IFV(B22,£22,022,A22,622) returns 250
@IFV(B22,422,022,£22,022) returns 900
@INT(.33) = 0
109 R (range)
G@IRR calculates the Internal Rate of Return on an investment
represented by the values in the range. The first value in the range
‘must be the initial capital outlay; it will usually have a negative sign,
but does not need to. The remaining values are the periodic cash flows,
positive or negative, resulting from the investment,
Internal Rate of Return is the discount rate at which the Net Present
Value of an investment is zero; that is, the sum of the discounted
periodic cash flows is exactly equal to the initial capital outlay. IRR is
therefore the minimum rate of return at which a specific investment
is worthwhile. @IRR caleulates this rate by iteration, substituting
values until NPV is zero, The result is expressed as a decimal; that is,
15 percent would appear as .15. A negative total cash flow yields a
‘meaningless IRR; the MBA will return ERROR in the cell containing
the function.
IRR uses the following formula:
cr, cl
cre 9 . Oh ,, OF
(a) roy or
‘Because it assumes different interest rates for known investments and
possible reinvestment of profits, @MIRR will yield a more accurate
and realistie Rate of Return than @IRR for many expenditures.
@ISERR (formula)
If any cell referred to in the formula contains the value ERROR, then
@ISERR returns the value TRUE; if not, the function returns
FALSE.
@ISNA (formula)
If any cell specified in the formula contains the value N/A (Not
Available), then @ISNA returns the value TRUE; if not, it returns
FALSE.
@LNE (formula)
@LNE calculates the Logarithm to base e of the formula. This
funetion can be used with @EXP to return natural-number results of
logarithmic caleulations. Car
Internal Rate of Return
Cells A2...F2 contain the following periodic cash flows:
65000 15000 25000 40000-35000 25000
@IRR(A2...F2) = 0.28944, or 29 percent
Is..Error
Celis B25...F25 contain the following numbers and formulas:
100 200 400750 © @SQAT(-1)
@ISERR(@SUM(B25..F25)) returns TRUE
@ISERR(@SUM(B26..£25)) returns FALSE
Is..Not Available
Celis 30...630 contain the following values:
20 90 40 «N/A
@ISNA(@AVG(D30...630)) returns TRUE
@ISNA(@AVG(030..F30)) returns FALSE
Log Base e
@LNE(1) =0
@LNE(10) = 2.9026
@LNE(45012) = 10.714
ar @LOK (formula, range)
@LOK Looks up the specified formula in the specified range of a
table—which must be a single row or column—and returns the
corresponding value from the next row or eolumn of the table.
The table used for the @LOK funetion must be set up in 2 rows or
columns; the left column or the upper row must contain the reference
values—those with which will be compared—while the
right column or bottom row contains the values to be returned. The
reference values must be arranged in ascending order, from left to
rright or from top to bottom. @LOK finds the first reference value
greater than and returns the result corresponding to the
preceding reference value.
@MAX (range)
@MAX returns the Maximum value in the specified range.
@MIN (range)
@MIN returns the Minimum value in the specified range.
@MIRR (rate1, rate2, range)
@MIRR calculates the Modified Internal Rate of Return on the
investment described by the values in the range. The first value in the
range must be the initial eapital outlay, which has a negative sign
(although the negative sign is not necessary in order for the funetion to
return a result). Rate! is the “safe” rate returned by current or
feasible investments; Rate? is the “risk” rate at which the projected
future cash flows can presumably be reinvested. These rates are
expressed as decimals; for example, 14.5 percent would be .145.
@MIRR uses the following formul:
MIRR = 100% [(_NEVoe 1/2), where NFVix = NPVs * (1ri)"
“NPV
Because the provision for 2 discount rates more accurately reflects
real-world finance under conditions of inflation, @MIRR ean
sometimes produce more realistic figures than @IRR. ol
Look Up
Cell range D1...12 contains the following labels and numbers:
Bracket 20000 25000 30000 © 35000-40000
Tax Rate 18 22 26 31 36
@LOK(28750,E1...1) returns .22
@LOK(34900,E1...1) returns 26
Maximum
For the cell range M12..P15:
23 2 25 24
a 22 26 23,
22 20 21 24
25 a 22 23,
@MAX(M12..P15) = 26
Minimum
For the cell range described under Maximum (above),
@MIN(M12...P15) = 20
Modified Internal Rate of Return
Cell range J3...05 contains the following labels and numbers:
Safe Rate .09
Risk Rate .15
Cash Flows -78000 24000 30000 40000-35000
@MIRR(K3,K4,KS...05) = 0.191442, oF 19 percent
113, @NA produces an N/A (Not Available) value wherever it is used.
‘This value displays as N/A on the screen, and produces further N/A
values in any calculation in which it is used. N/A is also displayed
when the @CHZ, @LOK, @MAX, and @MIN functions cannot find
the expected values.
@NOT (formula
If the boolean is FALSE, @NOT returns and displays
TRUE; otherwise, it returns FALSE.
@NPV (rate, range)
@NPV calculates the Net Present Value, at the specified discount
rate, of the investment described by the values in the range. The first
value in the range must be the initial capital outlay, and will have a
negative sign (although the negative sign is not necessary in order for
the function to return a result). The remaining values in the range are
the periodic cash flows, positive or negative, resulting from the
investment.
@NPY uses the following formula:
nev-cr« CF, CF 5. CR
a a) ay"
Net Present Value is the difference between the initial capital
outlay and the sum of the discounted future cash flows. A positive
NPV indicates a profitable investment; a negative NPV, an
unprofitable one.
@OR (range)
@OR accepts a range consisting of individual cell addresses
separated by commas. Each of the individual cells contains a
boolean value. The function links all these cell contents into a single
boolean value. If all the individual data items are FALSE, @OR
returns FALSE in the cell; if any of them is TRUE, it returns
TRUE.
"4 Ce
Not Available
For the cell range A6..C7:
12000 23000-34000
15 18 22
@LOK(9000,A6...C6) returns N/A
Not
‘The cell range D10...H10 contains the following numbers and formulas:
2500 9500 4900 +DI0>E10.+E10BS DE: +B5>C5_CB:+CS>DS DE: +DS>AS
@OR(AS..C6) returns FALSE
@OR(AG...D8) returns TRUE
1" er
@PI
@PI returns the value of Pi (8.14159).
@SDV (range)
@SDV calculates the Standard Deviation of the non-blank values
contained in the range. The Standard Deviation is a measure of the
fluctuation of each number in the range around the mean of the
numbers in the range. It is the square root of the variance (see
@VAR).
@SIN (formula)
@SIN caleulates the Sine of the value contained in or yielded by the
formula.
@SQRT (formula)
@SQRT calculates the Square Root of the value contained in or
yielded by the formula,
@SUM (range)
@SUM calculates the Sum of all the valid numeric entries in the
specified range.
@TAN (formula)
@TAN calculates the Tangent of the value contained in or yielded
by the formula.
16 Sl
Pi
@Pi=3.1416
@PI*(25 A 2) = 19635
Standard Deviation
‘The cell range A40...H40 contains the following values:
3 36 92 35 32H
@SDV(AAO...H140) = 11.886
Sine
@SIN(10)
@SIN(100) = -0.50636
@SIN(.21) = .20846
-0.544021
Square Root
@SQRT(225) = 15
@SORT(2) = 1.41421
@SQRT(.05) = 0.2236
@SQRT(-1) = ERROR with a message on the prompt line
sum
Cell range D4...H5 contains the following numbers:
345 400-541 789376
198-602 «202917808
@SUM(D4...H5) = 1366
@SUM(D4...E5) = 341
(@SUM(D4...F4,H4,H5) = -228
Tangent
@TAN(1) = 1.5574
@TAN(50) = -0.272
@TAN(25) = 0.2553,
" @TRUE
@TRUE can be used to assign the logical value TRUE to a cell. The
value produced by @TRUE is the same as that generated by the
MBA to display the word TRUE in a cell in response to the logical
functions @AND, @OR, @IF, @NOT, @ISERR, and @ISNA.
@VAR (range)
@VAR calculates the Variance of the non-blank values contained
in the specified range. Variance is the sum of the squared
deviations of each value from the mean, divided by the number of
values in the range.
Other Functions
‘The logical functions @TRUE, @FALSE, @BRROR, and @NA
may be used as formulas in the functions @IF and @IFV; they may
be either control values (those tested for by the function) or values to
be returned by the formula.
‘The Graph functions @PLOT, @DATA, @TTL, @XLBL, and
@GRID are explained in section 4, “Graph Commands.”
‘The Printer Format functions @PRINTER, @PPAPER,
@PFORMAT, @PHEADING, @PSETUP, and @DATA are
described in section 2.12, “/P (Print).”
‘The Telecommunications functions @MODEM, @ASYN,
@PROTOCOL, @DATA, @DIAL, @WAIT, and @RECEIVE
are described in section 8, “Telecommunications.”
118 True
(B142)>0 returns TRUE (if B1 is not equal to 0)
5/16>6/23 returns TRUE
@IF(45>44,08<99,31>32) returns TRUE
e@ Variance
For the cell range E6..J8:
4646 at ag
441 47 4384
42 45 46 «41 40
47 40 430 44a
@VAR(E6...J9) = 5.9112
Sets
" 3 SUMMARY TABLE:
=a BUILT-IN FUNCTIONS
The follo
ing table summarizes some of the operating characteristics
of the MBA’s Built-In Functions: whether a function generates Error
or Not Available values in response to faulty or missing data; how it
responds to unacceptable cell data; and whether the function will
propagate Not Available and Error values when
referenced in
subsequent caleulations. A dash means that the function will accept
the indicated data.
Generates _Response to-cell data types
Function @ERROR @NAV Blank Number
@ABS (formula) x 55 =
@ACS (formuta) x - = =
@AND (range) x = Ignore Error
@ASN (formula) x - - -
@ATN (formula) x - - =
@AVG (range) x = Ignore —
@CHZ (formula, range)
formula x Xe -
range x X Error =
@ENT (range) = = Ignore =
@COS (formula) x - - -
@CPY (folder, = 7 = =
document, range)
@ERROR - - -
@EXP (formula) x - - -
@FALSE - - - -
QIF (11, 12, 13)
fl x — Error Error
2,18 x - = =
QIFV (11, 12,13, 4, 15)
tHt2 x - - -
19, 14, 15 x - - -
120
Boolean
Error
Error
Error
Error
Error
Error
Error
Error
Toxt
Error
Error
Error
Error
Error
lgnore
Error
Error
Error
Error
Error
Error
Error
Error
Propagates
NA, ERROR
x XK x KX
xx Generates Response to cell ata types Propagetes
Funetion @ERROR @NAV Blank Number Boolean Text NA.ERROR
INT (formula) x = = Error Error X
IRR (range) X = Error Error Emor «xX
@ISERR (formula) = -- — — =- =
@ISNA (formula) = - - - S
@LNE (formula) x ——— = Error Error, =X
@LOK (formula, range)
formula x x = - Error Error x
range x X Error — Error Error X
@MAX (range) x X Ignore — Enor Eror—-X
@MIN (range) x X Ignore — Error Error xX
QMIRA (rater, x aS = Error Error X
rate2, x 26 = Error Error X
range) xX = Emor Error Error =X
@NA - x = - = = -
@NOT (formula) x — Error Error — Error X
QNPY (rate, x aS = Error Error X
range) x = Etror = Error Error. —«X
@OR x — Ignore Error — Error Xx
@pr - - - - - - -
@SDV (range) x — lgnore — Error Error X
@SIN (formula) x - - _ Error Error x
@SQRT (formula) x - - = Error Error x
@SUM (range) x — Ignore — Ignore ignore =X
@TAN (formuta) x - - - Error Error x
@TRuE Se os - - - =
@VAR (range) x = Ignore — Error Error X
