Mathematical and Logical operators in Excel

In this article, we will learn about operators and how to use them in formulas in excel.

For instance, we have a list of values and we need to perform a criteria on the list using the operator in formulas. For this, we will be learning about different types of operators. We will be using different function operations depending on the ease of the formula.

The following operators will be useful in excel. Let's study about them first. There are two types of operators.

  1. Logical operators
  2. Mathematical operators

These operators are used with or without a formula to compare the value of same data type like text matching text or number matching number. Most operators are used with absolute or relative cell references.

Logical operators

  1. Equal to ( = )
  2. Not equal to ( <> )
  3. Less than ( < )
  4. Less than or equal to ( <= )
  5. Greater than ( > )
  6. Greater than or equal to ( >= )

The above mentioned operators are quite well known and widely used. See the below snapshot for the usage of the logical operators operators 

See the table below for more understanding.

Operator value Formula Operation (Result)
= 10 = B2 = 10 TRUE
<> 20 = B3 <> 20 FALSE
< 30 = B4 < 50 TRUE
<= 20 = B5 <= 15 FALSE
> 10 = B6 > 9 TRUE
>= 0 = B7 >= 0 TRUE

Notes: 

  1. The first equal to ( = ) sign is not only the logical operator but it's a necessary operator which marks the starting of the formula.
  2. TRUE and FALSE are the default outcome in excel, which indicates the result of the operation performed.
  3. Use the above operation in other excel formulas to extract values or return logic value like TRUE or FALSE.
  4. These operators must match the data type on both sides of the operator, or else the operator returns error or the wrong outcome. 
  5. Values provided to the function can be a cell reference or named ranges or directly to the formula.

Mathematical operators

  1. Addition ( + )
  2. Subtraction ( - )
  3. Multiplication ( * )
  4. Division ( / )
  5. Exponentiation ( ^ )
  6. Parenthesis ( ( ) )
  7. Concatenation ( & )
  8. Range ( : )

The above mentioned operators are quite well known and widely used. See the below snapshot for the usage of the logical operators.

See the below table.

Operator Formula Result
+ = E2 + E3 30
- = E4 - E3 10
* = E6 * E7 0
/ = E5 / E6 2
^ = E4 ^ 2 900
() = ( E3 + E5 ) / 2 20
& = "B" & E6 B10
: = MAX ( E2:E7 )

Wait!!!. What if there is more than 1 operator in a formula? What operation would excel be performing first? That's cool. For this, Excel already has predefined order of operations. Take a look at the order list below.

  1. Parentheses ( ( ) )
  2. Exponentiation ( ^ )
  3. Multiplication / division ( * / / )
  4. Addition / Subtraction ( + / - )
  5. Concatenation ( & )
  6. Logical operators ( > < = <> )

Hope you understood how to use Mathematical and Logical operations in Excel. Explore more articles on Excel operators in formulas here. Please feel free to state your query or feedback for the above article. We will assist you.

Related Articles

Vlookup Top 5 Values with Duplicate Values Using INDEX-MATCH in Excel

VLOOKUP Multiple Values

VLOOKUP with Dynamic Col Index

Partial match with VLOOKUP function

Popular Articles

If with wildcards

Vlookup by date

Convert Inches To Feet and Inches in Excel 2016

Join first and last name in excel

Count cells which match either A or B

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube