Reference: WordPerfect table functions

You can learn the basics of using table functions, descriptions of commonly used argument keywords, and definitions and examples of all the functions listed in the Table functions dialog box.

Each WordPerfect table function has a name and is followed by parentheses. If a function needs arguments (information), argument keywords display inside the parentheses.

For example, the CHOOSE function displays as

  CHOOSE(Number, List)

in the Table functions dialog box. “Number” and “List” are the arguments of the function. The arguments are separated by a comma, and each argument is represented by a keyword that describes the type of information needed. Spaces are not needed between values, operators, and functions. Letters in addresses and functions can be uppercase or lowercase.

If a function needs no arguments, the function name is followed by empty parentheses:

  COL( )

Brackets ( [ ] ) surrounding a keyword indicate that the keyword is optional and may be omitted:

  CHAR([Set Number,] Number)

Do not include the brackets when entering the function.

The following is a list of the WordPerfect table functions by category. You can click each one to find out more about it.

•

Date table functions

•

Mathematical table functions

•

Financial table functions

•

Logical table functions

•

String table functions

•

Miscellaneous table functions

Argument keywords

The following is a list of the most common argument keyword descriptions. You can click each one to find out more about it.

•

Block

•

Cell

•

Date/time

•

List

•

Value

•

Depreciation keywords

•

Growth/amortization keywords

Date table functions

Function

Description

DATE( )

Returns the current date in the format you have selected for that cell, using Custom Date Types. If you have not selected a date format for the cell, the date will display in the document’s current date format.

DATETEXT(Date)

Converts Date (a date value) into text (see DATEVALUE below). The format of the date text depends on the date setting in Custom Date Types or the document’s current date format (see DATE above).

DATETEXT(35711) = October 9, 1997

DATEVALUE(Text)

Returns the date value for the given text string. Each date from January 1, 1900, to April 19, 65493, is assigned a consecutive number in WordPerfect. The format of the date text depends on the setting in Custom Date Types or the document’s current date format (see DATE above). You can use all digits in the year or only the last two when you enter the text string.

If only two digits are given for the year, the function adds 1900 to the year. If no year is given, the function uses the current year.

DATEVALUE(“6/24/96”) = 35239

DATEVALUE(‘5-19’) = 35933 if the year is 1998

You can use MDY to calculate the date value from numeric values rather than a text string or to quickly calculate the date value for the current date.

DAY([Date Value])

Returns the day of the month (a number between 1 and 31) for the given date value.

DAY(35603) = 23

35603 is the date value for June 23, 1997. For details about date values, see DATEVALUE above. If you do not include a date value, the function returns the current day.

HMS([Hour], [Minute], [Second])

Returns the specified or current time of day as a decimal number (time value). Hour must be an integer from 0 to 24, and the Minute and Second arguments must be integers from 0 to 59. HMS then converts the given time to a decimal number from 0 to 0.999988, with 0 equal to 12:00 midnight and 0.5 equal to 12:00 noon.

HMS(9,42,38) = 0.404606

HMS may be entered in one of four ways:

•

HMS(Hour,Minute,Second)

•

HMS(Hour,Minute)

•

HMS(Hour)

•

HMS( )

If you include the hour with no minute or second argument, the function assumes the minute or second values to be zero. The hour, minute, and second arguments are optional. If none of them are specified, the function returns the decimal number converted from the current computer time.

To calculate the decimal value of a time specified by a text string, use TIMEVALUE.

HOUR([Time Value])

Returns the hour of the given or current time. Only the decimal portion of a number is recognized. For details on time values, see HMS above.

HOUR(0.404606) = 9, because 0.404606 is the decimal value for 9:42:38.

The time value argument is optional. If no time value is specified, the function returns the current hour of the day based on the current computer time.

MDY([[Month, Day], [Year]])

Returns the date value of the specified month, day, and year. For details on date values, see DATEVALUE earlier in this appendix.

The month must be a number from 1 to 12, the day must be a number from 1 to 31, and the year can be any year from 1900 through 65535. If you enter a two-digit number for the year, the function adds 1900 to the number (that is, 96 is the same as 1996). Because each piece of date information is treated as a separate argument, the names or addresses of cells containing appropriate numbers can be used for the month, day, and year entries.

MDY can be entered in one of three ways:

•

MDY(Month,Day,Year)

•

MDY(Month,Day)

•

MDY( )

The date format depends on the settings in Custom Date Types or in the document’s current date format.

Any arguments that are not included are assumed to be the current month, day, or year.

MDY(8,4,96) = 35280

MDY(8,4) = 35280 if the current year is 1996

MDY( ) = the current date value

You can use DATEVALUE to calculate the date value from a text string rather than from numeric entries.

MINUTE([Time Value])

Returns the minute (0–59) of the specified or current time. The time must be given as a decimal number. For details on time numbers, see HMS earlier in this appendix.

MINUTE(0.404606) = 42, because 0.404606 is the decimal value for 9:42:38.

The time value argument is optional. If it is not specified, the function returns the current minute based on the current computer time.

MONTH([Date Value])

Returns the number of the month for the specified date value. For details on date values, see DATEVALUE earlier in this appendix. If you do not include a date value, the number of the current month is returned.

MONTH(35280) = 8, because 35280 is the date value for August 4, 1996.

SECOND([Time Value])

