[Table of Contents] [docx version]

SpreadsheetML Reference Material - Table of Contents

Operators

An operator is a symbol that specifies the type of operation to perform on one or more operands. There are arithmetic, comparison, text, and reference operators.

infix-operator:
: | , | space | ^ | * | / | + | - | & | = | <> | < | <= | > | >=

postfix-operator:
%

prefix-operator:
-

The operators permitted in expression are:

Operators

Family

Operator

Description

Precedence

Reference operators

:

Binary range operator, which takes two cell reference3.17.2.3) operands, and results in one reference to the cells inclusive of, and between, those references. [Example: SUM(B5:C15), which references 11 cells. end example]

highest

,

Binary union operator, which takes two cell reference3.17.2.3) operands, and results in one reference to all those, possibly non-contiguous, cells. [Example: SUM((B5:B15,D5:D15))), which references 22 cells, 11 from column B, and 11 from column D. The grouping parentheses are necessary to indicate that the comma is an operator rather than a punctuator separating two arguments. end example]

space

Binary intersection operator, which takes two cell reference3.17.2.3) operands, and results in one reference to those, possibly non-contiguous, cells that are common. If the intersection is empty, the result is #NULL!. [Example: COUNT((B1:C1) (C1:D1)), which results in a reference to C1, while COUNT((B1:D1) (B1,D1)) results in a single reference to B1 and D1.

end example]

Arithmetic operators

-

Unary minus

 

%

Percentage (unary postfix), which divides its operand by 100. [Example: 10.5%, which results in 0.105. end example]

 

^

Exponentiation

 

*

Multiplication

 

/

Division

+

Addition

 

-

Subtraction

Text operator

&

Text concatenation (Each of the two operands is converted to text, if necessary, before concatenation.)

 

Comparison operators

=

Equal-to

lowest

<>

Not-equal-to

<

Less-than

<=

Less-than or equal-to

>

Greater-than

>=

Greater-than-or-equal-to

 

expression can contain grouping parentheses to document the default precedence or to override it.

operators in expression having the same precedence associate left-to-right.

[Example: Given that cell E38 contains the value 4, and cell F38 contains the value 2, the formula

((-1+E38^2)*3-F38)/2

produces the result 21.5. end example]

The comparison operators yield TRUE for true and FALSE for false. An expression with value 0 tests logically false while one with any non-zero value tests true.

For any given operator in an expression, if only one operand is an error value, the result is that error value. If more than one operand has an error value and those error values are the same, the result is that error value. If more than one operand has an error value and those error values are not all the same, as to which of those error values is used as the result is unspecified.

It the semantics of an operator having a given operand are not specified by this Standard, the result is #VALUE!. [Example: "abc"+1 results in #VALUE!, and "abc"/0 results in #VALUE! rather than #DIV/0!. end example]