Excel® 2007 Power Programming with VBA

Chapter 3. Formula Tricks and Techniques

In This Chapter

This chapter provides an overview of Excel’s formula-related features and describes some techniques that might be new to you.

• An overview of Excel formulas

• Differentiating between absolute and relative references in formulas

• Understanding and using names

• Introducing array formulas

• Counting and summing cells

• Working with dates and times

• Creating megaformulas

Virtually every successful spreadsheet application uses formulas. In fact, constructing formulas can certainly be construed as a type of programming.

Note

For a much more comprehensive treatment of Excel formulas and functions, refer to my book, Excel 2007 Formulas (Wiley).

About Formulas

Formulas, of course, are what make a spreadsheet a spreadsheet. If it weren’t for formulas, your worksheet would just be a static document — something that could be produced by a word processor that has great support for tables.

Excel has a huge assortment of built-in functions, has excellent support for names, and even supports array formulas (a special type of formula that can perform otherwise impossible calculations).

A formula entered into a cell can consist of any of the following elements:

• Operators such as + (for addition) and * (for multiplication)

• Cell references (including named cells and ranges)

• Numbers or text strings

• Worksheet functions (such as SUM or AVERAGE)

A formula in Excel 2007 can consist ...