Returns the second (0-59) of the given or current time. The time must be given as a time value. See HMS earlier in this appendix for details on time values.

SECOND(0.404606) = 38, because 0.404606 is the decimal value for 9:42:38.

The time value argument is optional. If it is not specified, the function returns the current second based on the current computer time.

TIME( )

Returns the current time as a text string. The time is updated each time the worksheet is recalculated.

The time format depends on the settings in Custom Date Types or the document’s current date format.

TIMETEXT(Time)

Returns the given time as a text string. TIMETEXT only uses the decimal portion of a number. Se
e HMS earlier in this appendix for more information on time values.

TIMETEXT(0.404606) = 9:42am

The time format depends on the settings in Custom Date Types.

TIMEVALUE(Text)

Returns the decimal value of the time indicated by the given text string. See HMS earlier in this appendix for more information on time values.

Text must be in one of the following formats:

•

“hour”

•

“hour:minute”

•

“hour:minute:second”

The hour must be an integer from 0 to 24, and the minute and second must be integers from 0 to 59. You can also include “am” or “pm.”

TIMEVALUE(“19:42:05”) = 0.820891

To calculate a decimal value for a time entered as numbers rather than text, or to quickly calculate the decimal value of the current time, use HMS.

YEAR([Date Value])

Returns the year for the given Date Value. Returns the current year if no argument is included. See DATEVALUE earlier in this appendix for information about date values.

Date Value can be any integer from 1 (January 1, 1900) to 23,227,139 (October 19, 65493).

YEAR(35678) = 1997, because 35678 is the date value for September 6, 1997.

Mathematical table functions

