# Different Types and Usages of Operators in Excel Formulas

Operators are special symbols that perform specific operations between operands, and then return a result. In our everyday life we basically use four mathematical operators, i.e. + (addition), - (subtraction), * (multiplication) and / (division). In Microsoft Excel we have many other operators to make our work easier.

Operators in Microsoft Excel are divided in four simple types or classes to make it easy to understand and remember. This classes are **Mathematical or Arithmetic Operators**, **Logical or Comparison Operators**, **Reference Operators** and **Text Concatenation Operator**. Below I have described the function and usages of this operators with examples. Say we have 10 in A1 field and 10 B1 field.

### Mathematical or Arithmetic Operators

As the name suggest this operators are used to do mathematical calculation, whether it is addition or subtraction. In real life we have only 4 (better to say 2; + & -) mathematical operators, i.e. +, -, * and /. In Excel two more mathematical operators are created using this basic operators. Operators like % (Percent; is not a real operator though) and ^ (Exponentiation) are used in Excel. The table below will help you understand the usages of this operators.

## Example and Usages of Mathematical Operators in MS Excel

Symbol | Operator | Usages | Usages | Result |
---|---|---|---|---|

+ | Addition | =A1+B1 | 10+10 | 20 |

– | Subtraction | =A1-B1 | 10-10 | 0 |

/ | Division | =A1*B1 | 10*10 | 100 |

* | Multiplication | =A1/B1 | 10/10 | 1 |

% | Percent | =(A1+B1)% | (10+10)% | 0.2 |

^ | Exponentiation | =A1^4 | 10^4 | 10000 |

### Logical or Comparison Operators

This operators are used to do a logical test between tow or more numbers or strings. This operators returns a value of either **True** or **False**. **Three** symbols =, > and < combined with each other makes **six** logical operators. Basic use of logical test is like “If he is earns more than $10,000 then he has to pay government taxes”. Lets do it the Excel way; say we have 10 in A1 field and 5 B1 field.

## Example and Usages of Logical Operators in MS Excel

Symbol | Operator | Usages | Usages | Result |
---|---|---|---|---|

= | Equal to | =A1=B1 | 10=5 | FALSE |

> | Greater than | =A1>B1 | 10>5 | TRUE |

< | Less than | =A1 | 10<5 | FALSE |

>= | Greater than or equal to | =A1>=B1 | 10>=5 | TRUE |

<= | Less than or equal to | =A1<=B1 | 10<=5 | FALSE |

<> | Not equal to | =A1<>B1 | 10<>5 | TRUE |

### Reference Operators

Excel supports another class of operators known as reference operators, it basically combine ranges of cells for calculations. Here we have three symbols that represents three operators. **:** (colon), **,** (comma), and (single blank space) are used for this.

## Example and Usages of Reference Operators in MS Excel

Symbol | Operator | Usages | Result |
---|---|---|---|

: (colon) | Range operator, which produces one reference to all the cells between two references, including the two references. | =A1:B4 | This will refer to all (8 in this case) cells from A1 to B4 range. |

, (comma) | Union operator, which combines multiple references into one reference | =A1:B4,D1:E4 | This will refer to the combination of cells from A1 to B4 and D1 to E4 |

(single space) | Intersection operator, which produces on reference to cells common to the two references | (B7:D7 C6:C8) | This will return the value of the cell C7 as this is the intersection of this two ranges. |

### Text Concatenation Operator

This operator is made for joining two more cells and return a single String in Excel and is represented by a **Single Blank Space**. Though concatenation is used with text, it can also be used to work with numbers. If the source and the results are numeric then the result can be used as a number, though it is technically a text string.

For example say we have **25** in cell A1 and **60** in cell A2 and **Home** in cell A3, then **=A1&A2** will return **2560**(remember the result is a string but excel can use it as a number too), and **=A1&A2&A3** will return **2560Home**

### Related Posts

- Prevent Save As in a Excel Work Book using VBA Macro 2 Comments | Dec 4, 2010
- Calculating Straight Line Depreciation in MS Excel No Comments | Mar 12, 2011
- Creating an Accelerated Depreciation Schedule 1 Comment | Mar 14, 2011
- Change Excel’s default Subtotal format No Comments | Jun 15, 2011

#### About The Author

##### Rabin Biswas

Blogging and PHP programming are what make me feel alive. Other than that party with friends and music.