OzGrid

Lesson 25 - Excel's Calculations

< Back to Search results

 Category: [Excel]  Demo Available 

EXCEL CALCULATIONS

Lesson 25 - Excel Calculations/How Excel Calculates/The Order of Calculations

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.

 

The main function of Excel is obviously the number crunching side of things and a good spreadsheet is one that returns accurate results 100% of the time. So whilst we may have a spreadsheet that looks very pretty and is formatted to make it look a million dollars, it is the guts of the spreadsheet, or the nuts and bolts, that make it either a workable spreadsheet or an unworkable spreadsheet, not the visual appeal.

Operators that Excel Recognizes

 

The text below is from the Excel help file:

 

Calculation operators in formulas

Operators specify the type of calculation that you want to perform on the elements of a formula. Microsoft Excel includes four different types of calculation operators: arithmetic, comparison, text, and reference.

Arithmetic operators

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

Arithmetic operator Meaning Example
+ (plus sign) Addition 3+3
– (minus sign) SubtractionNegation 3–1–1
* (asterisk) Multiplication 3*3
/ (forward slash) Division 3/3
% (percent sign) Percent 20%
^ (caret) Exponentiation 3^2 (the same as 3*3)

 

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.

Comparison operator Meaning Example
= (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 concatenation operator

 

Use the ampersand (&) to join, or concatenate, one or more text strings to produce a single piece of text.

Text operator Meaning Example
& (ampersand) Connects, or concatenates, two values to produce one continuous text value "North" & "wind" produce "Northwind"

 

Reference operators

 

Combine ranges of cells for calculations with the following operators.

Reference operator Meaning Example
: (colon) Range operator, which produces one reference to all the cells between two references, including the two references B5:B15
, (comma) Union operator, which combines multiple references into one reference SUM(B5:B15,D5:D15)

 

End of MS Excel Help file

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

  • Exponentiation
  • Multiplication And Division
  • Subtraction And Addition

 

If a formula contained both a multiplication and a division operator Excel would calculate them from left to right. The same would apply for subtraction and addition. We can change the order in which Excel does its calculations by closing the relative function in parenthesis. Let's say we had the formula =10-10*10 the result would be -90 (negative 90). If we then used =(10-10)*10 the result would be 0 (zero). In other words we have forced Excel to change its natural order of calculation. Excel is quite happy to do this.

 

Some examples of this would be:

  • =5+5*5+5+5 Would Result In 40
  • =(5+5)*5+5+5 Would Result In 60
  • =(5+5)*(5+5)+5 Would Result In 105

 

So as you can see, we can manipulate any formula to calculate in the order we want, simply by placing the parenthesis in the appropriate places.

 

We will leave Formulas at this stage to allow you time to let what we have discussed to date sink in. If there are any questions you would like to ask or any particular formulas you would like explained you only need to ask. What we have shown you is what we consider the least you should know about Excel and formulas. Once you have gone over and fully understand these lessons on Excels functions and formulas you will have the foundations on which we can build. You may also discover that you will know the fundamentals of Excel formulas and functions better than a lot of so called experienced users!!

 

Go back to:

Lesson 1 - Excel Fundamentals
Lesson 2 - Starting Excel and Excel Workbooks
Lesson 3 - Excel Toolbars and Task Panes
Lesson 4 - Excel Worksheets
Lesson 5 - Excel Cells and Navigating a Worksheet
Lesson 6 - Excel Cut/Copying and Pasting Data
Lesson 7 - Excel Copying with the Fill Handle
Lesson 8 - Excel Paste Special
Lesson 9 - Excel Insert Command
Lesson 10 - Excel's default options
Lesson 11 - Excel's Undo and Redo
Lesson 12 - Excel's Format Painter
Lesson 13 - Excel's Dates and Times
Lesson 14 - Excel's Custom Formats
Lesson 15 - Excel Formulas
Lesson 16 - Excel Cell References
Lesson 17 - Excel: Avoid Typing
Lesson 18 - Excel Formulae Arguments & Syntax
Lesson 19 - Excel Autosum Formula
Lesson 20 - Excel Auto Calculate
Lesson 21 - Excel's Insert Function
Lesson 22 - Excel's Useful Functions
Lesson 23 - Excel's Named Ranges
Lesson 24 - Excel's Constants and the Paste Name Dialog

See also:

Lesson 26 - Excel Comments Cell
Lesson 27 - Excel Find and Replace
Lesson - 28 - Clear Excel Cell Contents
Lesson 29 - Effective Excel Printing 1
Lesson 30 - Effective Excel Printing 2
Lesson 31 - Sorting in Excel
Lesson 32 - Hide/Show Row/Columns in Excel
Lesson 33 - Auto-Formats in Excel
Lesson 34 - Creating a Basic Excel Spreadsheet
Lesson 35 - Excel Charting Lesson: The Basic Excel Spreadsheet
Lesson 36 - Excel Worksheet Protection
Lesson 37 - Excel IF Formula Nesting
Lesson 38 - Excel Function Now/Today Formulas

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


Gallery



stars (0 Reviews)