Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Collapse]WPF Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Common Concepts
 [Expand]MVVM Framework
 [Collapse]Controls and Libraries
  [Expand]Windows and Utility Controls
   Dialogs, Notifications and Panels
   Reporting
  [Expand]Data Grid
  [Expand]Ribbon, Bars and Menu
  [Expand]Charts Suite
  [Expand]Pivot Grid
  [Expand]Scheduler
  [Collapse]Spreadsheet
    Product Structure
   [Expand]Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Collapse]Formulas
    [Expand]Functions
     Operators
     Array Formulas
     Calculation
     Formula Engine
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Printing
    Protection
    Find and Replace
   [Expand]Visual Elements
    Services
    Themes and Templates
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Tree List
  [Expand]Gauge Controls
  [Expand]Map Control
  [Expand]Layout Management
  [Expand]Windows Modern UI
  [Expand]Printing-Exporting
  [Expand]Data Editors
  [Expand]Navigation Controls
  [Expand]Spell Checker
  [Expand]Property Grid
  [Expand]PDF Viewer
  [Expand]TreeMap Control
  [Expand]Diagram Control
  [Expand]Scheduler (legacy)
 [Expand]Scaffolding Wizard
 [Expand]Localization
  Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Operators

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.

Expanded 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.

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.

Expanded 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.

Expanded 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.

Expanded 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.

How would you rate this topic?​​​​​​​