The Basics of Formulas and Functions in Excel 2010
Formulas are primarily mathematical equations that can perform calculations. In Excel, a formula always begins with an equal sign (=).
Excel calculates each formula from left to right, according to a specific order for each operator in the formula. For multiple operators, Excel evaluates several operators in a defined order when you combine them in a single formula.
1. Negation (as in -1)
2. Percent (%)
3. Exponentiation (^)
4. Multiplication and Division (* and /)
5. Addition and Subtraction (+ and –)
For example, =5+2*2 returns 9, while =5*2+2 returns 12.
Since multiplication comes before addition in the order, the formula =5+2*2 returns 9 because 2*2 is multiplied first before it is added to 5. The product of 2*2 is 4; therefore 5+4=9.
In the second formula, =5*2+2 returns 12 because 5*2 is multiplied first before it is added to 2. The product of 5*2, which is 10, is then added to 2 resulting to 12.
Users can control the order of evaluation by using brackets. Values within brackets are evaluated first by Excel. In the case of multiple brackets, they are evaluated by Excel working from left to right. For example:
=(5+2)*2 returns 14 because 5+2 = 7, therefore 7*2 = 14.
=5*(2+2) returns 20 because 2+2=4, therefore 5*4=20.
One powerful feature of Excel is that it allows us to substitute Cell References in formulas. So if we assume that A1 contains a 5, then = (A1+2)*2 returns 14. Excel also lets us name Cells and uses the name in formulas. If we assume A1 contains a 5 and is named Start, then =Start*(2+2) returns 20. To do this:
1. Type the value 5 in cell A1 and the name Start in B1.
2. Select the cells containing the value and the name.
3. On the Formulas tab, click Create Names from Selection.
4. In this case, since the name is to the right of the value, we are going to create names from values in the Right column. Click OK.
5. Select a cell where you would want the answer to appear; let’s choose C1. In the formula bar, type the formula =Start*(2+2). Press Enter. Notice we get the answer of 20 because the name Start now has the value of 5.
Functions are predefined formulas that perform calculations by using specific values in a particular order, or structure. These specific values are called arguments.
Function Structure always begins with an equal sign (=), followed by the function name, an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis. Remember that for every opening parenthesis, there should be a closing parenthesis for the structure to be in balance.
Excel holds an extensive library of functions that can be used to perform simple or complex calculations. This capability makes it easier to use functions correctly. You can find the list of the available functions on the Formulas tab where they are listed by category.
Another method of locating a function is by using the Insert Function dialog box. You can access it by clicking on the Insert Function button found on the Formulas tab of the Ribbon.
If you already know which functions you need to use in your formula, you may also directly type them in where necessary other than using the Ribbon or Insert function dialog box to look for the function.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!