 |
|
|
|
|
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:
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:
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 To Free Excel Training Lesson 26 . Back to Previous Lesson
Go to Excel Basic/Level 1 Training Index
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel
| MSSQL Migration
Toolkit |
Monte Carlo Add-in |
Excel
Costing Templates