Function	Description
`ABS(Number)`	Returns the absolute value of the given number. Positive numbers remain positive; negative numbers become positive. `ABS(–3)` = 3
`ACOS(Number)`	Returns the arccosine (cos–1) of the given number. The result is an angle in radians from 0 to p. Number must be greater than or equal to –1 and less than or equal to 1. `ACOS(.5)` = 1.047198 (or p/3) Angles expressed in radians can be expressed in degrees using `DEGREES`. `DEGREES(ACOS(.5))` = 60
`ASIN(Number)`	Returns the arcsine (sin–1) of the given number. The result is an angle in radians from –p/2 to p/2. Number must be greater than or equal to –1 and less than or equal to 1. `ASIN(.5)` = 0.523599 (or p/6) Angles expressed in radians can be expressed in degrees using `DEGREES`. `DEGREES(ASIN(.5))` = 30
`ATAN(Number)`	Calculates the arctangent (tan–1) of the given number, returning an angle in radians from –p/2 to p/2. Number can be any value. `ATAN(1)` = 0.785398 (or p/4) Angles expressed in radians can be expressed in degrees using `DEGREES`. `DEGREES(ATAN(1))` = 45
`ATAN2(Xnumber, Ynumber)`	Returns the arctangent (tan–1) of the angle represented by a point with the coordinates Xnumber and Ynumber. An angle is formed between the X axis and a line drawn from the indicated point (Xnumber, Ynumber) to the origin (0,0). The arctangent is then calculated on that angle. The result is a value in the range of –p( ) to p( ). `ATAN2(3,4)` = 0.927295
`AVE(List)`	Returns the average (mean) of a list of numbers. The average of the null set `AVE( )` equals NA. `AVE(15,2*10,SUM(1,2,4))` = 14
`COS(Number)`	Returns the cosine of the given number, which must be an angle in radians. `COS(PI( )/3)` = 0.5
`DEGREES(Number)`	Converts the angle represented by the given number from radians to degrees. `DEGREES(PI( )/4)` = 45 `DEGREES` is the inverse function of `RADIANS`. In other words, if `DEGREES(PI( )/4)` = 45, then `RADIANS(45)` = 0.785398 (or p/4).
`EXP(Number)`	Calculates the base of the natural logarithm, or “e” (2.7182818), to the power indicated by the given number. `EXP(4)` = 54.59815 `EXP` is the inverse function of `LN`. In other words, if `EXP(4)` = 54.59815, then `LN(54.59815)` = 4.
`FACT(Number)`	Returns the factorial of the specified number. The factorial of a number is the product of all the integers less than and equal to that number and greater than zero. For example, the factorial of 4 is 4 * 3 * 2 * 1 = 24. The smallest value that can be used is 0, and th e largest value that can be used is 170. (The factorial of 0 equals 1.) `FACT(3)` = 6 Only the integer portion of a number with decimal places is used to perform the factorial function. For example, `FACT(3.9)` = 6, because 3! = 6. You can also use the Factorial operator (!) to calculate factorials.
`* (GRAND TOTAL)`	In the current column, adds the values in all cells above the formula cell that contain a `TOTAL(=)` calculation. (See `= (TOTAL)` later in this appendix.) This function is not listed in the Table Functions dialog box and it does not require you to type a name — just type an asterisk `()` to perform a grand total. You can also quickly sum values in cells using the QuickSum feature. For example, type an asterisk `()` in cell A40 to add the totals in the cells above A40.
`INT(Number)`	Deletes any fractional portion of the given number, returning only the integer portion. `INT(25.77)` = 25
`LN(Number)`	Calculates the natural logarithm of the given number. The number must be greater than zero. `LN(2)` = 0.693147 `LN` is the inverse function of `EXP`. In other words, if `LN(2)` = 0.693147, then `EXP(0.693147)` = 2.
`LOG(Number1, Number2)` or `LOG(Number)`	Calculates the logarithm of `Number1` to the base specified by `Number2`. The numbers must be positive. If only one number is specified, the base 10 logarithm of that number is calculated. `LOG(16,4)` = 2 `LOG(1000)` = 3
`MAX(List)`	Returns the largest number in the given list of numbers. `MAX(25,10,15)` = 25 If you do not include any numbers (for example, `MAX( )`), the function returns NA.
`MIN(List)`	Returns the smallest number in the given list of numbers. `MIN(25,10,15)` = 10 If you do not include a list of numbers, `MIN( )` returns NA.
`MINUS(Number)`	Returns the negative of the given number. If the number is already negative, a positive number is returned. `MINUS(25)` = –25 `MINUS(–3)` = 3 You can also use the Negation operator (–) to do the same thing.
`MOD(Value1, Value2)`	Returns the remainder after dividing `Value1` by `Value2`. The values may be negative or positive. However, `Value1` must have a greater absolute value than `Value2` for `MOD` to work properly. `MOD(6,2)` = 0 `MOD(7,2)` = 1 `MOD(–7,2)` = –1 You can also use the remainder operator (%) to do the same thing. For more information, see “arithmetic operators” in the WordPerfect online Help.
`PI( )`	Returns the number 3.141592653589793. This is an approximation of the number p. `SIN(PI( )/2)` = 1
`POWER(Num1, Num2)`	Calculates `Num1` raised to the power of `Num2`. `POWER(3,2)` = 9 You can also use the Power operator (^) to do the same thing. For more information, see “arithmetic operators” in the WordPerfect online Help.
`PRODUCT(List)`	Returns the product of all the numbers in the list. `PRODUCT(2,3,4,A1:B4)` = 1296 The result in this example above is the product of 2, 3, 4, and all the numbers in the range A1:B4. You can also use the Multiplication operator (*) to do the same thing. For more information, see “arithmetic operators” in the WordPerfect online Help.
`QUOTIENT(List)`	Divides the first number in the list by the second number, divides that result by the third number, divides that result by the fourth number, and so on. `QUOTIENT(160,5,8,2.5)` = 1.6 This example is calculated in the following order: ((160 / 5 = 32) / 8 = 4) / 2.5 = 1.6 You can also use the Division operator (/) to do the same thing. For more information, see “arithmetic operators” in the WordPerfect online Help.
`RADIANS(Number)`	Converts the given number from degrees to radians. `RADIANS(90)` = 1.570796 (or p/2) `RADIANS` is the inverse function of `DEGREES`. In other words, if `RADIANS(90)` = 1.570796, then `DEGREES(1.570796)` = 90.
`RANDOM([Number])`	Returns a random number greater than or equal to 0 and less than 1. You can manipulate `RANDOM` by using a number as the argument. If the given number is positive or there is no number argument, `RANDOM` returns the next random number in the sequence. If the given number is zero, `RANDOM` returns the previous random number again, without generating a new number in the sequ ence. If the given number is negative, `RANDOM` starts a new sequence of random numbers using the negative number to generate the starting number.
`ROUND(Number, Precision)`	Returns the Number rounded to the decimal place specified by Precision. Precision can be up to 15 decimal places. You can also specify a negative Precision, which rounds the number to the left of the decimal. A Precision value of –1 rounds to the nearest 10, –2 rounds to the nearest 100, and so on. `ROUND(125.388,1)` = 125.4 `ROUND(125.388,–1)` = 130
`SIN(Number)`	Returns the sine of the given number (an angle in radians). `SIN(PI( )/6)` = 0.5
`SQRT(Number)`	Returns the square root of the given number. The number must be positive (greater than zero). `SQRT(25)` = 5
`STDEV(List)`	Returns the standard deviation of a sample from the list of numbers. `STDEV(25,15,20)` = 5
`STDEVP(List)`	Returns the standard deviation of a population. `STDEVP(25,15,20)` = 4.082483
`+ (SUBTOTAL)`	Adds the numbers in the cells directly above the cell that contains the subtotal function. Note that this function is not listed in the Table Functions dialog box and does not require you to type a name — just type a plus sign (+) to perform a subtotal. You can also quickly sum values in cells using the QuickSum feature. For more information, see “QuickSum” in the WordPerfect online Help. Type + in cell A4 to add the values in the cells above A4.
`SUBTRACT(List)`	Subtracts the second number in the list from the first, then subtracts the third number from that result, the fourth from that result, and so on. `SUBTRACT(57,22,6,18)` = 11 The example above is calculated in the following order: ((57 – 22 = 35) – 6 = 29) – 18 = 11 You can also use the Subtraction operator, the minus sign (–), to do the same thing.
`SUM(List)`	Returns the sum of the numbers in the list. You can also specify a range of cells or use the name of a range of cells, as shown. `SUM(25,10–2,3)` = 36 `SUM(a1:a10)` `SUM(bonus)` You can also use the Addition operator, the plus sign (+), to do the same thing.
`TAN(Number)`	Returns the tangent of the given number (an angle in radians). `TAN(PI( )/4)` = 1
`= (TOTAL)`	In the current column, adds the values in all subtotal cells above the formula (subtotal cells contain a `SUBTOTAL(+)` calculation). See `+ (SUBTOTAL)` earlier in this appendix. This function is not listed in the Table functions dialog box and does not require you to type a name — just type an equal sign (=) to perform a total. You can also quickly sum values in cells using the QuickSum feature. Type = in cell B35 to add the subtotals in the cells above B35.
`VAR(List)`	Returns the sample variance for a list of numbers. List must include at least two numbers. `VAR(20,22,19,23,21)` = 2.5
`VARP(List)`	Returns the population variance for a list of numbers. `VARP(68,71,67,66)` = 3.5 The list must include at least one number.

