Export (0) Print
Expand All
ABS
AND
ASC
COS
DAY
DDB
EXP
FV
IF
INT
LEN
LN
LOG
MAX
Me
MID
MIN
MOD
NOT
IS
NPV
ODD
OR
PI
PMT
PV
SIN
SLN
SUM
SYD
T
TAN
VAR
Expand Minimize

About calculation operators

Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Operators specify the type of calculation that you want to perform on the elements of a formula. Lists support three different types of calculation operators: arithmetic, comparison, and text.

Types of operators

Arithmetic operators To perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results, use the following arithmetic operators.

Arithmetic operator

Meaning (Example)

+ (plus sign)

Addition (3+3)

(minus sign)

Subtraction (31)
Negation (1)

* (asterisk)

Multiplication (3*3)

/ (forward slash)

Division (3/3)

% (percent sign)

Percent (20%)

^ (caret)

Exponentiation (3^2)

Comparison operators You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value of Yes or No.

Comparison operator

Meaning (Example)

= (equal sign)

Equal to (A=B)

> (greater than sign)

Greater than (A>B)

< (less than sign)

Less than (A<B)

>= (greater than or equal to sign)

Greater than or equal to (A>=B)

<= (less than or equal to sign)

Less than or equal to (A<=B)

<> (not equal to sign)

Not equal to (A<>B)

Text concatenation operator Use the ampersand (&) to join, or concatenate, one or more text strings to produce a single piece of text.

Text operator

Meaning (Example)

& (ampersand)

Connects, or concatenates, two values to produce one continuous text value ("North"&"wind")

The order in which a list performs operations in a formula

Formulas calculate values in a specific order. A list formula might begin with an equal sign (=). Following the equal sign are the elements to be calculated (the operands), which are separated by calculation operators. Lists calculate the formula from left to right, according to a specific order for each operator in the formula.

Operator precedence

If you combine several operators in a single formula, lists perform the operations in the order shown in the following table. If a formula contains operators with the same precedence for example, if a formula contains both a multiplication and division operator lists evaluate the operators from left to right.

Operator

Description

 

Negation (as in 1)

%

Percent

^

Exponentiation

* and /

Multiplication and division

+ and

Addition and subtraction

&

Connects two strings of text (concatenation)

= < > <= >= <>

Comparison

Use of parentheses

To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example, the following formula produces 11 because a list calculates multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.

=5+2*3

In contrast, if you use parentheses to change the syntax, the list adds 5 and 2 together and then multiplies the result by 3 to produce 21.

=(5+2)*3

In the example below, the parentheses around the first part of the formula force the list to calculate [Cost]+25 first and then divide the result by the sum of the values in columns EC1 and EC2.

=([Cost]+25)/SUM([EC1]+[EC2])

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft