Excel Sales Forecasting For Dummies, 2nd Edition

Chapter 19

Ten Fun Facts to Know and Tell about Array Formulas

IN THIS CHAPTER

Entering an array formula

Selecting the range

Array formula peculiarities

Editing array formulas

If you find yourself using regression to forecast sales — and regression is one of the three principal methods used for forecasting — then you’re also going to find yourself using array formulas. Excel has quite a few functions that require you to use what’s called “array entry,” and several of them are intended for regression analysis.

Array formulas are quirky little beasts and at least one entire book has been written about their use. At least two forms of array formulas proved so popular a few years ago that Microsoft coded new functions of the normal type so that users wouldn’t have to use array formulas to get the correct results.

To use array formulas effectively, and to use functions in array formulas, you need to know more than just the arguments to the functions. You need to know the dimensions of the range that the results will occupy. And you’d best know how to edit them, because you’re going to want to.

Entering Array Formulas

The term “array formula” itself is a fuzzy one. It’s true that many array formulas are intended to fill an array of cells on the worksheet. But it’s also true that many array formulas are intended to occupy one cell only. You might find it helpful to think of an array formula as one that processes one or more arrays of data — arrays that might or might not appear on ...