Financial table functions

Function	Description
`DDB(Cost, Salvage, Life, Period)`	Returns the depreciation amount using the double-declining balance method of calculating depreciation. This method accelerates the rate of depreciation, so that more depreciation is written off in early periods than in later periods. The depreciation stops when the book value of the asset reaches the salvage value. In any one period, the book value equals the cost minus the total depreciation over all previous periods. `DDB(5000,750,5,2)` = 1200 In this example, the Cost of the asset is $5,000, the Life of the asset is 5 years, the Salvage value after 5 years is $750, and to find the amount of depreciation in the second year, a Period of 2 is used.
`FV(Rate%, PV, Payment, Periods, [Type])`	Returns the future value of an investment or loan. The example below finds the future value of an investment with no periodic payments. This investment is a six-month certificate of deposit (CD) with a $5000 deposit and an interest rate of 12.5%, compounded daily. The following formula finds the balance of the account at the end of the six months: `FV(12.5%/365,–5000, 0,365/2,0)` = 5322.42 The interest rate percentage is divided by 365 because it is compounded daily. The period must also be expressed in days (365/2 equals six months). The present value (`PV`) is expressed as a negative number because you pay it at the beginning of the period. The result is positive because you get it back at the end of the period. The example below finds the future value of a loan after a specified number of periods. This loan is a $90,000 mortgage for 30 years at an annual interest rate of 11.75%. The monthly payment is $908.47 and is paid at the end of the period. The following formula finds the balance of the loan after 20 years: `FV(.1175/12,90000,–908.47,20*12,0)` = –63962.22 The interest rate is expressed as a decimal instead of a percentage. It is divided by 12 because it is compounded monthly. Because the rate is expressed in months, the period must also be expressed in months. The present value of the loan is a positive number because it is paid to you. The future value of the loan is returned as a negative number because it is money still owed on the loan.
`IRR(List of Cashflows, Rate%)`	Calculates the internal rate of return for a list (series) of cash flows. The first cash flow in the series is negative, representing the initial investment. An estimate of the rate of return (rate argument) must be specified as a percentage. `IRR` uses a sequence of estimates to calculate the internal rate of return. Because `IRR` uses approximations, you enter only an estimate for the second argument. In this example, the `IRR` for Project 4 is 7.07% (shown in cell H7). The Rate% used is 14%.
`NPV(List, Rate%)`	Calculates the net present value (the amount of money required now to produce the given cash flow in the future). The calculation is based on the specified interest rate. Each item in the list is the cash flow required at the end of a period. The cash flows must be listed in the order the periods occur (first period, second period, and so on). The period of the interest rate and the required cash flow must be the same. The interest rate can be entered as a decimal fraction (for example, .25) or as a percentage (for example, 25%). In this example, the numbers in cells B3, B4, and B5 are 15000, 20000, and 25000, respectively. `NPV(B3:B5,12%)` = 47,131.24
`PMT(Rate%, PV, Periods, FV, [Type])`	Returns the payment for a loan or investment. The following example finds the periodic payment needed to fully amortize (eliminate the balance of) a $125,000 mortgage for 30 years at an annual interest rate of 7.25%. To find the monthly payment made at the end of the period, use the following formula: `PMT(7.25%/12,125000,30*12,0,0)` = –852.72 The interest rate percentage is divided by 12 because it is compounded monthly, and therefore the period must also be expressed in months. The payment is negative because it is the amount paid to the lender, and the future value is 0 because the loan will be fully amortized. The type is 0 because payments occur at the end of the period. The following example finds the payment required for an investment with a specific present value to obtain a specific future value. The investment is a savings account that pays an annual interest rate of 8.5% with an initial deposit of $2,000. To find the required periodic deposit made at the end of the period to obtain a balance of $3,500 in 18 months, use the following formula: `PMT(8.5%/12,–2000,18,3500,0)` = –64.26 The interest rate percentage is divided by 12 because it is compounded monthly, and the period must also be expressed in months. The present value (`PV`) is negative because it is money that is paid into the savings account. The type is 0 because the payment is made at the end of the period. The resulting payment (`PMT`) is also negative because it is money that must also be paid into the savings account. The following example finds the periodic payment for an investment to obtain a specific future value with no present value. The investment is a profit-sharing program with your company in which a set amount is deducted from your paycheck at the end of each month. Your company offers an annual interest rate of 32%, and you want to have a balance of $1,500 in one year. To find the amount you need to have deducted from your paycheck, use the following formula: `PMT(.32/12,0,12,1500,0)` = –107.71 The interest rate in this example is expressed as a decimal. The rate is consistent with the period because both are expressed in months. The present value is 0, because you start with nothing. The resulting payment is negative, because you must pay it from your paycheck.
`PV(Rate%, Payment, Periods, FV, [Type])`	Returns the present value for a loan or investment. The following example finds the amount of a loan that you want to fully amortize at a given interest and payment rate and in a specific number of periods. The loan is a 30-year mortgage, with an annual interest rate of 7.125%. If you want to make payments of $850 at the end of each period, you can find the amount you can borrow by using the following formula: `PV(7.125%/12,–850,30*12,0,0)` = 126165.45 The interest rate percentage is divided by 12 because it is compounded monthly, and therefore the period must also be expressed in months. The payment is negative because it is the amount paid to the lender, and the future value is 0 because you want to fully amortize it. The type is 0 because payments occur at the end of the period. The following example finds what an investment’s present value would have to be to reach a specified future value at a certain payment and period. The investment is a savings account that pays an annual interest rate of 5.5%. If you want to make a deposit of $45 at the end of each month and see a return of $2,500 after 18 months, use the following formula to find the amount you need to initially deposit: `PV(.055/12,–45,18,2500,0)` = –1526.68 The interest rate is expressed as a decimal. The rate is consistent with the period because both are expressed in months. Payment is a negative number because it is an amount paid into the account. The resulting present value is also negative because it must also be paid into the account. The `FV` is positive because it is money that you will receive from the account after 18 months.
`RATE(PV, Payment, Periods, FV, [Type])`	Returns the periodic interest rate for a loan or investment. The rate returned is based on the Periods. If the period is other than a year, you can adjust the result of the function to obtain the yearly rate. For example, if you use months to state the number of periods in this function, the result will be given as a monthly rate. To convert this monthly rate to a yearly rate, multiply the result by 12. The following example finds the interest rate required for an investment to obtain the specified future value with the specified payment and periods, with no present value. If you want to invest $50 at the end of each month for 2 years (24 months) and receive a return of $1,500, use the following formula to find the necessary monthly interest rate: `RATE(0,–50,24,1500,0)` = 0.018854 The period is expressed monthly (24), therefore the interest rate is also expressed monthly. Multiply the resultant rate (1.89%) by 12 to get the annual rate, approximately 22.6%. The payment is negative because it is an amount you must pay into the investment fund. The future value (`FV`) is positive because it will be paid to you. The type is 0 because payments occur at the end of each period. The following example finds the interest rate necessary for a loan to be fully amortized in the specified periods with the given present value and payment. An $8,000 loan is paid over 36 months, and you want to make payments of $270 at the end of each period. To find the required interest rate, use the following formula: `RATE(8000,–270,36,0,0)` = 0.01093 The period is expressed monthly (36), therefore the interest rate is also expressed monthly. Multiply the resulting rate (1.09%) by 12 to get the annual rate, approximately 13.1%. The payment is negative because it is an amount that you pay the lender. The type is 0 because payments occur at the end of each period. The present value of $8,000 is positive because it is an amount received from the lender and paid to you.
`SLN(Cost, Salvage, Life)`	Returns the straight-line depreciation amount for one period. The straight-line method takes the amount of depreciation for an asset (cost minus salvage) and divides it evenly over the life of the asset. `SLN(5000,800,6)` = 700 In this example the cost of the asset is $5,000, the life of the asset is 6 years, and the salvage value is $800 at the end of its life. The formula determines the amount of depreciation for each year.
`SYD(Cost, Salvage, Life, Period)`	Returns the sum-of-years digits depreciation amount for the specified period. This method accelerates the rate of depreciation, so that more of the asset is depreciated at the beginning of the asset’s life than at the end. `SYD(5000,800,6,4)` = 600 In this example, the cost of the asset is $5,000, the life of the asset is 6 years, and the salvage value is $800. The period is 4 to find the depreciation amount for the fourth year.
`TERM(Rate%, PV, Payment, FV, [Type])`	Returns the term (number of periods) for a loan or investment. The example below finds the number of periods required for an investment to obtain the specified future value with the given interest rate and payment and with no present value. You will obtain $3,000 by depositing $50 at the end of each month in a savings account that pays a yearly interest rate of 8.5%. To find the number of periods (months) it will take to accumulate $3,000, use the following formula: `TERM(8.5%/12,0,–50,3000,0)` = 50.2 The interest rate percentage is divided by 12 because it is compounded monthly, and therefore the term is also months (50.2 months). The payment is negative because it is an amount to be paid into the investment. The future value is positive because it is an amount you will receive from the investment. The type is 0 because payments occur at the end of the period. The example below finds the number of periods needed for a loan to fully amortize with the specified interest rate, present value, and payment. The loan’s present value is $8,000 and you want to make payments of $230 at the end of the period. If the current interest rate is 11.5%, use the following formula to determine the length of the loan: `TERM(.115/12,8000,–230,0,0)` = 42.5 The interest rate is expressed as a decimal and is divided by 12 because the period is monthly. Payment is a negative number because it is an amount you will pay to the lender. The present value is positive because it is an amount you will receive from the lender. The resultant term is expressed in months.

