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
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:
Brackets ( [ ] ) surrounding a keyword indicate that the keyword is optional and may be omitted:
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.
•
|
|
•
|
|
•
|
|
•
|
|
•
|
|
•
|
|
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 |
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) = 45DEGREES 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.59815EXP 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) = 6Only 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.693147LN 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) = 2LOG(1000) = 3 |
MAX(List) |
Returns the largest number in the given list of numbers.
MAX(25,10,15) = 25If 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) = 10If 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) = –25MINUS(–3) = 3You 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) = 0MOD(7,2) = 1MOD(–7,2) = –1You 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) = 9You 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) = 1296The 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.6This 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 sequence. 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.4ROUND(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) = 11The 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) = 36SUM(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.5The 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) = 1200In 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, = 5322.42The 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.22The 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.72The 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.26The 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.71The 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.45The 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.68The 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 moneythat 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.018854The 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.01093The 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) = 700In 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) = 600In 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.2The 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.5The 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) = 0In 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( )) = TrueIF(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) = 0In 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) = 1In 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) = 0In 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) = 0NOT(2*0) = 1You 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) = 1Even 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) = –1SIGN(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) = ACHAR(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”) = 2FIND(“me”, “shatter”) = 0In 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) = NewYou 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) = winYou 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”) = naturalIf
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) = useYou 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 ”) = Here 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)) = ABCDSummumCONCAT 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) = 15With
CELL(Text) , the text string specifies the cell address. For example, if cell H1 contains “B3,” thenCELL(“H1”) = 15CELL( ) 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) = 11CHOOSE(–1,“ten”,11,“twelve”) = twelveSUM(CHOOSE(1,A1:B2,C1:C3)) = total of cells A1 through B2In 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) = 7In 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 lower 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. |