Microsoft Office Tutorials and References

In Depth Information

**Chapter 12: Building formulas**

CHAPTER 12

Building formulas

Formula fundamentals............................467

Using functions: A preview ........................480

Working with formulas ...........................484

Worksheet calculation ............................505

Using arrays.....................................512

Linking workbooks...............................517

Creating conditional tests.........................521

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.

In this chapter, we cover the basics and then look more closely at using functions, defining

names, building structured references, working with arrays, creating linking formulas, and

constructing conditional tests.

Formula fundamentals

A lot of this section will seem elementary to experienced Excel users, but it is important

information for anyone who is just arriving at the party. And even experienced users might

find something useful here that they didn’t know about.

All formulas in Excel begin with an equal sign.

This is the most fundamental fact of all. The equal sign tells Excel that the succeeding

characters constitute a formula. If you omit the equal sign, Excel might interpret the entry as

text. To show how formulas work, we’ll walk you through some rudimentary ones. Begin by

selecting blank cell A10. Then type
=10+5
, and press the Enter key. The value
15
appears

in cell A10. Now select cell A10, and the formula bar displays the formula you just typed.

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:

●
Excel processes expressions within parentheses first.

467