Logical table functions

Function	Description
`AND(List)`	Returns the logical `AND` of the given list of numbers. A list of logical statements is placed between the parentheses. If all the statements are true, the number 1 (true) is returned. If any of the statements are false, 0 (false) is returned. `AND(45>10,10+5=16,5<7)` = 0 In the above list, the statement “10+5=16” is false, so 0 is returned. You can also use the And operator (&) to do the same thing. For more information, see “arithmetic operators” in the WordPerfect online Help.
`FALSE( )`	Returns the logical value 0 (false). Use `FALSE` with functions that require a logical value of 0 (false), such as `IF`. `IF(B4<=100,True( ),False( ))` = 0 when B4 contains a value greater than 100.
`IF(Condition, Val1, Val2)`	Returns `Val1` or `Val2`, depending on whether the condition is true or false. The condition can be any logical statement. If the statement is true, `Val1` is returned; if it is false, `Val2` is returned. A value may be a number; text string; cell address or name; a function, such as `NA`, `FALSE`, or `TRUE`; or another formula. `IF(2<3,‘True’,NA( ))` = True `IF(5*5<=0,25,10/2)` = 5
`ISERR(Cell)`	Returns the number 1 (true) if any formula error (“??” or “ERR”) is found in the given cell. If no error is found, 0 (false) is returned.
`ISERR2(Cell)`	Returns the number 1 (true) if a real formula error (“??”) is found in the given cell. If no real formula error is found, returns a 0 (false). A real formula error is one that originates in the given cell and produces a “??” error message in the cell. An error (“ERR”) that results from referencing another cell containing an error is not a real formula error. For example, if 2.5/0 is entered in cell F10 and F10+1 is entered in cell F11, a “??” message displays in cell F10 and an “ERR” message displays in cell F11. In this case, `ISERR2(F10)` returns 1 and `ISERR2(F11)` returns 0.
`ISNA(List)`	Determines if a cell or range is empty (or “NA”). The list may have one or more cell references. If the cells referred to in the list have no entries, the number 1 (true) is returned to the formula cell. If even one entry exists, 0 (false) is returned. `ISNA(tax,B11:B20,A5)` = 0 In this example, the cell named “tax” and cell A5 have no entries, but there is an entry in the range B11:B20, so 0 is returned.
`ISTEXT(List)`	Determines whether the cells referred to in the list contain text. If at least one of the cells in the list contains text and none of the cells contain numbers, 1 (true) is returned; if even one cell contains a number, 0 (false) is returned. `ISTEXT(A4,company,E5:G9)` = 1 In this example, 1 is returned, because cell A4, the cell named “company,” and four of the cells in the range E5:G9 contain text, and none of the cells contain a number.
`ISVALUE(List)`	Determines whether the cells referred to in the list contain numbers. If at least one of the cells in the list contains a number and none of the cells contain text, 1 (true) is returned; if even one cell contains text, or if all the cells are empty, 0 (false) is returned. `ISVALUE(C6,interest,D22:H26)` = 0 In this example, cell C6 contains text, so 0 is returned.
`NA( )`	Returns the `NA` (not available) value — an empty cell. `IF(2>3,‘True’,NA( ))` = an empty cell
`NOT(Number)`	Returns the number 1 to the formula cell if the given number equals zero; otherwise, 0 is returned. `NOT(25)` = 0 `NOT(2*0)` = 1 You can also use the Not operator (!) to do the same thing. For more information, see “arithmetic operators” in the WordPerfect online Help.
`OR(List)`	Returns the logical `OR` of the given list of numbers. A list of logical statements is placed between the parentheses. If at least one of the statements is true, the number 1 is returned to the cell. If all of the statements are false, 0 is returned. `OR(45>10,10+5>16,5>7)` = 1 Even though two of the statements are false, the statement “45>10” is true, so 1 is returned. You can also use the Or operator (\|) to do the same thing. For more information, see “arithmetic operators” in the WordPerfect online Help.
`SIGN(Number)`	Returns –1 if the number is negative, 1 if the number is positive, and 0 if the number is 0. `SIGN(–2)` = –1 `SIGN(2)` = 1
`TRUE( )`	Returns the logical value of 1 (true). `IF(B5>200,True( ),False( ))` = 1 if B5 contains a value greater than 200.

