Thanks Conrad,

So basically the {} denote an array is being used in the formula and Excel
needs to treat it differently.

Brad

"Conrad Carlberg" wrote:


"Brad" wrote in message
...
Perfect!!!

Why?

Oh, man, did you ever just open a can of worms. Bob Umlas, aka the Excel
Trickster and undisputed king of array-entered formulas, just had an entire
book published on that topic. I remember an MVP colloquium in Seattle just
before Excel 95 was released, and asking other Excel MVPs for a crisp
definition of when array formulas were needed. No one could supply one.
(Certainly I couldn't; else I wouldn't have asked.)

Quite apart from your situation, array formulas are necessary when you're
working with certain worksheet functions such as LINEST, TRANSPOSE, certain
matrix algebra functions, and simpler functions such as FREQUENCY. In these
cases, array-entry is necessitated because the function is intended to
return an array of results, not just a single number. And in that case you
have to begin by selecting an array of worksheet cells that conforms to the
rows-by-columns range that the function will return.

In the situation you describe, array-entry is needed because you're
presenting Excel with an array of TRUE or FALSE values that are used to
determine whether to submit values in another array (D5:D14) to the AVERAGE
function. Because AVERAGE returns a single value, as distinct from, say,
LINEST, you're working with a single-cell array formula. Entered normally,
with just the Enter key, Excel expects to see a single condition following
the IF function [e.g., IF(C5="Europe",] but your formula submits an array of
conditions, and using Ctrl-Shift-Enter signals Excel that it needs to
evaluate an array of conditions, not just one. Using Tools | Formula
Auditing | Evaluate Formula can provide insight into what's going on. So can
other particupants in this ng.

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005