The Excel Operators perform actions on numeric values, text or cell references. There are four different types of Excel Operators. These are:
Each of these Operator types is discussed below.
The Excel Arithmetic Operators and the order in which they are evaluated are shown in the table below:
|%||The percent operator||1|
|^||The exponentiation operator||1|
|*||The multiplication operator||2|
|/||The division operator||2|
|+||The addition operator||3|
|-||The subtraction operator||3|
The table above shows that the percent and exponentiation operators have the greatest precedence, followed by the multiplication and division operators, and then the addition and subtraction operators. Therefore, when evaluating Excel Formulas that contain more than one arithmetic operator, the percent and exponentiation operators are evaluated first, followed by multiplication and division operators. Finally, the addition and subtraction operators are evaluated.
The order in which the arithmetic operators are evaluated makes a big difference to the result of an Excel formula. However, brackets can be used to force parts of a formula to be evaluated first. If a part of a formula is encased in brackets, the bracketed part of the formula takes precedence over all of the above listed operators. This is illustrated in the following examples:
The following formula uses the concatenation operator to combine the text strings "SMITH", ", " and "John"
The Excel Comparison Operators are used when defining conditions (for example when using the Excel If Function). These operators are listed in the table below:
|>=||Greater than or equal to|
|<=||Less than or equal to|
|<>||Not equal to|
The spreadsheets below show examples of the comparison operators used with the Excel If Function.
The Excel Reference Operators are used when referring to ranges within a spreadsheet. The reference operators are:
|:||Range operator (defines a reference to a range of cells)|
|,||Union operator (combines two references into a single reference)|
|(space)||Intersection operator (returns a reference to the intersection of two ranges)|
Cell C1 of the following spreadsheet shows the range operator, used to define the range A1-B3. The range is then provided to the Excel Sum Function, which adds together the values in cells A1-B3 and returns the value 21.
Cell C1 of the following spreadsheet shows the union operator, used to define a range made up of cells in the two ranges A1-A3 and A1-B1. The resulting range is then provided to the Excel Sum Function, which adds together the values in the combined range and returns the value 12.
(Note that the Excel Union Operator does not return a true mathematical union, as cell A1, which is included in both ranges A1-A3 and A1-B1 is counted twice in the sum calculation).
Cell C1 of the following spreadsheet shows the intersection operator, used to define a range made up of cells in the intersection of ranges A1-A3 and A1-B2. The resulting range (range A1-A2) is then provided to the Excel Sum Function, which adds together the values in the intersecting range and returns the value 4.
Further information on Excel Operators is provided on the Microsoft Office website.