String table functions

Function	Description
`CHAR([Set Number], Number)`	Returns a text string of one character, corresponding to the ASCII or WordPerfect character set code given as the number. If no set number is given, character set 0 (ASCII characters) is used. `CHAR(65)` = A `CHAR(1,23)` = ß
`CODE(Text)`	Returns the WordPerfect character set code for the first character in a text string. `CODE(“Alphabet”)` = 65
`CONCAT(List)`	Combines or concatenates all text values in List into a single string. When you refe rence other cells, the function combines the displayed text, numbers, or formula results of those cells in the string. If you want spaces included in a concatenation, they must be inside quotation marks. `CONCAT(“Total”, “ ”, “Costs”)` = Total Costs
`CURRENCY(Number)`	Converts the given number to a text string in currency format. This function allows a number (after it has been converted) to be included in a text string. `CURRENCY(36)` = $36.00
`FIND(Text1, Text2)`	Returns the position of the first occurrence of the first text string (`Text1`) in the second text string (`Text2`). The position is returned as a number with position 1 being the first character. If `Text1` was not found in `Text2`, 0 (false) is returned. `FIND` is not case sensitive, meaning a lowercase letter will match an uppercase letter and vice versa. `FIND(“Hat”, “shatter”)` = 2 `FIND(“me”, “shatter”)` = 0 In the first example, the text string “Hat” is found beginning at the second position in the second text string “shatter.” The text string “me” in the second example is not found in the text string “shatter,” so 0 is returned. `FIND` counts each character in a text string, including spaces, punctuation marks, and so on, as a position.
`LEFT(Text, Count)`	Returns the first Count characters in a text string. `LEFT(“New York”,3)` = New You can also give the count as a negative number. For example, `LEFT(“New York”,–3)` returns all the characters in the text string except the last three (New Y).
`LENGTH(Text)`	Returns the number of characters or length of a text string. `LENGTH(“home”)` = 4
`LOWER(Text)`	Returns the given Text string with all uppercase letters converted to lowercase. `LOWER(“THE Teacher”)` = the teacher
`MID(Text, Position, Count)`	Returns part of a text value to the formula cell. The function returns the portion of the text string beginning at the specified position, for the specified number (`Count`) of characters. The first character is position 1. `MID(‘winter’,1,3)` = win You can also give the position and count as negative numbers. If you give a negative number for the position, it indicates a position the specified number of characters to the left of the last character in the text. For example, `MID(“winter”,–3,2)` returns te. The position of –3 starts the count 3 characters to the left of the last character (at t). The count value of 2 then begins at t and counts two characters, so the result is te. If you give a negative number for the count, `MID` returns all the characters starting with the character specified by `Position` and ending `Count` characters from the last character. For example, `MID(“winter”,3,–1)` returns nte. The position value of 3 will start the count on the third character (at n). The result is everything from n to the end of the text, excluding the number of characters specified by the count value (the r), so the result is nte.
`PROPER(Text)`	Returns each word in the given text string with the first letter in uppercase and the rest in lowercase. With `PROPER`, a word is considered any group of characters preceded by a space or a period. `PROPER(“my DOG”)` = My Dog
`REPEAT(Text, Count)`	Returns the given Text string duplicated Count times. `REPEAT(“top”,3)` = toptoptop
`REPLACE(Text1, Position, Count, Text2)`	Replaces part of `Text1`, starting at the `Position` indicated, takes out `Count` characters, then inserts `Text2`. (The first letter of `Text1` is position 1.) `REPLACE(“national”,4,3, “ur”)` = natural If `Count` is the same length as `Text1`, you can replace all of `Text1` with `Text2`. By using 0 (zero) for `Count`, you can insert new text into `Text1`. If you specify a position just beyond `Text1`, you can add text onto the end of `Text1`. If you do not type any text for `Text2`, you can delete part of `Text1`.
`RIGHT(Text, Count)RIGHTText Count`	Returns the last `Count` characters in a text string. `RIGHT(“house”,3)` = use You can also give the count as a negative number. For example, `RIGHT(“house”,–3)` returns all the characters in the text string except the first three (se).
`TEXT(Number, [Precision])`	Converts a number to a text string. The Precision argument determines how many digits are displayed after the decimal point and should be a number from 0 to 15. `TEXT(12.472,2)` = 12.47 (as a text string) You can also use -1 as the precision argument to convert the number to a text string that displays all necessary digits (as many as 15).
`TRIM(Text)`	Returns the text string with no leading, trailing, or consecutive spaces. `TRIM(“ Here I am ”)` = Her e I am
`UPPER(Text)`	Returns the text string with all letters in uppercase. `UPPER(“toTAL Costs”)` = TOTAL COSTS
`VALUE(Text)`	Converts the text value to a number, if possible, and returns the number as the result. `VALUE(“$34.23”)` = 34.23

