Excel Cookbook

Chapter 3. Using Formulas

Most spreadsheets rely on formulas to perform calculations and manipulate data using operators, functions, references, and constants. However, as these grow more complex, they can quickly become unwieldy and prone to error.

This chapter contains recipes designed to help you work with formulas more effectively and deal with any problems efficiently. Areas covered include:

• Using arithmetic, comparison, and reference operators, including ones that return a spill range or implicit intersection

• Working with arrays (matrices of values), including array constants, dynamic arrays, and legacy arrays

• Dealing with Excel’s error values

• Using formula-auditing tools to analyze and debug formulas, including tracing interdependencies and errors, running error checks, stepping through formulas, and watching cell values and formulas with the Watch window

• Using automatic, manual, and iterative calculations to control when formulas recalculate and resolve circular references

3.1 Using Operators and Order of Precedence

Problem

You want to know what operators are available in Excel, what they do, and their order of precedence.

Solution

Excel includes four types of operators: arithmetic, comparison, text concatenation, and reference.

The arithmetic operators let you perform basic mathematical operations and return numbers. They include the following:

+ and -

These are for addition and subtraction (for example, =A1+B1 or =A1-B1). You can also use the minus ...