How To Create A Formula Using Arithmetic Operators In Excel

Introduction to Operators in Excel

Operators along with other elements like cell references, functions, values and text elements constitute the formulas in Excel and enable them to give the results for our queries. The role of the Operators is not readily recognized and is often taken for granted, as most of them are represented by basic signs and symbols that the users of Excel have dealt with in their maths lessons from early school days.

As we all know, a formula needs a code to be input into a cell. When the code is put into action, it performs the calculations and gives a result as per the code fed in. A function is a pre-defined, built-in formula available in Excel. To use the Excel formulas and functions, we need operators. Formulas begin with an equal sign '=' and the operators along with other elements like cell references, functions, values and text elements constitute the formulas and enable them to give us the results.

Operators mostly constitute the mathematical or logical symbols of the tasks like addition, multiplication and similar ones that are to be performed.

Please browse our Articles section containing various articles where you will find discussions about functions and formulas and naturally they contain operators!

How Operators are used in the formulas?

Excel has hundreds of built-in functions and can support many more formulas as well. There are many mathematical operators, but the Operators in Excel are those that are recognized and allowed by Excel.

It is pertinent to know the various operators, how to use them and the sequence they have to be used if multiple operators are to be used in a formula.

Broadly the Operators in Excel can be divided into categorized as (a) Mathematical Operators, (b) Logical Operators, (c) Text Operators and (d) Operators to work with ranges. Let us discuss them one by one. Some discussions speak about more operators like those that support the operations of array and table functions, but this article restricts its scope to the aforesaid categories.

Mathematical Operators

As the name indicates, mathematical or arithmetical operators include those fundamental operators that help to perform the basic mathematical activities, namely,
addition (+), subtraction (-), multiplication (represented by *) and division represented by a slash ( /), besides, the percentage (%) and exponentiation represented by the caret (^) symbol.
Refer to the table below :

Logical Operators

The next set of operators in Excel is the Logical Operators. They are also called as Comparison operators. They help in comparison like 'greater than', 'less than' and so on. They are used in formulas from which the expectation is mostly the result of "True" or "False". They can be used for numbers and also for text in some cases.

Within a formula these operators can be used more than one time, depending on the requirement and following the recommended precedence.

The six logical operators are:
= Logical comparison (equal to)
<> Logical comparison (not equal to)
> Logical comparison (greater than)
< Logical comparison (less than)
>= Logical comparison (greater than or equal to)
<= Logical comparison (less than or equal to)

Text Operators

The next operator in Excel is the Text Operator. Text operators are not many and primarily help when we have to concatenate or combine the text items that appear in different cells in a worksheet. This operator is the ampersand (&) character.

We can concatenate multiple parts of the text in a single unit. By using the & (ampersand) we can concatenate values from multiple cells and get the combined text in one cell.
For example, if four cells have the value 9,7,6,5 in them, concatenating them as (9&7&6&5) gives us the result 9765. Similarly, when we combine the cells containing Australia, cricket, players, by using "Australia"& "Cricket"& "Players", we get the text "Australia cricket players" in the cell where we have entered the formula.

A point to note here is that operators only function as operators when they are used in formulas. If we need to use one of them inside a formula, but as a simple text and not as an operator, then they must be enclosed within quotes.

For instance, in the following: = C1 & " & " & D1

If there is text like (Jack and Jill) in cells C1 and D1, then the result of this formula would be the following: Jack & Jill. You can see that here, the second ampersand "&" is not functioning as an operator but as a text by itself along with Jack and Jill. The first and last "&" ampersand act as operators.

Operators to work with ranges

There are three key Operators in Excel that help to work with ranges.

Range Operator : (colon) This helps to work with ranges and includes formula like =SUM(E6: F19)

Union operator , (comma) Union operator helps to combines multiple references and arrive at a single one. Example, =SUM(C23,G9:K39,D33) .
The operator for the 'Union' can be either 'semicolon' or 'comma' ( ; or , ).

Intersection operator (space) Intersection operator that produces one reference to cells in common with two references =SUM(C3: C6 C3: E6).

Reference Operators in excel — Reference Operators

You most often use the comparison operators with the IF function when building more complex formulas that perform one type of operation when the IF condition is TRUE and another when it is FALSE. You use the concatenating operator (&) when you need to join text entries that come to you entered in separate cells but that need to be entered in single cells (like the first and last names in separate columns).

List of Operators for easy reference

The Sequence of using Operators in Excel

Remember BODMAS from school days? That was the simple rule which governed our approach to calculate results when there were many numbers connected by different operators. To refresh, the sequence of BODMAS is Brackets, Orders or powers, Division, Multiplication, Addition and finally subtraction. If not followed in proper order, the results would be incorrect. In BODMAS, Brackets can be ( or [ or {.
BODMAS is also called PEDMAS (or PEMDAS).
The order of operations as per PEMDAS is Parentheses, Exponents, Multiplication and Division, and Addition and Subtraction.

While using multiple operators in an Excel formula, naturally confusion would arise on which one to use first and about the sequence in which the others should follow. Naturally, in a long formula, which contains some elements in between within parenthesis, those will be calculated and the value will be arrived at and this value will now be considered along with other elements in the formula.

Why following the sequence is important? Where there are no brackets but many operators are used, Excel should have some standards to ensure that there is uniformity in the results as well as the thought process. The below table has the Precedence of operators tabulated.

Operators Precedence table in excel — Operators Precedence table

Note: If a formula contains both a multiplication operator and the division operator, Excel evaluates the operators from right to left. This is to say that items having the same precedence order like addition and subtraction OR multiplication and division will follow the sequence of left to right rule.

By using parentheses, we can choose to define the precedence and ignore the in-built Excel's order and expressions. Items with parenthesis will be calculated first. For example in the formula (B3+B4)*B5, the additional action for the components within parentheses will be done first and the result will be multiplied by the component in B5.
If we don't use the parentheses then the formula would follow the precedence rules built-in Excel and the answer may differ. In this case, without parenthesis, B3+B4*B5, the multiplication with higher precedence will be done first, before B3 is added to the result of the multiplication.

We can also nest parentheses within formulas. Nesting is the term used for putting items inside other parentheses. Excel would step up step calculate the most deeply nested expressions first—and then works its way backward, and finally does the formula for the outermost parenthesis.

Note:
In Excel, there are mathematical, comparison, logical, and reference operators. The symbol is called an Operator and the Value is called an Operand. The operands can be values, cell references, names, labels, and functions.

We frequently update our collection of informative videos available in the YOUTUBE.
Please view them and refresh your Excel knowledge!