Introduction To Calculations In Excel.


Performing calculations is one of the main uses of spreadsheets and Excel allows you to build quite complex formulae using arithmetic operators, but it also has a myriad of predefined functions for performing statistical analysis, conditional functions, scientific and financial calculations.

A function is still a calculation, but it is simply a formula which is predefined in Excel.

Formulae in Excel are made up of a number of different elements that when combined in a specific way ensure the result of the formula is accurate.

See below for an example of the building blocks of s simple formula and also then a more complex one.

Each of the elements or parts of a formula are explained below in more detail.

The order of calculations in Excel.

In Excel, formulae are calculated the left to the right according to a strict precedence.  The precedence can be changed by enclosing sections of the formula in brackets ().  These bracketed sections are calculated first, then the result is held as a sub total for subsequent calculations.  See below for a table to simply explain the order of precedence of formulae. A great acromyn to remember the order of calculations is BEDMAS

Brackets

Exponents

Division

Multiplication

Addition

Subtraction

If you are unsure if your formula is giving the correct answer, always test it using some simple numbers and by knowing the expected result, in this way you can easily verify your formula is working correctly.  If it is not working out right there will be an error in your sequence of it or the formula has simply been typed incorrectly.

Here’s a simple 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

See how this changes things?

 

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts