Operators in MS Excel

Operators in MS Excel

There are four different types of Operators in Excel: Arithmetic, Comparison, Text Concatenation and Reference.

Arithmetic Operators
To perform basic mathematical operations, such as addition, subtraction, multiplication, or division; combine numbers; and produce numeric results,use the following arithmetic operators.

Arithmetic Operator
Meaning
Operation Performed
Output
+ (plus sign)
Addition
3+3
6
- (minus sign)
Subtraction
3-1
2
-
Negation
-1
-1
* (asterisk)
Multiplication
3*3
9
/ (forward slash)
Divison
3/2
1.5
% (percent sign)
Percent
20%
.20
^ (caret)
Exponentiation
3^2
9


Comparison Operators
You can compare two values with the following 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
<= (greater than or equal to sign)
Less than or Equal to
A1<=B1
<> (not equal to sign)
Not Equal to
A1<>B1

Text Concatenation Operator
Use the ampersand (&) to concatenate (join) one or more text strings to produce a single piece of text.

Text Operator
Meaning
Example
& (ampersand)
Connects, or concatenates, two values to produce one continuous text value
"North" & "Wind"
results in "Northwind"
Reference Operators in MS Excel
Combine ranges of cells for calculations with the following 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)
(space)
Intersection operator, which produces
one reference to cells common to the
two references
B7:D7 C6:C8

Note: AND,OR etc are logical functions not logical operators as they return value on passing arguments.


BACK TO CHAPTER 1