Microsoft Excel

Operators in Formulas

An operator is the basic element of a formula, it is a symbol that represents an operation. Following table shows the Excel-supported operators.

SymbolOperator
+Addition
-Subtraction
/Division
*Multiplication
%Entering a percent sign after a number divides the number by 100 and formats the cell as percent.
& Text concatenation
^Exponentiation
=Logical comparison (equal to)
>Logical comparison (greater than)
<Logical comparison (less than)
>=Logical comparison (greater than or equal to)
<=Logical comparison (less than or equal to)
<>Logical comparison (not equal to)
: (colon)Range. Produces one reference to all the cells between two references. (Reference Operator)
, (comma)Union. Combines multiple cell or range references into one reference.  (Reference Operator)
(single space)Intersection. Produces one reference to cells common to two references. (Reference Operator)

Note: Reference operators work with cell references.

Examples

  • The following formula joins (concatenates) the two literal text strings (each enclosed in quotes) to produce a new text string: MS-Excel:
    ="MS-"&"Excel"
    
  • The next formula concatenates the contents of cell E1 with cell E2:
    =E1&E2
    

    MS Excel Concatenating Text

    Usually, concatenation is used with text, but concatenation works with values as well. For example, if cell E1 contains 123 and cell E2 contains 456, the preceding formula would return the value 123456.

  • The following formula uses the exponentiation operator to raise 6 to the third power, to produce a result of 64:
    =2^6
    
  • A more useful form of the preceding formula uses a cell reference instead of the literal value.:
    =E1^E2
    

    Referencing Cells

  • This formula returns the cube root of 216 (which is 6):
    =216^(1/3)
    
  • The next formula returns TRUE if the value in cell E1 is less than the value in cell E2. Otherwise, it returns FALSE:
    =E1<E2
    
    

    Logical comparison operators also work with text. If E1 contains ABCDEF and E2 contains GHAJKL, the formula returns TRUE because ABCDEF comes before GHAJKL in alphabetical order:

  • The following formula returns TRUE if the value in cell E1 is less than or equal to the value in cell E2. Otherwise, it returns FALSE:
    =E1<=E2
    

  • The next formula returns TRUE if the value in cell E1 does not equal the value in cell E2. Otherwise, it returns FALSE:
    =E1<>E2
    

  • Excel doesn't have logical AND or OR operators, for example, this formula returns TRUE if cell E1 contains either 100 or 1000:
    
    =OR(E1=100,E1=1000)
    

    This last formula returns TRUE only if both cell E1 and cell E2 contain values less than 100:

    =AND(E1<100,E2<100)
    
by BrainBellupdated
Advertisement: