About calculation operators
Operators specify the type of calculation that you want to perform on the elements of a formula. The Spreadsheet Component includes four types of calculation operators: arithmetic, comparison, text concatenation, and reference. Operators calculate in a specific order.
Use the following arithmetic operators to perform basic mathematical operations, such as addition, subtraction, or multiplication; to combine numbers; and to produce numeric results.
| Arithmetic operator |
Meaning |
Example |
|---|---|---|
| + (plus sign) | Addition | 3+3 |
| – (minus sign) | Subtraction Negation |
3–1 –1 |
| * (asterisk) | Multiplication | 3*3 |
| / (slash mark) | Division | 3/3 |
| % (percent sign) | Percent | 20% |
| ^ (caret) | Exponentiation | 3^2 (the same as 3*3) |
To compare two values, use the following comparison operators. When two values are compared by using these operators, the result is a logical value, either TRUE or FALSE.
| Comparison operator |
Meaning |
Example |
|---|---|---|
| = (equal sign) | Equal to | A1=B1 |
| > (greater than sign) | Greater than | A1>B1 |
| < (less than sign) | Less than | A1<B1 |
| >= (greater than or equal to sign) | Greater than or equal to | A1>=B1 |
| <= (less than or equal to sign) | Less than or equal to | A1<=B1 |
| <> (not equal to sign) | Not equal to | A1<>B1 |
To join, or concatenate, one or more text strings to produce a single piece of text, use the ampersand (&) operator.
| Text operator |
Meaning |
Example |
|---|---|---|
| & (ampersand) | Concatenates | "North" & "wind" produces "Northwind" |
To combine ranges of cells for calculations, use the following reference operators.
| Reference operator |
Meaning |
Example |
|---|---|---|
| : (colon) | Range operator, which produces one reference to all the cells between two references, including the two references | B5:B15 |
| , (comma) | Union operator, which combines multiple references into one reference | SUM(B5:B15,D5:D15) |
| (single space) | Intersection operator, which produces one reference to cells that are common to two references | SUM(B5:B15 A7:D7)
In this example, cell B7 is common to both ranges. |
Order in which operators calculate
If you combine several operators in a single formula, the Spreadsheet Component performs 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 — the Spreadsheet Component evaluates the operators from left to right. To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first.
| Operator | Description |
|---|---|
| : (colon)
(single space) , (comma) |
Reference operators |
| – | Negation (as in –1) |
| % | Percent |
| ^ | Exponentiation |
| * and / | Multiplication and division |
| + and – | Addition and subtraction |
| & | Concatenation (joins two strings of text) |
| = < > <= >= <> | Comparison |