Table of Contents

Entire Site

320 Chapter 12. Building Formulas Formula Fundamentals 351 Using Functions: A Preview 361 Naming Cells and Cell Ranges 368 Understanding Error Values 378 Worksheet Calculation 379 Using Arrays 385 Linking Workbooks 389 Creating Conditional Tests 393 Formulas are the heart and soul of a spreadsheet, and Microsoft Excel offers a rich environment in which to build complex formulas. Armed with a few mathematical operators and rules for cell entry, you can turn a worksheet into a powerful calculator. Formula Fundamentals All formulas in Excel begin with an equal sign. The equal sign tells Excel that the succeeding char- acters constitute a formula. If you omit the equal sign, Excel might interpret the entry as text. To see how formulas work, we'll walk through some rudimentary ones. Begin by selecting blank cell A10. Then type =10+5 and press Enter. The value 15 appears in cell A10. Now select cell A10, and the formula bar displays the formula you just entered. What appears in the cell is the displayed value; what appears in the formula bar is the underlying value, which in this case is a formula. Understanding the Precedence of Operators Operators are symbols that represent specific mathematical operations, including the plus sign (+), minus sign (-), division sign (/), and multiplication sign (*). When performing these operations in a formula, Excel follows certain rules of precedence: · Expressions within parentheses are processed first. · Multiplication and division are performed before addition and subtraction. · Consecutive operators with the same level of precedence are calculated from left to right. Enter some formulas to see how these rules apply. Select an empty cell and type =4+12/6. Press Enter, and you see the value 6. Excel first divides 12 by 6 and then adds the result (2) to 4. If Excel used different precedence rules, the result would be different. For example, select another empty cell and type =(4+12)/6. Press Enter, and you see the value 2.666667. This demonstrates how you can change the order of precedence using parentheses. The formulas in Table 12-1 contain the same values and operators, but note the different results because of the placement of parentheses: