Excel Tutorial
Calculation Operators and Calculation Order in Excel
Calculation Operators
| Arithmetical Operators | |
|---|---|
| + (plus sign) | Addition (5+12) |
| - (minus sign) | Subtraction (7-3) |
| * (asterisk) | Multiplication (7*8) |
| / (forward slash) | Division (15/5) |
| % (percent sign) | Percent (50%) |
| ^ (caret) | Exponential (5^3) |
| Comparison Operators | |
| = (equal sign) | Equal to (A1=B1) |
| > (greater than sign) | Greater than (A1>B1) |
| < (less than sign) | Less than (A1<B1) |
| >= (greater than or equal to sign) | Greater than or equal to (A1>=B1) |
| <= (less than or equal to sign) | Less than or equal to (A1<=B1) |
| <> (not equal to sign) | Not equal to (A1<>B1) |
| Text Operators | |
| & (ampersand) | Connects, or concatenates, two values to produce one continuous text value ("North"&"wind") |
| Reference Operators | |
| : (colon) | Range operator, which produces one reference to all the cells between two references, including the two references (B3:B10) |
| , (comma) | Union operator, which combines multiple references into one reference (SUM(B5:B15,D5:D15)) |
| (space) | Intersection operator, which produces on reference to cells common to the two references (B7:D7 C6:C8) |
Calculation Order
A formula in Excel always begins with an equal sign (=). The equal sign tells Excel that the succeeding characters constitute a formula. Following the equal sign are the elements to be calculated (the operands), which are separated by calculation operators. Excel calculates the formula from left to right, according to a specific order for each operator in the formula.
Operator precedence
If you combine several operators in a single formula, Excel performs the operations in the order shown in the following table. If a formula contains operators with the same precedence - for example, if a formula contains both a multiplication and division operator- Excel evaluates the operators from left to right.| : (colon)(single Space) | Reference Operators |
| (single Space) | |
| , (comma) | |
| - | Negation (as in -7) |
| % | Percent |
| ^ | Exponential |
| * and / | Multiplication and Division |
| + and - | Addition and Subtraction |
| & | Connects two strings of text (concatenation) |
| = < > <= >= <> | Comparison |
Use of Parenthesis
To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example, the following formula produces 11 because Excel calculates multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result. =5+2*3
In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to produce 21. =(5+2)*3
In the example below, the parentheses around the first part of the formula force Excel to calculate B4+25 first and then divide the result by the sum of the values in cells D5, E5, and F5. =(B4+25)/SUM(D5:F5)