Big Blue Disk 43/bbd43.zip/PCFUNCTS.TXT

Text File | 1990-03-19 | 17KB | 386 lines

|A╔═══════════════╗══════════════════════════════════════════════╔═══════════════╗
|A║ |6Personal Calc|A ║════════════ ^1Function Documentation |A══════════║ |6Personal Calc|A ║
|A╚═══════════════╝══════════════════════════════════════════════╚═══════════════╝
^Cby
^CDoug Harrison

 Function reference
 ==================
 PC supports multiple arguments for the statistical functions; the term "expr
 list" means that you may use up to 20 values, cell references, and range
 references as function arguments.  Although some functions, such as NA(),
 require no arguments, the parentheses are required.

 Arithmetic
 ----------
 DIV(numerator, denominator), MOD(numerator, denominator)
 These are division and modulus functions that satisfy the equation below (for
 both real and integer values):

 x = DIV(x,y)*y+MOD(x,y)

 ABS(expr)
 Returns the absolute value of expr.

 Power
 -----
 LN(expr)                           natural logarithm (base e)
 LOG(expr)                          log base 10
 EXP(expr)                          e raised to a power
 POW(expr1,expr2)                   expr1 raised to expr2 power
 SQR(expr)                          square
 SQRT(expr)                         square root

 Trig
 ----
 All the trig functions (except RAD) expect angles to be specified in radians,
  and the inverse trig functions return a radian value.

 SIN, COS, TAN(angle)               sine, cosine, tangent
 ASIN, ACOS, ATAN(expr)             inverse functions
 ATAN2(x-expr,y-expr)               four-quadrant arctangent
 RAD(angle in degrees)              returns angle in radians
 DEG(angle in radians)              returns angle in degrees
 PI()                               returns pi

 Logical
 -------
 =, <>, <, <=, >, >=
 Logical operators return 1 to indicate TRUE and 0 to indicate FALSE.  For
  example, the value of 2 <> 3 is 1, while 2 > 3 evaluates to 0.

 IF(condition, action, alternate action)
 The three IF parameters may be any valid expression, including nested IFs.  IF
 returns the value of "action" when condition evaluates to non-zero, and it
 returns "alternate action" when condition evaluates to zero.

 AND(expr1, expr2, expr3, ...)
 Returns 1 if all parameters are non-zero, and 0 if any evaluate to zero.  Both
 AND and OR require at least two parameters.

 OR(expr1, expr2, expr3, ...)
 Returns 1 if any parameter is non-zero, and 0 only if all evaluate to zero.
 Both AND and OR require at least two parameters.

 NOT(expr)
 Returns 1 if expr evaluates to zero, and 0 if expr evaluates to non-zero.

 TRUE()
 Returns 1.

 FALSE()
 Returns 0.

 Statistical
 -----------
 SUM(expr list)
 Computes sum of all values within expr list.  Empty cells and labels are
 considered to have value 0.

 PROD(expr list)
 Computes product of all values within expr list.  Empty cells and labels are
 considered to equal 1.  Should the range contain no values, PROD returns 0.

 MEAN(expr list)
 Computes the mean or average of all values within expr list.  Empty cells and
 labels aren't considered.

 MEDIAN(expr list)
 Computes the median of all values within expr list, ignoring labels and empty
 cells.  The median is defined for sets of values containing three or more
 members.  Median works by creating a temporary array which it then sorts; each
 value requires eight bytes in this temporary array.  Thus, to compute the
 median of 1,000 values, you must have 8,000 bytes of free RAM as indicated
 under Statistics.  Note:  this RAM is allocated and returned to the system as
 MEDIAN is computed; although calculating the median requires some free memory,
 the MEDIAN function itself requires no more RAM than any other function.

 VAR(expr list)
 Computes the sample variance for expr list, ignoring empty cells and labels.

 VARP(expr list)
 Computes the population variance for expr list, ignoring empty cells and
 labels.

 SDEV(expr list)
 Computes the sample standard deviation for expr list, ignoring empty cells and
 labels.

 SDEVP(expr list)
 Computes the population standard deviation for expr list, ignoring empty cells
 and labels.

 SERR(expr list)
 Computes the standard deviation of the mean (the standard error) for expr
 list, ignoring empty cells and labels.

 MAX, MIN(expr list)
 Return the maximum and minimum values within the expr list.

 COUNT(expr list)
 Returns the number of cells containing values within expr list.

 RAND(lower bound, upper bound)
 Returns a random number between the given lower and upper bounds.
 Note: There is no restriction on the difference between the bounds, except
 that the upper bound must be greater than the lower.  Keep in mind that PC's
 RAND function has a range of 32,767.  This means that there are a maximum of
 32,767 discrete values within any range, whether it be 1E-20 to 1.0001E-20 or
 1 to 1E30.  Actually, due to limitations of binary floating point
 representation, there may be fewer discrete values within a given range.

 Financial
 ---------
 PC follows a convention that involves breaking down monetary values into cash
 inflows (like income), which are expressed as positive numbers, and cash
 outflows (like expense), expressed as negative numbers.  For example, the PMT
 function will normally return a negative value, as you are paying out funds.

 The "type" argument appears in all the financial functions, and if equal to 1,
 it indicates payments occur at the ends of periods (ordinary annuities).  If
 type equals 0, payments are assumed to occur at the beginning of periods
 (annuities due).  Note that for some combinations of arguments, namely those
 implying a simple or compound interest calculation, the type field will be
 meaningless; nevertheless, it is required as a place-holder.

 The number of compounding periods should be whatever is most appropriate; for
 example, you would use 365*n for daily compounding over n years.  The rate
 arguments refer to the interest rate per compounding period, so for monthly
 compounding over 1 year at 12% interest, rate would equal 12%/12, or 1%, while
 # periods would equal 12.

 PV(rate, # periods, payment, future value, type)
 Computes present value.

 FV(rate, # periods, payment, present value, type)
 Computes future value.

 NPER(rate, payment, present value, future value, type)
 Calculates number of periods.

 PMT(rate, # periods, present value, future value, type)
 Calculates payment.

 RATE(#periods, payment, present value, future value, type, guess)
 Computes interest rate.  RATE uses an iterative method (Newton's) to find
 rate; it requires a "guess value" to seed the process, and you should try
 using a value less than what you expect RATE to return.  (Good starting points
 for guess are values like 0.01, 0.001, etc.) RATE returns an error if it can't
 find a reasonably accurate solution within 20 iterations.

 The financial functions are based on the following equation, and RATE
 successfully returns a value if it finds an interest rate that satisfies the
 following equation to an accuracy of 1E-8:

 0 = PV+(1+(i%*S)/100)*PMT*USPV+FV*SPPV.

 S is the payment mode (0 for end mode, 1 for begin mode).  Note this is the
 opposite of what the functions expect as an argument (the value 1 seemed more
 logical than 0 to indicate a date later in the period).
 i% = periodic interest rate
 n = number of compounding periods
 SPPV = (1+i%/100)^-n
 USPV = (1-(1+i%/100)^-n)/(i%/100)

 Lookup
 ------
 INDEX(row index, column index, range)
 INDEX returns the value of a cell within range, located by row index and
 column index, which must be numbers greater than or equal to one.  These
 numbers are offsets into the range, with the upper-left corner of the range
 having 1,1 for its indices.  For example, INDEX(1,1,A1:B5) returns the value
 of A1, while INDEX(3,2,A1:B5) returns the value of B3 (row 3 and column 2
 within the range A1:B5).

 VLOOKUP(lookup value, column index, range)
 VLOOKUP searches down the leftmost column (which should be sorted in ascending
 order) of the indicated range to find the greatest value which is less than or
 equal to the lookup value; it uses this row number as a row index, along with
 the column index supplied in the function, then returns the value of the cell
 pointed to by these two indices.  VLOOKUP is like INDEX, except it determines
 the row index based on the lookup value, while you still provide the column
 index and the range.   An error occurs if no match is found or the index
 references a column outside the range.

 HLOOKUP(lookup value, row index, range)
 HLOOKUP searches across the top row (which should be sorted in ascending
 order) of the indicated range to find the greatest value which is less than or
 equal to the lookup value; it uses this column number as a column index, along
 with the row index supplied in the function, then returns the value of the
 cell pointed to by these two indices.  HLOOKUP is like INDEX, except it
 determines the column index based on the lookup value, while you still provide
 the row index and the range.   An error occurs if no match is found or the
 index references a row outside the range.

 CHOOSE(index, expr 1, expr 2, ...)
 CHOOSE allows you to store a list of values in a single cell, and it retrieves
 the value at the position indicated by index.  For example, an index of 2
 returns the value of the second expression in the list.

 LOOKUP(lookup value, lookup range, results range)
 LOOKUP searches the lookup range for the greatest value less than or equal to
 the lookup value.  It notes the row and column indices of this value within
 the lookup range and returns the value in the results range having the same
 indices.  The values within the lookup range should be arranged in ascending
 order for LOOKUP to function properly, and the two ranges must contain the
 same number of cells.  If you specify a two-dimensional range for LOOKUP, the
 range is scanned by rows.

 MATCH(lookup value, range, type)
 MATCH returns the position of the lookup value within the indicated range.
 The type argument determines the search criteria.  When type is 1, MATCH
 searches for the greatest value less than or equal to the lookup value.  When
 type is 0, MATCH searches for an exact match.  Finally, when type is -1, MATCH
 looks for the smallest value greater than or equal to the lookup value.  For
 this function to operate properly, when type is 0 or 1, the values must be
 arranged in ascending order.  When type is -1, they must be in descending
 order.  If you specify a two-dimensional range for MATCH, the range is scanned
 by rows.

 Note:  LOOKUP, MATCH, HLOOKUP, and VLOOKUP all search a range to perform their
 function, and all depend on the range being sorted a certain way, as described
 above.  A logical question concerning the search criteria is, "how do they
 deal with a run of equal values, if this value ultimately matches the search?"
 In this case, they all proceed to the last value within the run of equal
 values.

 Date/Time
 ---------
 DATE(year, month, day)
 DATE computes the proper serial date value from the supplied arguments.  Year
 should be specified as the number of years from 1900, month should be a number
 between 1 and 12, and day should be between 1 and the last day of month.
 Except for the year, these rules are not immutable; for example, DATE(89,2,29)
 returns a serial date value corresponding to March 1, 1989, since 1989 isn't a
 leap year.  DATE returns a value with a fractional portion equal to zero; in
 other words, all DATE values are set at 12 Midnight of the indicated day.

 YEAR(expr)
 Returns the year (1900-26541) of a given serial date value.

 MONTH(expr)
 Returns the month (1-12) of the serial date value.

 DAY(expr)
 Returns the day of the month of the serial date value.

 WEEKDAY(expr)
 Returns the day of the week of the given serial date value, where Sunday is 1,
 Monday is 2, etc.

 ISLEAP(expr)
 Returns one if the value is a leap year and zero otherwise.  In contrast to
 DATE, here the value should be specified as years A.D.

 DAYSMONTH(month, year)
 Returns the number of days in a given month within a given year.  The year
 should be specified as years A.D.

 TIME(hours, minutes, seconds)
 Returns a serial date value with integer portion 0 representing the indicated
 time.  Hours should be expressed using the military convention, (the 0-23
 scale).  Minutes and seconds range from 0-59.  As with the DATE function,
 these rules are flexible.

 HOUR(expr)
 Returns the hour of a serial date value in military notation (0-23).

 MINUTE(expr)
 Returns the minute (0-59) of a serial date value.

 SECOND(expr)
 Returns the seconds (0-59) of a serial date value.

 NOW()
 Returns a serial date value representing the current date and time, as read
 from the PC's clock.  One date format, "m/d/yy, h:ii", is designed to display
 this function's result.

 Date/Time Arithmetic
 --------------------
 PC provides a full set of date and time functions.  Dates are represented as
 integers equalling the number of days from PC's "day 0," while time is
 expressed as a fraction of a day (24 hours).  The smallest valid date value is
 0, corresponding to 12 Midnight, Dec.  30, 1899, while the largest supported
 value is for all intents and purposes, unlimited.  Since dates are represented
 by integers and time by fractions, dates and times may be combined into a real
 number representing both.  For example, 12 Noon on May 3, 1989 would have
 value 32631.5.

 Since date and time are numbers, they may be operated on just as any other
 number.  It is up to you to determine what operations make sense; for example,
 adding one date to another is not usually meaningful, but PC will allow it.
 However, PC can easily calculate the number of days between two dates by using
 something like DATE(89,5,3)-DATE(89,3,2).  Adding integers to date values may
 also be useful in creating a series of dates, but you may find it more
 convenient to use PC's Date Fill commands.

 You may also find a use for combined date/time numbers.  For example, you
 could create a formula DATE(89,5,5)+TIME(18,0,0), and the result would be a
 serial date value representing 6 PM, May 5, 1989.  Adding 0.5 (12 hours) to
 this would result in a number corresponding to 6 AM, May 6, 1989.

 Cell
 ----
 ROW, COLUMN(cell ref)
 Return the row and column number of the argument.

 Range
 -----
 ROWS, COLUMNS(range)
 Return the number of rows or columns in the range supplied.

 Miscellaneous
 -------------
 ISERR(expr list)
 ISERR() returns 1 if any expression within the list generate an error and 0
 otherwise.  It could be used in a conditional such as:

 =IF(ISERR(A1/A2),A3,A4).

 ISERR() does not check for the NA error condition.

 ISNA(expr list)
 ISNA() is similar to ISERR(), except that it tests specifically for the NA
 error status.  These two functions allow PC's extended error reporting to
 coexist peacefully with Lotus 123 files; 123 supports two error conditions,
 ERR and NA.

 ISEMPTY(list of cells or ranges)
 ISEMPTY determines if any cell or range out of a supplied list contains a
 value; it returns 1 if no values are encountered and 0 if a value is detected.

 ERROR()
 ERROR() is a function that forces the ERR error condition.  For example, it
 might be used as in the following way:

 =IF(ISEMPTY(A1),ERROR(),A2).

 NA()
 Similar to ERROR(), NA() forces the NA error status.

 ROUND, TRUNC(expr, place)
 These functions take an expression and either round or truncate it to the
 value specified by "place."  Place may be positive, negative or zero.
 Consider the number 126.556, supplied to ROUND and TRUNC, for the following
 values of place:

 expr = 126.556

 Place         ROUND          TRUNC
   2            126.56         126.55
   1            126.6          126.5
   0            127            126
  -1            130            120
  -2            100            100
  -3              0              0

 As you can see, you can round or truncate within the whole number portion of
 the expression, as well as within the fractional portion.

 INT, FRAC(expr)
 Return the integer and fractional parts of a number, such that INT(X)+FRAC(X)
 = X.  Negative numbers are handled as follows:  INT(-3.4) = -3, FRAC(-3.4) = -
 0.4.