excel formula workshop - mobile mouse workshop manual sample... · 2011-01-06 · microsoft excel...
TRANSCRIPT
Excel Formula Workshop
1 DAY COURSE
Revision # 2.0
Red
uced
Qua
lity
Sam
ple
THIS MANUAL BELONGS TO…
.........................................................................................................................................................
TODAY’S DATE IS…
.........................................................................................................................................................
MY OBJECTIVE(S) FOR THIS COURSE ARE…
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................
.........................................................................................................................................................Red
uced
Qua
lity
Sam
ple
Mobile MOUSeUnit 13 / 795 Beaufort St
MT Lawley, WA, 6050Telephone: (08) 9404 7041Facsimile: (08) 9467 9132
Email: [email protected]: www.mobilemouse.com.au
Microsoft Excel Formula Workshop
ABOUT THIS COURSE…
This workshop is focused on the real power of Microsoft Excel… calculations. Excel has many built-in calculations (known as Functions) that perform specific tasks or calculations. This workshop focuses on many different categories of functions right through from fundamental functions to more advanced array {CSE} formulae.
DOWNLOAD YOUR PRACTICE FILES...
In addition to the exercises you will complete in class, there are also exercises in this workbook. These workbook exercises can only be completed in conjunction with your Mobile MOUSe practice files. In order to locate your Mobile MOUSe Practice Files visit www.mobilemouse.com.au/dowloads.php
COURSE STRUCTURE…
SESSION 1 - HOW TO USE THIS MANUAL
SESSION 2 - FORMULA BASICS
SESSION 3 - COUNT, SUMIF, IF, AND, OR FORMULAE
SESSION 4 - LOOKUP & REFERENCE, TEXT & MORE NESTING
SESSION 5 - DATE AND TIME FORMULAE
SESSION 6 - DATABASE FUNCTIONS
SESSION 7 - ROUNDING FORMULAE
SESSION 8 - FINANCIAL FORMULAE
SESSION 9 - ARRAY FORMULAE
SESSION 10 - FORMULA AUDITING
WHAT IS REQUIRED…
To complete the exercises in this manual you must have the following resources at your disposal…
Mobile MOUSe configured computer (with Windows & Microsoft Excel)
Set of Mobile MOUSe practice filesRed
uced
Qua
lity
Sam
ple
TABLE OF CONTENTS
SESSION 1 - HOW TO USE THIS MANUAL................................................................................. 1
1.1 A Brief Explanation ............................................................................................................1
1.1 Mobile MOUSe Practice Files.............................................................................................21.1.1 How to Download and Install the Practice Files ..................................................................................................2
SESSION 2 - FORMULA BASICS................................................................................................. 6
2.1 ENTERING FORMULAS......................................................................................................6
2.2 BOMDAS / PEMDAS / BIMDAS / BIDMAS..........................................................................72.2.1 Using AutoSUM to create Totals Formulas .........................................................................................................92.2.2 More simple formulas (calculating a percentage) .............................................................................................122.2.3 Absolute References ..........................................................................................................................................132.2.4 Calculating an Average, Minimum and Maximum.............................................................................................152.2.5 Using LARGE and SMALL (Get the X largest or X smallest value) .................................................................172.2.6 The REPT Function (Repeats characters) ........................................................................................................182.2.7 MODE (Most Common Numerical Value) .........................................................................................................19
SESSION 3 - COUNT, SUMIF, IF, AND, OR FORMULAE .......................................................... 21
3.1 COUNT Functions ............................................................................................................213.1.1 COUNT (Count Cells that contain Numbers) ....................................................................................................213.1.2 COUNTA (Counts all cells) and COUNTBLANK (Counts Blank Cells) ............................................................223.1.3 COUNTIF (Counts cells if they contain certain values) ....................................................................................233.1.4 SUMIF (SUM cells only IF certain Conditions are met) ....................................................................................25
3.2 IF (If this... then do X, if not do Y) ....................................................................................273.2.1 Building a simple IF statement...........................................................................................................................283.2.2 Nested IF (Multiple IF statements).....................................................................................................................303.2.3 IS Functions........................................................................................................................................................31
3.3 AND Function / OR Function ...........................................................................................333.3.1 AND Function .....................................................................................................................................................333.3.2 The OR Function ................................................................................................................................................343.3.3 Nesting an IF with AND......................................................................................................................................35
SESSION 4 - LOOKUP & REFERENCE, TEXT & MORE NESTING........................................... 37
4.1 Naming Ranges ................................................................................................................374.1.1 The Name Box....................................................................................................................................................38
4.2 Using VLOOKUP and Reference Functions in Formulas ...............................................394.2.1 Using VLOOKUP................................................................................................................................................40
4.3 HLOOKUP.........................................................................................................................43
4.4 More Named Ranges........................................................................................................444.4.1 Create Names with the Create Names dialog box............................................................................................444.4.2 Create / Modify / Delete Names using the Define Name dialog box ................................................................454.4.3 Using LOOKUP ..................................................................................................................................................47
4.5 Using INDEX and MATCH (Finding a value & returning a corresponding value, without sort or directional limitations) ..................................................................................................50
4.5.1 INDEX.................................................................................................................................................................504.5.2 MATCH ...............................................................................................................................................................514.5.3 Nesting INDEX and MATCH together ...............................................................................................................514.5.4 Using Nesting to Replace Error Messages .......................................................................................................53
4.6 SUBTOTALS.....................................................................................................................55
4.7 Comparing Two Lists.......................................................................................................57
4.8 Useful TEXT Functions ....................................................................................................584.8.1 Using UPPER .....................................................................................................................................................584.8.2 Using LOWER ....................................................................................................................................................594.8.3 Using PROPER ..................................................................................................................................................604.8.4 Using CONCATENATION..................................................................................................................................604.8.5 Using TEXT ........................................................................................................................................................61
4.9 More Nested Examples ....................................................................................................62
4.10 RANK ................................................................................................................................634.10.1 Using RANK........................................................................................................................................................64Red
uced
Qua
lity
Sam
ple
SESSION 5 - DATE AND TIME FORMULAE .............................................................................. 66
5.1 Common Date and Time Functions.................................................................................66
5.2 DATEDIF ...........................................................................................................................68
SESSION 6 - DATABASE FUNCTIONS...................................................................................... 70
6.1 Database Functions .........................................................................................................706.1.1 DAVERAGE........................................................................................................................................................716.1.2 DSUM .................................................................................................................................................................726.1.3 DMAX..................................................................................................................................................................726.1.4 DMIN...................................................................................................................................................................736.1.5 DCOUNT ............................................................................................................................................................746.1.6 Notes on DVAR / DVARP / DSTDEV / DSTDEVP............................................................................................75
SESSION 7 - ROUNDING FORMULAE....................................................................................... 76
7.1 The Need for Rounding....................................................................................................767.1.1 Using ROUND ....................................................................................................................................................777.1.2 Using INT ............................................................................................................................................................787.1.3 Using ROUNDDOWN.........................................................................................................................................797.1.4 Using ROUNDUP ...............................................................................................................................................807.1.5 Using CEILING ...................................................................................................................................................817.1.6 Using FLOOR .....................................................................................................................................................83
SESSION 8 - FINANCIAL FORMULAE....................................................................................... 85
8.1 Financial Functions..........................................................................................................85
SESSION 9 - ARRAY FORMULAE ............................................................................................. 90
9.1 An Introduction to Arrays and Array Formulas ..............................................................90
9.2 Array Formula Syntax ......................................................................................................929.2.1 Rules for Entering and Changing Array Formulas ............................................................................................929.2.2 Expanding an Array Formula .............................................................................................................................939.2.3 Disadvantages of Using Array Formulas ...........................................................................................................93
9.3 Array Constants ...............................................................................................................939.3.1 An Introduction to Array Constants....................................................................................................................939.3.2 Creating One-Dimensional and Two-Dimensional Constants ..........................................................................949.3.3 Naming Array Constants ....................................................................................................................................989.3.4 Troubleshooting Array Constants ......................................................................................................................999.3.5 More Array Formula Exercises ..........................................................................................................................999.3.6 Create Arrays and Array Constants from Existing Values ..............................................................................1029.3.7 More Array Examples (with SUM) ...................................................................................................................1039.3.8 More Array Examples – (with AVERAGE).......................................................................................................1049.3.9 More Array Examples - Count the Differences between Two Ranges of Cells .............................................104
SESSION 10 - FORMULA AUDITING.................................................................................... 105
10.1 Formula Auditing............................................................................................................10510.1.1 Precedents and Dependants ...........................................................................................................................10510.1.2 Locating and Resolving Errors.........................................................................................................................10910.1.3 Using the Formula / Error Checker to Find Errors ..........................................................................................11110.1.4 The Watch Window ..........................................................................................................................................114
Red
uced
Qua
lity
Sam
ple
LIST OF FIGURES
Figure 1.1-1: Mobile MOUSe downloads ..........................................................................................................................................................2
Figure 1.1-2: File Download Security ................................................................................................................................................................3Figure 1.1-3: Saving the file to be downloaded.................................................................................................................................................3
Figure 1.1-4: Security Warning ..........................................................................................................................................................................4
Figure 1.1-5: Starting the Installation.................................................................................................................................................................4Figure 1.1-6: The default installation location ...................................................................................................................................................5
Figure 1.1-7: Completing the installation...........................................................................................................................................................5
Figure 2.2-1: Building a simple point and click formula ....................................................................................................................................9Figure 2.2-2: Microsoft Excel AutoSum (above) ...............................................................................................................................................9
Figure 2.2-3: The SUM function in action........................................................................................................................................................10
Figure 2.2-4: looking at the function in the formula bar ..................................................................................................................................11Figure 2.2-5: Using the AutoFiller to copy a formula.......................................................................................................................................11
Figure 2.2-6: Selecting a range for the purpose of AutoSUM.........................................................................................................................11
Figure 2.2-7: the end result of AutoSUM.........................................................................................................................................................12Figure 2.2-8: the Initial division formula...........................................................................................................................................................13
Figure 2.2-9: When using the AutoFiller, you receive errors? ........................................................................................................................13
Figure 2.2-10: the Relative reference is moving away from cell F10 .............................................................................................................14Figure 2.2-11: Creating an Absolute reference...............................................................................................................................................14
Figure 2.2-12: Having adding the absolute reference the formula now works...............................................................................................15
Figure 2.2-13: Choosing the AVERAGE function from the AutoSUM drop-down .........................................................................................15Figure 2.2-14: Reselecting the correct range on which to perform the AVERAGE .......................................................................................16
Figure 2.2-15: the MEDIAN function................................................................................................................................................................16
Figure 2.2-16: Use Quick Sum for quick answers...........................................................................................................................................17Figure 2.2-17: Building the REPT function ......................................................................................................................................................19
Figure 2.2-18: An “In-Cell Bar Chart” created with the REPT function...........................................................................................................19
Figure 3.1-1: Choosing the COUNT Function .................................................................................................................................................21Figure 3.1-2: the results of your functions .......................................................................................................................................................26
Figure 3.2-1: the Williams Paint Commission worksheet................................................................................................................................27Figure 3.2-2: Building the IF statement ...........................................................................................................................................................28
Figure 3.2-3: ISERROR producing TRUE for cells that contain errors ..........................................................................................................32
Figure 3.3-1: Building the AND function ..........................................................................................................................................................34Figure 3.3-2: Building the OR function ............................................................................................................................................................35
Figure 3.3-3: Nesting AND within IF ................................................................................................................................................................36
Figure 4.1-1: Naming cells with the Name box ...............................................................................................................................................38Figure 4.2-1: We can use Lookup functions to return information from corresponding columns or rows.....................................................40
Figure 4.2-2: Using the Insert Function dialog box .........................................................................................................................................41
Figure 4.2-3: The Function Arguments dialog box..........................................................................................................................................41Figure 4.2-4: Pasting a Range name...............................................................................................................................................................42
Figure 4.2-5: the Function Arguments dialog box with arguments entered ...................................................................................................42
Figure 4.4-1: the Create Names dialog box ....................................................................................................................................................45Figure 4.4-2: the Define Name dialog box.......................................................................................................................................................46
Figure 4.4-3: A named ranges cell reference..................................................................................................................................................46
Figure 4.4-4: Creating a named constant ........................................................................................................................................................47Figure 4.4-5: the Select Arguments dialog box ...............................................................................................................................................48
Figure 4.4-6: Function Arguments for the vector version of the LOOKUP function.......................................................................................49
Figure 4.4-7: LOOKUP arguments as entered................................................................................................................................................49Figure 4.5-1: the INDEX and nested MATCH function ...................................................................................................................................52
Figure 4.5-2: the completed Index and Match results for cells D6 and D7 ....................................................................................................52
Figure 4.5-3: Your custom error message is displayed ..................................................................................................................................54Figure 4.8-1: Using the TEXT function as part of concatenation....................................................................................................................61
Figure 4.9-1: the Create Names dialog box again ..........................................................................................................................................62
Figure 4.9-2: the results of your VLOOKUPS .................................................................................................................................................63Figure 4.10-1: AutoFill the RANK function ......................................................................................................................................................65
Figure 5.1-1: Using the Insert function dialog to insert date and time functions ............................................................................................67
Figure 5.1-2: Format the result of the subtraction of two dates as a General number ..................................................................................68Figure 6.1-1: Choosing Poinsettia as the criteria ............................................................................................................................................73
Figure 6.1-2: the DGSales workbook demonstrates DFunctions ...................................................................................................................74
Figure 7.1-1: Some results of the ROUND formula ........................................................................................................................................78Figure 7.1-2: the ROUNDDOWN function results ...........................................................................................................................................80
Figure 7.1-3: the FLOOR function rounds values down .................................................................................................................................84
Figure 8.1-1: More Functions...........................................................................................................................................................................87Red
uced
Qua
lity
Sam
ple
Figure 8.1-2: Financial Function Arguments ...................................................................................................................................................88
Figure 8.1-3: the Completed Financial Functions worksheet .........................................................................................................................89
Figure 9.1-1: Building a multi-cell Array formula .............................................................................................................................................91Figure 9.1-2: Building a single-cell Array formula ...........................................................................................................................................92
Figure 9.3-1: a Horizontal Array constant........................................................................................................................................................94
Figure 9.3-2: a Vertical Array Constant ...........................................................................................................................................................95Figure 9.3-3: a 2-Dimensional Array Constant................................................................................................................................................95
Figure 9.3-4: a Horizontal Array Constant multiplied by a single cell .............................................................................................................96
Figure 9.3-5: a Vertical Array Constant multiplied by a single cell .................................................................................................................96Figure 9.3-6: a 2D Array Constant multiplied by a single cell.........................................................................................................................97
Figure 9.3-7: An Array Constant used in combination with the SUM function ...............................................................................................97
Figure 9.3-8: Entering the LARGE function as a MultiCell array ....................................................................................................................97Figure 9.3-9: the Define Name dialog box.......................................................................................................................................................98
Figure 9.3-10: the Results of the named Array Constant: Qtr1 ......................................................................................................................99
Figure 9.3-11: using an Array formula to simplify a AND function................................................................................................................100Figure 9.3-12: Excluding zero values from a MIN.........................................................................................................................................100
Figure 9.3-13: Using an Array to cross reference and Add values from another sheet ..............................................................................102
Figure 9.3-14: Creating Array Constants from existing cell values ..............................................................................................................103Figure 10.1-1: The formula auditing toolbar ..................................................................................................................................................105
Figure 10.1-2: Tracing Precedents ................................................................................................................................................................106
Figure 10.1-3: Tracing precedents even further............................................................................................................................................106Figure 10.1-4: Tracing Dependants ...............................................................................................................................................................107
Figure 10.1-5: Tracing dependants across sheets or workbooks.................................................................................................................107
Figure 10.1-6: Go To dialog box ....................................................................................................................................................................108Figure 10.1-7: Error Checker .........................................................................................................................................................................112
Figure 10.1-8: Trace Error..............................................................................................................................................................................113Figure 10.1-9: Copying a formula from above using the error options smart tag ........................................................................................113
Figure 10.1-10: More Error Checking ............................................................................................................................................................114
Figure 10.1-11: The Watch Window ..............................................................................................................................................................114
LIST OF TABLES
Table 2.1-1: Mathematical and logical operations and the symbol(s) used.....................................................................................................6
Table 2.2-1: Microsoft Excel AutoSum options (below) ..................................................................................................................................10
Table 3.2-1: IS Function formulae ...................................................................................................................................................................31Table 3.2-2: More Information functions..........................................................................................................................................................32
Table 3.3-1: Excel Logical Functions...............................................................................................................................................................33
Table 4.2-1: Excel’s Lookup and Reference Functions ..................................................................................................................................39Table 4.6-1 : the Subtotal Worksheet Function Numbers...............................................................................................................................55
Table 4.8-1: Excel Text functions ....................................................................................................................................................................58
Table 4.10-1: Useful Position / Ranking / Percentage functions ....................................................................................................................63Table 5.1-1: Common Date and Time functions .............................................................................................................................................66
Table 5.2-1: DateDif Interval codes .................................................................................................................................................................69Table 6.1-1: Database Functions.....................................................................................................................................................................70
Table 8.1-1: A comprehensive list of Excels’ Financial Functions..................................................................................................................85
Table 8.1-2: To calculate your repayment amount according to the repayment type....................................................................................89Table 10.1-1: Formula Auditing Toolbar ........................................................................................................................................................109
Table 10.1-2: Excel error values, causes and corrective actions .................................................................................................................110
Red
uced
Qua
lity
Sam
ple
Microsoft Excel - Formula Workshop Page 1 of 125
Copyright © 2007 – Mobile MOUSe / Scope Computing Vision. Version 2.0. No part of this manual may be reproduced or copied in any form or fashion without the express written permission of Mobile MOUSe / Scope Computing Vision - Tel: (08) 9404 7041, Fax: (08) 9467 9132. Visit us online at www.mobilemouse.com.au or email us at [email protected] – Intellectual property rights retained by Russell Kitching.
SESSION 1 - HOW TO USE THIS MANUAL
In this session you will learn about the following…
How to use this manual
1.1 A Brief Explanation
What follows is a brief explanation of the various fonts and symbols used in this manual.
When you see It means...
Text listed like this
You MUST read this, because it could have an effect on the final outcome of an action you perform.
Text listed like this
This is optional to read, but these tips often point out quicker ways of doing things, or alternative methods.
This is the name of an Exercise
You are about to start an exercise in the book.
Bolded text in exercisesObjects that you click on, like buttons, tabs or menus are often listed in bold. Locations and folders are also listed in bold.
Text listed like thisWhen you see text like this it means you should type the text you see in this style.
F+G+L
Keyboard shortcuts are displayed like this. In this example you would press and hold F, hold
G and then press L once (while still
holding F and G)
Format > CellsEdit > PasteView > Normal
Click the Format menu, click CellsClick the Edit menu, click PasteClick the View menu, click Normal
Red
uced
Qua
lity
Sam
ple
Microsoft Excel - Formula Workshop Page 2 of 125
Copyright © 2007 – Mobile MOUSe / Scope Computing Vision. Version 2.0. No part of this manual may be reproduced or copied in any form or fashion without the express written permission of Mobile MOUSe / Scope Computing Vision - Tel: (08) 9404 7041, Fax: (08) 9467 9132. Visit us online at www.mobilemouse.com.au or email us at [email protected] – Intellectual property rights retained by Russell Kitching.
1.1 Mobile MOUSe Practice Files
In order to complete the exercises in this book, you will need access to our practice files.
If you do not have access to the internet call us on (08) 9404 7041 and we will send you a CDROM disk with the practice files (a small postage charge may apply).
1.1.1 How to Download and Install the Practice Files
To install the practice files perform the following steps...
How to Download and Install the Mobile MOUSe exercise files
1. Start Internet Explorer, click in the address bar and type the following address: http://www.mobilemouse.com.au/downloads.php and then press J and wait a few seconds, the following screen should appear...
Figure 1.1-1: Mobile MOUSe downloads
1. Scroll down the webpage and then click the link that says Microsoft EXCEL essentials practice files
2. At the screen that appears, click the button that says Save
Red
uced
Qua
lity
Sam
ple
Microsoft Excel - Formula Workshop Page 3 of 125
Copyright © 2007 – Mobile MOUSe / Scope Computing Vision. Version 2.0. No part of this manual may be reproduced or copied in any form or fashion without the express written permission of Mobile MOUSe / Scope Computing Vision - Tel: (08) 9404 7041, Fax: (08) 9467 9132. Visit us online at www.mobilemouse.com.au or email us at [email protected] – Intellectual property rights retained by Russell Kitching.
Figure 1.1-2: File Download Security
3. At the Save As dialog box, click the Desktop link on the left hand side of the screen, click Save
Figure 1.1-3: Saving the file to be downloaded
4. Depending on the speed of your internet connection, the time it takes to download the practice files may vary. Once the download has completed, Close any open programs
5. On your Windows Desktop there should see Mobile MOUSe - Excel Practice Files.exe (or at least Mobile MOUSe - Excel Practice Files). Double click this file to start the installation process, you may be presented with the following screen...Red
uced
Qua
lity
Sam
ple
Microsoft Excel - Formula Workshop Page 4 of 125
Copyright © 2007 – Mobile MOUSe / Scope Computing Vision. Version 2.0. No part of this manual may be reproduced or copied in any form or fashion without the express written permission of Mobile MOUSe / Scope Computing Vision - Tel: (08) 9404 7041, Fax: (08) 9467 9132. Visit us online at www.mobilemouse.com.au or email us at [email protected] – Intellectual property rights retained by Russell Kitching.
Figure 1.1-4: Security Warning
6. If you are presented with the above image, click the Run button, which will them present you with the following screen…
Figure 1.1-5: Starting the Installation
7. Click Next
8. Click Next, which takes you to the following screen...
Red
uced
Qua
lity
Sam
ple
Microsoft Excel - Formula Workshop Page 5 of 125
Copyright © 2007 – Mobile MOUSe / Scope Computing Vision. Version 2.0. No part of this manual may be reproduced or copied in any form or fashion without the express written permission of Mobile MOUSe / Scope Computing Vision - Tel: (08) 9404 7041, Fax: (08) 9467 9132. Visit us online at www.mobilemouse.com.au or email us at [email protected] – Intellectual property rights retained by Russell Kitching.
Figure 1.1-6: The default installation location
9. At this screen, you would typically choose Next. This will install the practice files onto your Windows Desktop (the location referred to in the exercises in this manual). You can however choose a different location if you like, however we would recommend that you click Next... (so click Next )
10. Click Next to install the files
Figure 1.1-7: Completing the installation
11. At the final screen click Finish to complete the installation. Your practice files are now installed and you can commence the exercises in this manual
Red
uced
Qua
lity
Sam
ple
Microsoft Excel - Formula Workshop Page 6 of 125
Copyright © 2007 – Mobile MOUSe / Scope Computing Vision. Version 2.0. No part of this manual may be reproduced or copied in any form or fashion without the express written permission of Mobile MOUSe / Scope Computing Vision - Tel: (08) 9404 7041, Fax: (08) 9467 9132. Visit us online at www.mobilemouse.com.au or email us at [email protected] – Intellectual property rights retained by Russell Kitching.
SESSION 2 - FORMULA BASICS
In this session you will learn about the following…
PEMDAS, BOMDAS, BIMDAS, BIDMAS SUM WORKING OUT A PERCENTAGE AVERAGE MAX MIN MEDIAN LARGE SMALL REPT
2.1 ENTERING FORMULAS
Whenever you want to perform a calculation in Microsoft Excel you use a formula or a combination of formulas. Microsoft Excel uses standard computer operator symbols to denote mathematical and logical operations.
Table 2.1-1: Mathematical and logical operations and the symbol(s) used
Mathematical Operation Symbol used
Addition + (plus)
Subtraction - (minus)
Multiplication * (asterisk)
Division / (forward slash)
Exponentials (Power of) ^ (carat)
Precedence (Do this first) - enclose the argument in parenthesis
( ) (round brackets)
Percentage % (percentage)
Equal to = (equals)
Not equal to < > (less than greater than)
Greater than > (greater than)
Less than < (less than)
There are three ways to create simple formulas in Microsoft Excel:
Typing the formula using cell addresses
Point and Click
Copying / filling in a formula from another cell
It is very important to note that every calculation created in Microsoft Excel must begin with an operator. It is common and good practice to use the =(equals) operator to begin most calculations in Excel.
Red
uced
Qua
lity
Sam
ple
Microsoft Excel - Formula Workshop Page 7 of 125
Copyright © 2007 – Mobile MOUSe / Scope Computing Vision. Version 2.0. No part of this manual may be reproduced or copied in any form or fashion without the express written permission of Mobile MOUSe / Scope Computing Vision - Tel: (08) 9404 7041, Fax: (08) 9467 9132. Visit us online at www.mobilemouse.com.au or email us at [email protected] – Intellectual property rights retained by Russell Kitching.
2.2 BOMDAS / PEMDAS / BIMDAS / BIDMAS
Complex formulas may involve more than one operation. For example, adding two numbers and then multiplying the result by another number: = B1+G7 * F7.
When you have more than one operator in a formula you will need to know about the order of operations. The order of operations is a rule regarding how formulas are calculated.
With regards to the order of operations there are a few simple things to remember.
Formulas are calculated from left to right
Multiplication and Division are always done before any Addition or Subtraction. Microsoft Excel makes two left to right passes when calculating a formula (the Multiplication and Division is done on the first pass and Addition and Subtraction on the second).
You can force Excel to add or subtract before doing any Multiplication and Division by adding the Addition and Subtraction operations into brackets. An easy way to remember this is to remember the acronym BOMDAS (Brackets of Multiplication, Division, Addition, Subtraction). Alternatively you could also use this acronym which is more technically correct, PEMDAS (Parenthesis, Exponents, Multiplication, Division, Addition, Subtraction). BIMDAS or BIDMAS is another commonly used acronym that describes the order of operations.
Working with the order of operations
1. Open the file entitled Quarterly Sales.xls, located in the located in the \ Desktop \Excel \ Formula Workshop folder
2. Click cell B22 and enter the following formula: =2+1*7. Do this calculation in your head, before you press enter. Did you arrive at an answer of 21? Press I
3. An answer of 9 is returned (not 21 as you may have first assumed)
The reason for this is because of the order of operations. Remembering that following the rule of PEMDAS / BOMDAS / BIMDAS, multiplications are done BEFORE additions... So what is actually happening with your calculation is that 1*7 = 7 and +2 = 9 and not 2+1 = 3 and *7 = 21
4. You can force an answer of 21 by adding in a set of brackets (parenthesis). Double click cell B22 and amend the formula to read: =(2+1)*7. Press I
This time by forcing (2+1) to be calculated first (because of the brackets) you will get an answer of 21... (2+1) = 3 and 3 * 7 = 21.
5. Double click cell B22 and amend the formula to read =(2+1)*7 – 5 – 4. Press
IRed
uced
Qua
lity
Sam
ple