Excel Operations

Excel Operators

Although the Excel Operators are not, strictly speaking, Excel Functions, they are frequently used with Excel Functions and as a part of Excel Formulas. Therefore we have provided a brief explanation of Excel Operators Bellow:

Excel Operators

            Excel Mathematical Operators - used to perform basic Mathematical operations, such as addition, subtraction, multiplication, division and raising to a power,

or

the & Excel Operator is used to concatenate text strings

There are four different types of Excel Operators. These are:

Arithmetic Operators

Text Operator

Comparison Operators

Reference Operators

Each of these Operator types is discussed below.

Arithmetic Operators

The Excel Arithmetic Operators and the order in which they are evaluated are shown in the table below:

Operator	Description	Precedence (1=top; 3=bottom)
%	The percent operator	1
^	The exponentiation operator	1
*	The multiplication operator	2
/	The division operator	2
+	The addition operator	3
-	The subtraction operator	3

Precedence of Arithmetic Operators

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:

Arithmetic Operator Examples

Formulas: A 1 =1+2*3+4 2 =(1+2)*(3+4) 3 =3^2*2 4 =3^(2*2) 5 =20%*500

Results: A 1 11 2 21 3 18 4 81 5 100

Excel Text Operator

The Excel Concatenation Operator (denoted by the & symbol) joins together text strings, to make a further, single text string.

Concatenation Operator Example

The following formula uses the concatenation operator to combine the text strings "SMITH", ", " and "John"

Formulas: A 1 ="SMITH" & ", " & "John"

Results: A 1 SMITH, John

Excel Comparison Operators

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:

Operator	Description
=	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<>	Not equal to

Comparison Operator Examples

The spreadsheets below show examples of the comparison operators used with the Excel If Function.

Formulas: A B C 1 1 2 =IF( A1=B1, "equal", "not equal" ) 2 =IF( A1>B1, "A", "B" ) 3 =IF( A1<B1, "A", "B" ) 4 =IF( A1>=B1, "A", "B" ) 5 =IF( A1<=B1, "A", "B" ) 6 =IF( A1<>B1, "not equal", "equal" )

Results: A B C 1 1 2 not equal 2 B 3 A 4 B 5 A 6 not equal

Excel Reference Operators

The Excel Reference Operators are used when referring to ranges within a spreadsheet. The reference operators are:

Operator	Description
:	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)

Reference Operator Examples

Example 1 - Excel Range Operator

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.

	A	B	C
1	1	2	=SUM(A1:B3)
2	3	4
3	5	6

Example 2 - Excel Union Operator

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

Example 3 - Excel Intersection Operator

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.

Click On Main Menu