WD: Overview of Expression and Formula Field FunctionsLast reviewed: March 4, 1998Article ID: Q105640 |
The information in this article applies to:
SUMMARYThis article explains in detail the syntax and usage of the operators and functions that are available to the Expression (Formula) field. In Word versions 6.x and later, the Expression field name was changed to Formula. Also, the Formula field name was changed in version 6.x to Equation.
1.x, 2.x 6.x, 7.x, 97, 98 Macintosh Edition Field Name Field Name ------------------- ----------------------- = (Expression) = (Formula) Eq (Formula) Eq (Equation) OPERATORSIf you combine several operators in one field, Word evaluates the information in the following order:
1. % Percent 2. ^ Exponentiation 3. * and / Multiplication and division 4. + and - Addition and subtractionIn formulas with more than one operator with the same priority, Word evaluates the operators from left to right. Use parentheses if you want to alter the order of evaluations.
+ Addition Syntax: {=x+y}
- Subtraction Syntax: {=x-y}
NOTE: {=x--y} is the same as {=x+y}. An even
number of subtraction signs is equivalent to
one addition sign.
* Multiplication Syntax: {=x*y}
/ Division Syntax: {=x/y}
% Percentage Syntax: {=x%}
{=25%} = .25
{=200*25%} = 50
^ Powers and roots Raise a number to another power
or find a root of a number.
Syntax: {=x^y}
{=4^2} = 16 4 squared
{=3^3} = 27 3 Cubed
{=27^(1/3)} = 3 Cube root of 27
= Is equal to
< Less than
<= Less than or equal to
> Greater than
>= Greater than or equal to
<> Not equal to
TABLE COLUMN REFERENCES
Word 6.x, 7.x, 97, and Word 98 Macintosh Edition
NOTE: Word for Windows versions 6.x, 7.x, 97, and 98 use A1 reference
types to refer to cells. It does not use RnCn reference types.
Bookmarkname [D4] Cell D4 in table identified by
bookmarkname.
Bookmarkname [D4:J17] Range of cells identified by
bookmarkname.
ABOVE, BELOW, LEFT, RIGHT can be used with the table functions
(AVERAGE, COUNT, MAX, MIN, PRODUCT, SUM).
Word 1.x, 2.x
NOTE: n is used to indicate the row or column number.
[RnCn] Cell in Row n, Column n.
[Rn] All cells in Row n.
[Cn] All cells in Column n.
[R] Row that the formula is in, including the
current cell.
[C] Column that the formula is in, including
the current cell.
[RnCn:RnCn] Range of all cells between the two cell
references.
Bookmarkname [RnCn] Cell in table identified by bookmarkname.
Bookmarkname [RnCn:RnCn] Range of cells identified by
bookmarkname.
FUNCTIONSFunctions with () can take any number of arguments. If the arguments have more than two digits, you must put a space in front of each. There are five Boolean functions, which return a value of 1 (true) or 0 (false): NOT, OR, AND, FALSE, TRUE. The following list provides information about the available functions for use in Word: Note: the AVERAGE, MAX, MIN, COUNT, PRODUCT, SUM functions can refer to cells in a table.
ABS(x) Returns the absolute value of a number. The absolute
value of a number is the number without its sign.
{=ABS(3.5)} = 3.5
{=ABS(-3.5)} = 3.5
AND(x,y) Returns "1" if both arguments are true. Returns "0" if
both arguments are false. Mostly used with IF
statements.
{=AND(profits>1000, profits<2000)} = 1 when "profits"
has been defined as $1,234. The same formula returns 0
when profits has been defined as $2,234.
{=IF(AND(profits>1000,profits<2000),13,15)} = 13 when
profits has been defined as $1,234. The same formula
returns "15" when profits has been defined as $2,234.
AVERAGE() Gives the average value of the numbers in the list.
{=AVERAGE(10, 20, 60)} = 30 {=AVERAGE([RnCn],[RnCn])}
COUNT() Tells you how many items are in the list.
{=COUNT(2, 5, 67, 1.38)} = 4
{=COUNT([RnCn],[RnCn],[RnCn],[RnCn])}
DEFINED(x) Returns "1" (true) if x has been defined, “0" (false)
if it has not.
{=DEFINED(4/0)} = 0
{=DEFINED(gross_income)} = 1 if gross_income exists and
it evaluates without error.
FALSE Returns a 1 if true or a 0 if true.
{=FALSE} = 0
{=IF(5=6,TRUE,FALSE)} = 0
IF(x,y,z) The result y if the conditional expression x is true, or the
result z if the conditional expression is false. Note that y
and z is usually 1 and can be either any numberic value or
the words "true" or "false".
INT(x) Rounds x down to the nearest integer.
{=INT(5.67)} = 5
MAX() Returns the largest value in the list.
{=MAX(3, 4, 5)} = 5
{=MAX([RnCn],[RnCn],[RnCn])}
MIN() Returns the smallest value in the list.
{=MIN(3, 4, 5)} = 3
{=MIN([RnCn],[RnCn],[RnCn])}
MOD(x,y) Returns the remainder (modulus) after x is divided by y.
The result has the same sign as y. If the answer is 0,
returns "0.0" (without quotation marks).
{=MOD(4, 2)} = 0.0
{=MOD(3, 2)} = 1
{=MOD(-3, 2)} = 1
{=MOD(3, -2)} = -1
{=MOD(-3, -2)} = -1
{=MOD(3.5, 2.7)} = 0.8
NOT(x) Returns the Boolean opposite of the result. Mostly used
with IF formulas.
{=NOT(1=1)} = 0
{=NOT(1=5)} = 1
If x is defined as 7, 7{=NOT(x>10)} = 1
OR(x,y) Returns "1" if one or both arguments are true. Returns
"0" if both arguments are false. Generally used with IF
formulas.
{=OR(1+2=3, 2=4)} = 1
{=OR(1+5=3, 2=4)} = 0
PRODUCT() Multiplies all the items in the list.
{=PRODUCT(5,4,3)} = 60 {=PRODUCT(5,4,3,(1/2))} = 30
{=PRODUCT([RnCn],[RnCn],[RnCn])}
ROUND(x,y) Rounds x to y digits. If y is greater than 0, then x is
rounded down to y decimal places.
If y is 0, x is rounded down to the nearest integer.
If y is a negative number, x is rounded down to the
left of the decimal point.
{=ROUND(x,y)}
{=ROUND(123.456, 2)} = 123.46
{=ROUND(123.456, 1)} = 123.5
{=ROUND(123.456, 0)} = 123
{=ROUND(123.456, -0)} = 123
{=ROUND(123.456, -1)} = 120
{=ROUND(123.456, -2)} = 100
{=ROUND(156.789, -2)} = 200
y is zero. Rounds x to the nearest integer.
{=ROUND(123.456, 0)} = 123
y is positive. Rounds to y decimal places.
{=ROUND(123.456, 2)} = 123.46
{=ROUND(123.456, 1)} = 123.5
y is negative. Rounds x to that number of places to the
left of the decimal. Y must not be greater than 1
minus the number of digits in x. "-1" rounds to the
nearest tens place, "-2" rounds to the nearest hundreds
place, "-3" rounds to the nearest thousands place.
{=ROUND(123.456, -0)} = 123
{=ROUND(123.456, -1)} = 120
{=ROUND(123.456, -2)} = 100
{=ROUND(156.789, -2)} = 200
SIGN(x) Determines whether a number is positive (returns 1) or
negative (returns -1) or zero (returns 0). Mostly used
with the IF field.
{=SIGN(-25)} = -1
{=SIGN(0)} = 0
{=SIGN(456)} = 1
SUM() Adds the numbers in the list.
{=SUM(5, 13, 2, 4, 6)} = 30 {=SUM(5, 13, -2, 4, -6)} = 14
{=SUM([RnCn],[RnCn],[RnCn])}
TRUE Returns a 1 if true or a 0 if false.
{=TRUE} = 1
In Word 97 for Windows and Word 98 Macintosh Edition, for more information
about formulas, click the Office Assistant, type "formula," click Search,
and then click "Field codes: (Formula) field".
NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Word Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q120802 TITLE : Office: How to Add/Remove a Single Office Program or Component REFERENCES"Word for Windows and OS/2 Technical Reference" (blue book), pages 337-342 "Word for Windows Technical Reference" (brown book), pages 153-156 Word for Windows 2.0 Help, Index, Field Types and Instructions, Expression "Hacker's Guide to Word for Windows," Woody Leonard and Vincent Chen, Addison Wesley Publishing Company, 1993
|
Additional query words: winword2 word6 word95 word7 winword winword acword
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |