Chapter 5

Performing Custom Calculations with Formulas

To make your worksheets deliver the information you want, you'll probably need to perform calculations with your data. To perform calculations, you enter formulas and functions in cells. You'll learn to use formulas in this chapter and functions in Chapter 6.

We'll start by going over what functions and formulas are so that you're clear on the difference, and then make sure you know how you refer to cells and ranges in Excel.

After that, I'll show you how to create your own formulas using Excel's calculation operators. We'll start with straightforward formulas that use a single calculation operator, and then move on to more complex formulas that use multiple calculation operators.

When you use multiple calculation operators in a formula, you need to make sure Excel evaluates them in the right order—otherwise, you can produce results not only in the wrong ballpark but also in a different league. So you need to know Excel's default order for evaluating operators, and how to change the order when necessary. With this knowledge, you can build complex formulas—but you may want to take the easy route and build them one piece at a time so you can easily detect and fix problems.

At the end of the chapter, I'll introduce you to common problems that occur with formulas and tell you how to troubleshoot them.