06-How Excel Calculates

One of the fundamental things that you must know about Formulas and Functions is the method in which Excel performs calculations. We will not go into any great detail in this, but there are some basics all Excel users need to know.

Operators that Excel Recognises

Remember that for Excel to be able to perform a calculation you must obey 2 golden rules:

1. Begin your formula with an '=' sign, and 

2. Do NOT use spaces in your formula.

Example:

To add the contents of cells A1, B1 and C1, type

=A1+B1+C1

Note: Begins with an equals sign and that the formula statement has no spaces in it- 

Try this yourself:

- as shown above

- again but leaving out the 'equals to' sign

- again but insert some spaces as you retype the formula above

What did you find?

Do you understand what happened?

Arithmetic operators

To perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results, use the following arithmetic operators.

Comparison operators

You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value, either TRUE or FALSE.

Reference operators

Combine ranges of cells for calculations with the following operators.

Hierarchy of Mathematical Operation

When Excel performs a calculation it does so in the following order:

 
• Exponentiation

 
• Multiplication and Division

 
• Subtraction and Addition 

Exponentiation is "the operation of raising one quantity to the power of another".

Try working out the answers first and then confirm your results by typing them into empty cells on your spreadsheet:

=5+5*5+5+5 

 
=(5+5)*5+5+5 

 
=(5+5)*(5+5)+5 

You should get the following answers

 =5+5*5+5+5 will result in 40,

because the 5*5 would be evaluated first, followed by the rest

=(5+5)*5+5+5 will result in 60,

because (5+5) would be evaluated first. The result [10] would then be multiplied by 5 [=50] followed by 5+5

 
=(5+5)*(5+5)+5 will result in 105,

because (5+5) would be evaluated first. The result [10] would then be multiplied by (5+5) [=100] followed by +5.

As you can see from the examples above:

We can manipulate any formula we want, simply by placing the parenthesis ( ) in the appropriate places. This forces Excel to calculate elements in the order we choose

Test your knowledge!