The most frequently used operators in formulas or functions

With Excel you can have several operators in a function to deal with data. Operators are symbols that Excel identifies with arithmetic operations and the link between two arguments. *

In the chart below we can see the operators most frequently used.

 

OPERATOR SYMBOL
OPERATION PERFORMED
+ SUM
- SUBTRACTION
* MULTIPLICATION
/ DIVISION
^ EXPONENTIATION
& UNION/CONCATENATION
= Comparison EQUAL TO
> Comparison GREATER THAN
< Comparison LESS THAN
>= Comparison GREATER THAN/EQUAL TO
<= Comparison LESS THAN/EQUAL TO
<> Comparison DIFFERENT

In a formula or function you can use as many operators as you need, baring always in mind that operators always refer to arguments. You can create really complex formulas. Let's see an example.

= ((SUM(A1:A7)*SUM(B1:B7)) / (SUM(C1:C7)*SUM(D1:D7)))=(F8*SUM(G1:G5))

 

 

Operators priority

We have seen that a formula can be very complex, using many operators. Excel, as any other mathematical operator, has certain rules to establish what operations to do first so the result is the correct one.

In the following chart we are showing priorities stablished by Excel.

 

OPERATOR SYMBOL
OPERATION PERFORMED
PRIORITY
^ EXPONENTIATION
1
* MULTIPLICATION
2
/ DIVISION
2
+ SUM
3
- SUBTRACTION
3
& UNION/CONCATENATION
4
= Comparison EQUAL TO
5
> Comparison GREATER THAN
5
< Comparison LESS THAN
5
>= Comparison GREATER THAN/EQUAL TO
5
<= Comparison LESS THAN/EQUAL TO
5
<> Comparison DIFFERENT
5

Apart from this priorities chart, the top priority, that is the operation calculated first, is that one in brackets.

Let's see how would Excel resolve the formula we saw as an example:

We know there are 10 operations:

- 5 SUMS

- 3 MULTIPLICATIONS

- 1 DIVISION

- 1 COMPARISON

Excel will resolve first each of SUM operation separate, then it will do the MULTIPLICATION operations, next it will do the DIVISION operation, and finally the COMPARISON.

 



 
Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited.
November-2005.