|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.