AppleWorksGS Reference Alphabetical Directory of AppleWorksGS Functions ABS (number) Returns the absolute value of number. Examples: Abs(-1 ) returns 1. Abs(1) returns 1. Abs(0) returns 0. Abs(A1 ) returns the absolute value of the number in spreadsheet cell A1. Abs([fieldname]) returns the absolute value of the number in database field specified. ACOS(number) Returns in radians the arccosine of number. Examples: Acos(-0.5) returns 2.094 (2pi/3 radians). Acos(A1) returns the arccosine of the value in spreadsheet cell A1. Acos([fieldname]) returns the arccosine of the value in database field specified. ALERT(string) Returns an alert box with string displayed in box. Examples: The formula If(lsError(A1), A1ert("ERROR IN CELL A1"), NULL) returns an alert box with the message "ERROR IN CELL A 1 " if an error is found in spreadsheet cell A1. AND(logical list) Examples: And(1+1=2, 2+2=4) returns true. And(A1=B1, C1=D1) returns true if the value in cell A1 equals the value in B1 and the value in cell C1 equals the value in cell D1. ASC(string) Returns the ASCII value of the first character in string. Examples: Asc("A") returns 65, the ASCII value of the character "A". Asc("alphabet") returns 97, the ASCII value of the character "a". Asc(A1 ) returns the ASCII code of the first character in spreadsheet cell A. ASIN(number) Returns in radians the arcsine of number. Examples: Asin(-0.5) returns -0.524 (-pi/6 radians). Asin(A1 ) returns the arcsine of the value in spreadsheet cell A1. Asin(name1) returns the arcsine of the value in database field ATAN(number) Returns in radians the arctangent of number. Examples: Atan(1) returns 0.785 (7pi/4 radians). Atan(A1) returns the arctangent of the value in spreadsheet cell A1. Atan([fieldname]) returns the arctangent of the value in specified database field. AVG(numeric list) Returns the average of numeric list. Examples: Avg(1, 3, 5) returns 3. Avg(A1..A10) returns the average of values in cells A1 through A10 (ignores empty cells in a range). Avg([field1], [field2], ...) returns the average of the values in database fields specified. CHOOSE(number, numeric/string list) Returns the numeric value in numeric/string list that is in the position specified by the first argument. Examples: Choose(2, 5, 6, 8,) returns the value 6 (the second value on the list). Choose(3, A1..A10) returns the third value in the list of spreadsheet cells from A1 through A10 (ignores empty cells in ranges). Choose(3, [field1], [field2],...) returns the third value of the list of values found in the specified database fields. CHR(number) Returns the string value corresponding to the ASCII value specified by number. Examples: Chr(65) returns "A", the character represented by ASCII value 65. Chr(97) returns "a", the character represented by ASCII value 97. Chr(A1 ) returns the character represented by the ASCII code of the value in spreadsheet cell A1. Chr([fieldname]) returns the character represented by the ASCII code in the database field specified. CONCAT(string list) Returns a single string produced by concatenating string list. Examples: Concat("see", " the", " cat") returns the single string "see the cat". (Whereas Concat("see", "the", "cat") returns the single string "seethecat" . ) Concat("hello", " world") returns the single string "hello world". Concat(AI, A2, A3) returns a single string by combining the values in spreadsheet cell A1, A2, and A3. Concat([field1], [field2], [field3]) returns a single string by combining the values in the database fields specified. COS(number) Returns the cosine of number. The function expects number to be expressed in radians. Examples: Cos(1.041) returns 0.5. Cos(AI ) returns the cosine of the value in spreadsheet cell A1 . Cos([fieldname]) returns the cosine of the value in database field specified. COUNT(list of cells or fields) Returns the number of cells or fields containing values. Examples: Count(A1..A10) returns the number of spreadsheet cells in the range A1 through A10 that contain values. Count([field1], [field2], ...) returns the number of database fields that contain values among the fields listed. DATE(number) Returns the string date equivalent of number. The value 1 returns the date "Jan.1, 1900", the value 2 returns "Jan. 2, 1900", and so forth. Examples: Date(10894) returns the date "Oct. 29, 1929". Date(32873) returns the date "Jan. 1, 1990". Date(A1) returns the date equivalent of the numeric value in spreadsheet cell A1. Date([fieldname]) returns the date equivalent of the numeric value in the database field specified. DAY(string) Returns a number representing the day portion of the date represented by string. Examples: Day("Jan. 24, 1988") returns 24. Day("Jan 1, 1989") returns 1. Day(A1) returns the day part of the date in spreadsheet cell A1. Day([fieldname]) returns the day part of the date value in the field specified. DEGREES(number) Returns the value of number in degrees. The argument number should be entered in radians. Example: Degrees(Pi) returns the value 180, the number of degrees equivalent to pi radians. Degrees(3.14) returns the value 179.9087476, the number of degrees equivalent to 3.14 radians. ERROR Returns the value ERROR in red. Example: Error returns value ERROR. EXP(number) Returns the value e raised to the power of number. The symbol e represents the mathematical constant 2.7182818..., the base of the natural logarithm. The EXP function is the inverse of the natural logarithm function ln(). Examples: Exp(-1) returns .368. Exp(0) returns 1. Exp (1) returns 2.718. Exp(A1) returns value of e, raised to the numeric value found in spreadsheet cell A1. Exp([field]) returns value of e, raised to the numeric value found in the specified database field . FALSE Returns the value 0. False takes no arguments. Example: False returns value False (0). FRAC(number) Returns the fractional part of number. Examples: Frac(3.5) returns 0.5. Frac(2.75) returns 0.75. Frac(A1) returns the fractional part of the value in spreadsheet cell A1. Frac([fieldname]) returns the fractional part of the value in the database field specified. FV(payment, rate, term) Calculates the future value of an investment. Payment is the payment, the amount of the periodic investment; the payment should be entered as a positive value, and a leading dollar sign is optional. Rate is the interest rate; the rate may be entered as a percent value (such as 10%) or a decimal value (such as 0.10). Term is the number of periods for the term of the investment. Example: FV(5000, 8%, 20) returns the future value of an investment of $5000 at an annual rate of 8% after 20 years. HLOOKUP(value, range, offset) Looks up value in a table contained in the spreadsheet range. Finds the largest value in the first row of the range which is less than or equal to value, and then returns the value found offset rows below. (Assumes that the first row of the range is sorted in increasing order.) Example: HLookup(2, A1..D4,3) returns the value found using this method: ¥ finds largest value in row A1 to D1 which is less than or equal to 2 ¥ finds cell 3 rows down from top of range (the offset value), and returns the value in that cell. IF(Logical expression, expression1, expression2) If logical expression is true, this function returns expression1; otherwise, it returns expression2. Examples: If(A1<90, "B", "A") returns the value "B" if the value in cell A1 is less than 90; otherwise, it returns the value "A." If(A1=B1, 10.5, 12.75) returns the value 10.5 if the value in cell A1 is equal to the value in cell B1; otherwise, it returns the value 12.75. INF Returns the value of infinity. INT(number) Returns the integer part of number. Examples: Int(3.5) returns 3. Int(2.75) returns 2. Int(A1) returns the integer part of the value in spreadsheet cell A1. Int([fieldname]) returns the integer part of the value in the database field specified. IRR(guess, numeric range) Calculates the internal rate of return on a series of irregular periodic cash flows (numeric range). The internal rate of return is the rate that causes the net present value of the cash inflows from an investment to equal the cost of the investment. The guess parameter is your best guess of the approximate rate of return; the IRR function uses this guess as the starting point for its calculations. You can enter the guess parameter as decimal value or as a percentage; the values 10% or 0.10 would be treated as the same value. Use IRR to compare the attractiveness of investment opportunities. Examples: IRR(0.10, A1..A10) returns the internal rate of return on an investment that pays the periodic values stored in spreadsheet cells A1 through A10. The guess is .1, meaning that you think the rate is approximately 10%. ISBLANK(expression) Returns numeric value 1 (true) if expression is a null string. Example: IsBlank(A1) returns value 1 if cell A1 contains no value. ISEMPTY(expression) Returns true if the specified cell, field, or expression is empty. Examples: IsEmpty(A1) returns true if the cell A1 contains no value. IsEmpty([field1]) returns true if field1 contains no value. ISERROR(expression) Returns the value 1 (true) if expression evaluates to ERROR. Example: IsError(SQRT(A1)) returns 1 (true) if the value in cell A1 is less than 0, since attempting to evaluate the square root of a negative value produces an error. ISNA(expresslon) Returns the value 1 (true) if the parameter value is the special N/A Example: IsNA(A3) returns 1 if the cell A3 contains the specified value A3 ISNUMBER(expression) Returns true if expression (a cell, field, or expression) is a number. Examples: IsNumber(A1) returns true if the cell A1 contains a number value. IsNumber([field1]) returns true if the field named field1 contains a number. ISSTRING(expression) Returns true if expression (a cell, field, or expression) is a string. Examples: IsString(A1) returns true if the cell A1 contains a string value. IsString([field1]) returns true if the field named field1 contains a string. LEN(string) Returns a number representing the length of string. Examples: Len("Cat") returns 3. Len("Hello") returns 5. Len(A1) returns the length of the string value in spreadsheet cell A1. Len([fieldname]) returns the length of the string value in database field LN(number) Returns the natural logarithm of number (which must be a positive value). The Ln function is the inverse of the Exp function. Examples: Ln(1) returns numeric value 0. Ln(100) returns numeric value 4.605. Ln(0) returns INF. Ln(A1) returns natural log of numeric value found in spreadsheet cell A1. Ln([field]) returns natural log of numeric value found in database field. LOG(number) Returns the base 10 logarithm of number. Examples: Log(1) returns numeric value 0. Log(100) returns numeric value 2. Log(0) returns INF. Log(A 1) returns common log of numeric value found in spreadsheet cell A1. Log([field1) returns common log of numeric value found in database field. LOWER(string) Returns string with all characters in lowercase. Examples. Lower("CAT") returns string "cat". Lower(A1) returns string in spreadsheet cell A1, with all characters converted to lowercase. Lower([field]) returns string in database field, with all characters converted to lowercase. MAX(numeric list) Returns the maximum value in numeric list. The elements in the numeric list can include spreadsheet cell ranges. Examples: Max(2, 10, 4, 6) returns 10. MIN(numeric list) Returns the minimum value in numeric list. The elements of the numeric list may include spreadsheet cell ranges. Examples: Min(2. 1O. 4. 6) returns 2. MOD(number1, number2) Returns number1 modulo number2 (the remainder of number1 divided by number2). Examples: Mod(10, 3) returns numeric value 1. Mod(3, 0) returns value ERROR. Mod(A1, A2) returns remainder when the numeric value found in spreadsheet cell A1 is divided by \ value in spreadsheet cell A2. Mod([field1], [field2]) returns remainder when numeric value found in database field1 is divided by value found in database field2. MONTH(string) Returns a string for the month portion of the date represented by string.The string may be expressed in any of these forms: mm/dd/yy; mm/dd/yyyy; Month, dd, yyyy; Month dd, yyyy. Examples: Month("Jan. 24, 1988") returns "Jan." Month("2/1/89") returns "Feb." Month(A1) returns the month portion of the date in spreadsheet cell A1. Month([fieldname]) returns the month portion of database field specified. NOT(expression) Returns 1 (true) if expression is false (returns a zero value). Examples: Not(0) returns value 1. Not(1) returns value 0. Not(10) returns value 0. Not(A1) returns value 1 if value in spreadsheet cell A1 evaluates to 0; otherwise returns value 0. Not([field]) returns value 1 if value in database field evaluates to 0; otherwise returns value 0. NPV(rate, numeric range) Returns the net present value of future payments. Rate represents a discount rate; you can enter the rate as a percent value (such as 10%) or a decimal value (such as 0.10). The numeric range represents the amount of the investment and is a list of future credits and debits. Income is represented by positive values in the list, and payments are represented by negative values in the list. The future payments are assumed to occur at equal time intervals, and the first payment is assumed to occur at the end of the first time interval. Example: NPV(12%, A1..A10) describes the net present value of three future payments of the numeric values contained in spreadsheet cells A1 through A10, invested at a constant interest rate of 12%. NULL Returns a null string (""). Example: Null returns the null string "". PMT(pv, rate, term) Calculates the periodic payment necessary to amortize a loan across a fixed number of periods. pv is the present value or principal amount of the loan; enter pv as a positive value with an optional leading dollar sign. Rate is the interest rate of the loan; you can enter rate as a percent value (such as 10%) or a decimal value (such as 0.10). Term is the number of periods that make up the term of the loan. Example: Pmt(10000, 10.5%/12, 240) returns the monthly payment on a loan of $10,000 at an interest rate of 10.5% for 240 months. POSITION(string, string) Returns a number corresponding to the position where the first argument string first occurs in the second argument string. Examples: Position( "d", "abcde") returns the value 4, the position where the first argument first occurs in the second string. Position("world", "Hello world") returns the value 1. Position("ap", "apples and applications") returns the value 1. Position(A1, A2) returns the location of cell A1's string value within cell A2's string value. Position([field1], [field2]) returns the location of field1's string value within field2's string value. PRODUCT(numeric list) Returns the product of all the numbers on numeric list. Examples: Product(2, 3, 4) returns 24. Product(A1..A10) returns the product of the values found in the spreadsheet range from A1 through A10 (ignores empty cells in ranges or cells containing strings). Product([field1], [field2],...) returns the product of the values found in the specified database fields. PROPER(string) Returns a string with the first character in the string converted to upper case and all other characters in lowercase. Examples: Proper("robert") returns"Robert". Proper(A1) returns the string found in cell A1, with the first character converted to uppercase, and all other characters converted to lowercase. Proper([field]) returns the string found in the database field, with the first character converted to uppercase, and all other characters converted to lowercase. PV(payment, rate, term) Calculates the present value of a series of equal periodic payments or of a single investment. Payment is the amount of the investment or periodic payment; enter payment as a positive value, with an optional leading dollar sign. Rate is the discount rate; you can enter rate as a percent value (such as 10%) or a decimal value (such as 0.10). Term is the number of periods that make up the term of the investment. Example: PV(10000, 8%, 10) returns the present value (the value today) of a payment of $10000 invested at an annual rate of 8% for 10 years. RADIANS(number) Returns the number of radians equivalent to number degrees. The function expects the number to be expressed in degrees. Example: Radians(180) returns the value 3.14 ( an approximation of pi), which expresses the number of radians equivalent to 180 degrees. RANDOM(number) Returns a random number between 0 and (number - 1). If the argument is 0 or 1, then Random returns a random value greater than or equal to 0 and less than 1. Examples: Random(50) returns a random number between 0 and 49, inclusive. Random(0) or Random( 1 ) returns a random number greater than 0 and less than 1. Random(A1) returns a random number between 0 and the value in spreadsheet cell A1 minus 1. Random([fieldname]) returns a random number between 0 and the value in the database field specified minus 1. RATE(fv, pv, term) Calculates the interest rate of return on an investment involving constant, equal periodic payments or a single lump-sum payment. Fv is the future value of the loan; enter fv as a positive value with an optional leading dollar sign. Pv is the present value of the investment; enter pv as a positive value, with an optional leading dollar sign. Term is the number of periods that make up the term of the investment. Example: Rate(10000, 2000, 5) returns the rate of return of an investment whose present value is $2000 and future value is $10000 over 5 periods. ROUND(number, number) Returns the first argument, rounded up to the number of decimal places specified by the second argument. Examples: Round(3.14, 1) returns 3.1. Round(2.789, 2) returns 2.79. Round(1.5, 0) returns 2. Round(1, A1) returns the value in spreadsheet cell A1, rounded to 1 decimal place. Round([fieldname]) returns the value in the database field specified, rounded to one decimal place. SGN(number) Returns 1, 0, or -1, depending on the sign of number. Examples: Sgn(2) returns 1. Sgn(-2) returns-1. Sgn(0) returns 0. Sgn(A1) returns the sign of the value in spreadsheet cell A1. Sgn([fieldname]) returns the sign of the value in the specified database field. SIN(number) Returns the sine of number. The function expects the number to be expressed in radians. Examples: Sin(0) returns 0 (sine of 0 radians). Sin(Pi) returns 1 (sine of ~ radians). Sin(A1) returns the sine of the value in spreadsheet cell A1. Sin([fieldname]) returns the sine of the value in the database field specified. SQRT(number) Returns the positive square root of number. If the number is negative, the function returns ERROR. Examples: Sqrt(16) returns 4. Sqrt(0) returns 0. Sqrt(-1) returns ERROR . Sqrt(A 1) returns the square root of the value in spreadsheet cell A1. Sqrt([fieldname]) returns the square root of the value in database field specified. STDV(nnmeric list) Examples: Stdv(2, 7, 9, 10, 27) returns 9.46. Stdv(A1..A10) returns the standard deviation of the values found in spreadsheet cells A1 through A10 (ignoring empty cells in ranges). Stdv([field1], [field2],...) returns the standard deviation of the values field1...fieldn of the specified database fields. STRING(number) Examples: String(43) returns the string "43". String(999) returns the string "999". String(A1) returns the string equivalent of the numeric value in spreadsheet cell A1. String([fieldname]) returns the string equivalent of the numeric value in the database field specified. SUBSTR(string, start, length) Returns a substring specified by the two numeric arguments, start and length. The first argument is a string, from which a substring is extracted. The argument start specifies the starting position of the substring and the length argument specifies the length of the substring. Examples: SubStr("Hello world", 7, 5) returns "world". SubStr("abcdefg",3, 2) returns "cd". SubStr(A1, 3, 5) returns the substring found beginning at 3 position extending for 5 characters within cell A1's string value. SubStr([fieldname], 3, 5) returns the substring found beginning at position 3 and extending for 5 characters within the field's string. SUM(numeric list) Returns the sum of the numbers in numeric list. Examples: Sum(1, 3, 5) returns 9. Sum(A1..A10) returns the sum of the values found in spreadsheet cells A1 through A10 (ignoring empty cells). Sum([field1], [field2], ...) returns the sum of the values found in the specified database fields. TAN(number) Returns the tangent of the number. The function expects the number to be expressed in radians. Examples: Tan(Pi/4) returns 1 (tangent of pi/4 radians). Tan(A1) returns the tangent of the value in spreadsheet cell A1. Tan([fieldname]) returns the tangent of the value in the database field specified. TERM(payment, rate, fv) Returns the number of periods required to amortize a loan in constant periodic payments. Rate is the interest rate of the loan; you can enter rate as a percent value (such as 10%) or a decimal value (such as 0.10). Payment is the periodic payment; enter payment as a positive value, with an optional leading dollar sign. Fv is the future value of the loan; enter fv as a positive value, with an optional leading dollar sign. Example: Term(1000, 8%, 50000) returns the number of periods required to amortize a loan of $50000 at 8% with payments of $1000 per period. TODAY Returns a string representing today's date. (The date is taken from the internal clock on your Apple IIGS. If the date is incorrect, use the Apple IIGS Control Panel to reset the date.) Example: Today returns "Jan. 1, 1989", or a similar string that corresponds to the current date. TRUE Returns the value 1. True takes no arguments. Example: True returns numeric value 1. UPPER(string) Returns string with all characters in uppercase. Examples: Upper("robert") returns"ROBERT". Upper(A1) returns the string found in cell A1, with all characters converted to uppercase. Upper([field]) returns the string found in the database field, with all characters converted to uppercase. VAL(string) Returns the numeric equivalent of the string argument. Use this function to convert a number currently expressed as a string into its numeric equivalent, so that other numeric functions can operate on it. Examples: Val("43") returns the numeric value 43. Val("999") returns the numeric value 999. Val(A1) returns the numeric equivalent of the string value in spreadsheet cell A1. Val([fieldname]) returns the numeric equivalent of the string value in the database field specified. VAR(numeric list) Returns the variance of the numbers in the arguments in numeric list. Var assumes that the numbers in numeric list are sample numbers and not total population, and provides the best estimate of the population variance. If numeric list represents the entire population rather than a sample, you can calculate the variance for the entire population by including the average of the sample in numeric list. Example: Var(A1..A20) returns the variance of the values in spreadsheet cells A1 to A20. VLOOKUP(value, range, offset) Looks up value in a table contained in the spreadsheet range. Finds largest value in first column of the range which is less than or equal to value, and then returns the value found offset columns to the right. (This assumes that the first column of the range is sorted in increasing order.) Example: VLookup(2, A1..A4,3) returns value found using this search method: ¥ finds largest value in column A1 to A4 less than or equal to 2, and then ¥ finds cell 3 columns to the right in the range (the offset value), and returns the value in that cell. WEEKDAY(string) Examples: Weekday("Jan. 24, 1988") returns "Monday". Weekday("June 21, 1988") returns "Tuesday". Weekday(A1) returns the weekday of the date in spreadsheet cell A1. Weekday([fieldname]) returns the weekday of the date value in the specified field. YEAR(string) Returns a number representing the year portion of the date represented by string. Examples: Year("Jan. 24, 1988") returns 1988. Year("Jan. 1, 1989") returns 1989. Year(A1 ) returns the year of the date in spreadsheet cell A1. Year([fieldname]) returns year of the date value in the database field. -end of file-