Miscellaneous table functions

Function	Description
`BLOCK(Col1, Row1, Col2, Row2)`	Returns a list of values from a range of cells in the table. `Col1` and `Row1` numbers specify one corner of the range, and `Col2` and `Row2` specify the opposite corner. Because this function returns a list of values rather than a single result, it must be used inside a function that expects a list, such as `AVE`, `CONCAT`, or `SUM`. `BLOCK( )` cannot be used to reference another table. The following example illustrates the use of `BLOCK` for the table shown below. `CONCAT(BLOCK(2,2,2,6))` = ABCDSummum `CONCAT` returns the concatenated text strings from Col 2, Row 2 (cell B2) through Col 2, Row 6 (F2).
`CELL(Col, Row)` or `CELL(Text)`	Returns the value in the cell specified by the given column and row numbers, or by the text string that names the cell. These examples assume that cell B3 contains the number 15. `CELL(2,3)` = 15 With `CELL(Text)`, the text string specifies the cell address. For example, if cell H1 contains “B3,” then `CELL(“H1”)` = 15 `CELL( )` cannot be used to reference another table.
`CHOOSE(Number, List)`	Finds the value or string in List that is specified by Number. If Number is negative, the search goes right to left. `CHOOSE(2,10,11,12)` = 11 `CHOOSE(–1,“ten”,11,“twelve”)` = twelve `SUM(CHOOSE(1,A1:B2,C1:C3))` = total of cells A1 through B2 In the first example, the number “11” is the second (2) value in the list. In the second example, the text string “twelve” is the first value from the right end of the list (–1). In the third example, `CHOOSE` returns the first (1) range A1:B2 to `SUM`.
`COL( )`	Returns a number representing the column where the function is entered. Column A is “1,” column B is “2,” column C is “3,” and so on. If `COL( )` is entered in cell B10, 2 is returned.
`COUNT(List)`	Returns a count of all values (numeric and text) in the given List. If the list includes a range, each cell in the range that contains a value is counted. If a cell is `NA`, it is not counted. `COUNT(2,‘Total’,A3,D4:D10)` = 7 In this example, “7” is returned because 2 is a value, ‘Total’ is the name of a cell that contains a value, A3 contains a value, and four of the cells within the range contain values.
`INDEX(Block, Col, Row)`	Returns the number or text from the cell indicated by the column and row offsets within the given range. Use positive or negative numbers to refer to a column and row in the range. Positive numbers refer to columns from the left to right beginning with the first column of the specified range, and refer to rows from the top down beginning with the first row of the specified range. Negative numbers refer to columns from the right to left beginning with the last column in the range (column –1), and refer to rows from the bottom up beginning with row –1. `INDEX(B3:F12,2,–3)` In this example, the column and row numbers refer to the cell at the intersection of the second column from the left and the third row from the bottom of range B3:F12 (which is cell C10).
`LOOKUP(Block, Value, [Offset])`	Looks up a value (text or a number) in a block of cells in a table. Depending on how the table is set up, either the first column or the first row of the block is used as the index. If there are more rows than columns in the block, `LOOKUP` uses the first column as the index; if there are more columns than rows, the first row is used. For `LOOKUP` to work properly, the entries in the index column (or row) must be in numeric order or in alphabetic order if you are using text strings. The first argument of the function must be the name or coordinates of a range of cells. The range must include the index column (or row) and any portion of the table you want the function to look through. The second argument may be a number or a text string, depending on whether the table’s index column (or row) consists of numbers or text strings. The third argument is optional and is discussed below. With the range and index value entered, `LOOKUP` looks through the index column (or row) for the given value. When the value is found, the function goes to the last column of that row (if using an index column) or the last row of that column (if using an index row), then it returns the number or text found there. The terms “last column” and “last row” refer to the last column or row in the specified range. If the given index value is not found, `LOOKUP` finds the closest value that is numerically or alphabetically l ower in value. You can use the `LOOKUP` direction indicators ( < and > ) in the value argument to specify the direction (lower or higher) in which you want the function to look when an exact match is not found. For example, `LOOKUP(A12:E20,117>)` specifies that 117 or the next highest value is returned. If the value in `LOOKUP` is lower in value than the first value in the index column or row and you do not include the `LOOKUP` direction indicator, nothing is returned. `LOOKUP` is useful when you do not know the exact index value or if you only need an approximate value returned from the table. If you want the table value returned that corresponds to an exact index value, use `MATCH`. In the example below, cell A1 contains the function `LOOKUP(A4:E12,117)`. Because there are more rows than columns in the specified block of the table, the first column is used as the table index. The function returns the value (3045) found in the last column of the row containing the lookup value (117). If the function contained 116 as the index value in the example above, `LOOKUP` would have returned the value (659) in the last column of the row that begins with the closest lower value (113). If you want the function to return the value of a cell other than the last cell in the column or row, use the optional offset argument. The offset argument may be zero, a positive number, or a negative number. An offset number of 0 returns the value in the index column (or row). A positive offset number (n) returns the value in the nth column to the right of the index column or the value in the nth row below the index row. A negative offset number (–n) returns the value in the nth column to the left of the index column or the value in the nth row above the index row. In the following example, 3 is entered as the offset number. `LOOKUP(A4:E12,117,3)` The value found in the third column to the right of the index column (African Adventure) is returned.
`LOOKUP2(Block, Xvalue, Yvalue)`	Looks up a value in a table. `LOOKUP2` is similar to `LOOKUP`, except that the value to be returned is specified by X and Y coordinates. The first (top) row of the specified range becomes the “X-axis,” and the first column becomes the “Y-axis.” Like the index column or row in `LOOKUP`, the X row and Y column must be in numeric or alphabetic order for `LOOKUP2` to work properly. You can, however, have an X row of numbers and a Y row of text values or vice versa. The X and Y values may be numbers or text strings, depending on whether the X row and Y column contain numbers or text strings. After entering `LOOKUP2`, the value in the table below the X value and across from the Y value is returned to the formula cell. If the exact X and/or Y values are not found in the X row and/or Y column, the function goes to the closest value that is numerically or alphabetically lower in value. You can use the `LOOKUP` direction indicators ( < and > ) to indicate in which direction you want the function to look when the exact value is not found. If you want only the table value returned that corresponds to the exact X and Y values given, use `MATCH2`. If the value in `LOOKUP2` is lower in value than the first value in the index column or row and you do not include the `LOOKUP` direction operator, nothing is returned.
`MATCH(Block, Value, [Offset])`	Looks up a value in a table. `MATCH` is used in the same way as `LOOKUP`, except the specified value must match exactly a value in the index column or row. Otherwise, no value is returned. The only other difference is that the index column or row of a range used with `MATCH` does not need to be in alphabetic or numeric order.
`MATCH2(Block, Xvalue, Yvalue)`	Looks up a value in a table. `MATCH2` is used in the same way as `LOOKUP2`, except that the specified X and Y values must match exactly an X and Y value in the X row and the Y column. Otherwise, no value is returned. The only other difference is that the index column or row of a range used with `MATCH2` does not need to be in alphabetic or numeric order.
`ROW( )`	Returns the number of the row in which the function is entered. `ROW( )` = 3 if the function is entered in cell B3.

WordPerfect Office