Understanding Formulas and Functions

The major difference between a checkbook register and an Excel worksheet is that a worksheet can calculate the new balance for you and perform countless other mathematical calculations through the use of formulas and functions. Excel uses formulas to perform addition, subtraction, multiplication, and division on the entries in a worksheet to determine totals, grand totals, percentages, and other practical results. Figure 1.9 shows a sample formula in action. This simple formula totals the values in cells B4 to E4.

Figure 1.9. Formulas automate calculations in a worksheet.

The following helpful facts about formulas should help you understand how formulas operate in a worksheet:

• You enter a formula in the cell in which you want the result to appear. Excel performs the specified calculation and then displays the result in this cell.

• All formulas start with an equal sign (=). If you start a formula with a letter, Excel assumes you are entering a label. If the formula begins with a number, Excel interprets the entry as a value.

• Formulas typically include cell addresses that reference values contained in other cells in the worksheet. For example, the formula =B4+C4+D4+E4 calculates the total of the values contained in cells B4, C4, D4, and E4.

• Formulas can also include numbers . For example, if a worksheet displays your monthly income in cell H10, and you want to calculate your annual income, you can enter the formula =H10*12, which tells Excel to multiply the value in cell H10 by 12.

• Formulas use the following symbols, called arithmetic operators :

  + Addition

  “ Subtraction

  * Multiplication

  / Division

  ^ Raise to the ___ power of

  % Percentage

• Excel follows the standard order of operations for calculating equations, performing all multiplication and division first and then addition and subtraction. You can control the order of operations by using parentheses to group the operations you want Excel to perform first. For example, to determine the average of the values in cells B4, C4, D4, and E4, you would enter the formula =(B4+C4+D4+E4)/4 . The parentheses in this formula force Excel to perform addition before division. See "Learning the Order of Operations" in Chapter 6, "Automating Calculations with Formulas and Functions," for details.

• A formula automatically recalculates its result whenever you change a value in one of the cells that the formula references. For example, if a cell contains the formula =B4+C4+D4+E4, and you change the value in cell D4, Excel automatically recalculates the result and displays it in the cell that contains the formula.

Functions can be a little more complex than formulas, but functions act as a sort of mathematical shorthand. For example, the function AVERAGE can calculate the average of a range of numbers without requiring you to enter the cell address of every value you want included in the average. In the preceding example, for instance, you could save time by using the AVERAGE function: Rather than type =(B4+C4+D4+E4)/4 , you could simply enter =AVERAGE(B4:E4) .

Excel features a wide selection of functions, most of which are more complex than the AVERAGE function. Excel has functions designed specifically for financial calculations, engineering applications, statistics, and much more. Figure 1.10 shows a financial function in action. This function determines the monthly payment for a loan, based on the loan amount, the term of the loan, and the interest rate.

Figure 1.10. Functions can perform advanced calculations on the values in a worksheet.

Every function consists of the function itself followed by an argument . The argument consists of the values that the function uses to calculate the result and must be entered in the proper syntax (order). In Figure 1.10, for instance, the worksheet uses the function =PMT ( rate , nper , pv , fv ), where PMT is the function used to determine the payment due on a loan, rate is the interest rate, nper is the number of payment periods, pv is the present value of the loan, and fv is the future value of the loan (which is zero, when it is finally paid off). (In the argument ( rate , nper , pv , fv ), rate , nper , pv , and fv are merely placeholders. When you type the argument, you use cell addresses or actual values in place of the placeholders.)

Fortunately, you don't need to learn and memorize the functions to use them effectively. Excel comes equipped with the Insert Function feature, which leads you step-by-step through the process of choosing the function you need and then selecting the cells that make up the argument. Figure 1.11 shows the Insert Function feature in action.

Figure 1.11. Excel's Insert Function feature simplifies the process of entering functions.