Basically, that's correct, although it's frequently true that the array is
one that must be evaluated to a set of TRUE/FALSE conditions before the
surrounding function can return a result. But not always. Here's one that
isolates the final part of a string that contains embedded blanks (I think
it's from the Bob Umlas Formula Boutique):

=RIGHT(A1,MATCH(" ",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))),1),0))

So if A1 contains Charles Evans Hughes, the array formula would return
Hughes.

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005


"Brad" wrote in message
...
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