Operators
- 5 minutes to read
There are four different types of binary operators: arithmetic, boolean, text concatenation, and reference. Unary operators are represented by unary minus, unary plus and percent operators.
Arithmetic Operators
Before performing a specific action, each arithmetic operator evaluates the operands or converts the operands to numeric values.
If an operand is a string that is not surrounded by quotes, the operator tries to do the following:
- recognize the operand as the defined name, the cell or the array reference, and get the value
- use the System.Double.TryParse method to convert a string to a numeric value
- recognize the operand as a percentage value and then convert it to numeric
- recognize the operand as a boolean value and return 0 for FALSE and 1 for TRUE
If none of these methods succeed, the Name error occurs, which displays the “#NAME?” string.
If an operand is a string surrounded by quotes, no assumptions are made about the defined names or references. The operator tries to use methods listed previously to convert the operand to numeric. Also, an attempt is made to recognize the operand as a date or time value, and get the numeric representation of time. When the operand is a quoted string and all attempts fail, the Invalid Value in Function error occurs, which displays the “#VALUE!” string. This error also appears when your formula operates with blank cells that are not actually empty (e.g., when an empty string “” is assigned to any cell in a formula). To avoid this error and specify an empty value for a cell, assign the CellRange.Value property to null or CellValue.Empty.
Arithmetic operators are listed in the following table.
| Arithmetic Operator | Action and Specifics |
|---|---|
| + (plus sign) - binary | Values are added together. |
| + (plus sign) - unary | Evaluates the operand if it is a reference, otherwise converts it to numeric. |
| - (minus sign) - binary | Subtracts the value of the right operand from the value of the left operand. |
| - (minus sign) - unary | Negates the value of the operand. |
| * (asterisk) | Multiplies the value of the left operand by the value of the right operand. |
| / (forward slash) | Divides the value of the left operand by the value of the right operand. If the right value evaluates to zero, a Division by Zero error occurs and the “#DIV/0!” string is displayed. |
| ^ (caret) | The exponentiation operation. Returns the value of the first operand raised to the power of the second operand value. The operator is equivalent to the POWER() function. |
Boolean Operators
Boolean operators are used to compare the values of its operands.
If an operand is a string that is not surrounded by quotes, the operator tries to do the following:
- recognize the operand as the defined name, the cell or the array reference, and get the value
- use the System.Double.TryParse method to convert a string to a numeric value
- recognize the operand as a percentage value and then convert it to numeric
- recognize the operand as a boolean value and return 0 for FALSE and 1 for TRUE
If none of the methods succeed, the Name error occurs, which displays the “#NAME?” string. If an operand is a string surrounded by quotes, it is considered a string and a comparison is performed. Strings are compared based on a language-specific sort order. The result of this comparison is a Boolean value (TRUE or FALSE).
Boolean operators are listed in the following table.
| Boolean Operator | Action and Specifics |
|---|---|
| = (equal sign) | The values are equal. |
| > (greater than sign) | The left value is greater than the right value. |
| < (less than sign) | The left value is less than the right value. |
| >= (greater than sign and equal sign) | The left value is greater than or equal to the right value. |
| <= (less than sign and equal sign) | The left value is less than or equal to the right value. |
| <> (less than sign and greater than sign) | The values are not equal. |
Text Concatenation Operator
The & (ampersand) operator is used to concatenate two operands to produce a single string of text. Before performing concatenation, each operand is evaluated to produce a text value. If the operand is recognized as the defined name, the cell, or array reference, its value is obtained and converted to a string.
For example, the result of the formula =(5=5)&(5=9) will be the text string TRUEFALSE.
Reference Operators
Reference operators combine cell ranges for subsequent calculations.
Reference operators are listed in the following table.
| Reference Operator | Action and Specifics |
|---|---|
| : (colon) | The Cell Range operator. Produces a reference to all cells located between the cells specified as operands. The cells that are operands are included in the resulting reference. |
| , (comma) | The Union operator. Combines references to produce a single reference. |
| (space) | The Intersection operator. Produces a reference to cells that are included in both references. |