What is a Mathematical operator?
This page will guide you through using Excel to create basic mathematical operations. This is very similar to creating a function or formula except that we are not using the function's operative such as (=Sum) or (=Average) or any of the other function operative.
If we wanted the sub-total for a given range we can use the function operative (SUM):
=Sum(A1:A10)
however if we wanted to write this the super long manual way (but still using cell references), in plain mathematical operatives it would be:
=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10
We could also write out, instead of the cell references, with the values found within these cells:
(lets assume A1's value is 1, A10's Value is 10 and the numbers between count up between the two)
=1+2+3+4+5+6+7+8+9+10
To have Excel calculate the value of a mathematical equation you must start by adding the equals sign (=) as shown above, after this we may use any of the mathematical operatives displayed below to calculate the values we need:
+ (plus sign) | Addition | =3+3 |
- (minus Sign) | Subtraction | =3-1 |
*(asterisk) | Multiplication | =3*3 |
/ (Forward Slash) | Division | =3/3 |
% (Percent Sign) | Percent | =100*50% |
^ (caret) | Exponentiation (to the power of) | =3^2 |
Using these operators we can create almost any function (in long hand) we wish to. We can also replace the values (like I have used in my example) with cell references that will update the whole function when I update a value in their original cell. However you may have to consider the order of operations. As certain functions will mean you have to use parenthesis (brackets) to tell Excel which part of the function to calculate first.
The Order of Operations
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.
Operator | Description |
: (colon)(single space), (comma) | Reference operators |
– | Negation (as in –1) |
% | Percent |
^ | Exponentiation |
* and / | Multiplication and division |
+ and – | Addition and subtraction |
& | Connects two strings of text (concatenation) |
= | Comparison |
Use of Parentheses (brackets)
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.
11=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.
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)
You can watch this video on Operator order in Excel to learn more