Array

[Brad]

Thanks for taking the time to read my quesiton.

I typed in the word array into Excel Help and found this item

"About array formulas and how to enter them"

I am trying to duplicate the first example and cannot

Ex: =Average(if(C5:C14="Europe",D5:D14))

I tried something similar to this (diff cell ref) and I get #VALUE

Do I need to check off an addin or something?

Thanks,

Brad

[Peo Sjoblom]

They need to be entered with ctrl + shift & enter
select the cell with the formula, press F2, press
ctrl + shift & enter at the same time

Regards,

Peo Sjoblom

"Brad" <[email protected]> wrote in message
news:[email protected]...
> Thanks for taking the time to read my quesiton.
>
> I typed in the word array into Excel Help and found this item
>
> "About array formulas and how to enter them"
>
> I am trying to duplicate the first example and cannot
>
> Ex: =Average(if(C5:C14="Europe",D5:D14))
>
> I tried something similar to this (diff cell ref) and I get #VALUE
>
> Do I need to check off an addin or something?
>
> Thanks,
>
> Brad

[Brad]

Perfect!!!

Why?

"Peo Sjoblom" wrote:

> They need to be entered with ctrl + shift & enter
> select the cell with the formula, press F2, press
> ctrl + shift & enter at the same time
>
> Regards,
>
> Peo Sjoblom
>
> "Brad" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for taking the time to read my quesiton.
> >
> > I typed in the word array into Excel Help and found this item
> >
> > "About array formulas and how to enter them"
> >
> > I am trying to duplicate the first example and cannot
> >
> > Ex: =Average(if(C5:C14="Europe",D5:D14))
> >
> > I tried something similar to this (diff cell ref) and I get #VALUE
> >
> > Do I need to check off an addin or something?
> >
> > Thanks,
> >
> > Brad
>
>
>

[Conrad Carlberg]

"Brad" <[email protected]> wrote in message
news:[email protected]...
> 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

[Brad]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>
>
>

[damorrison]

Thanks alot for those can of worms! Peo!

[Conrad Carlberg]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >
> >
> >

[Harlan Grove]

Conrad Carlberg wrote...
....
>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.)

A complete answer requires full details in each argument for each
built-in function, but certain subsets are known. The following refer
to single function calls. In real formulas with multiple function
calls, if one of those functions requires array entry, the entire
formula requires array entry.


Formulas with only arithmetic or string operators, scalar-valued terms
and array constants don't require array entry, but they'd only return
the top-left entry of the array result.

Functions don't require array entry when using array constants but
neither derived arrays not multiple cell ranges, e.g.,
=SUM({1,2}*{10;100}) returns 330 while =SUM(A1:B1*A2:A3) returns
#VALUE! entered normally and 330 entered as an array formula when A1:B1
= {1,2} and A2:A3 = {10;100}.

LINEST, LOGEST and FREQUENCY, don't require array entry per se.
MINVERSE and TRANSPOSE require array entry except when all their
arguments are array constants. MMULT requires array entry except when
both its arguments are array constants *OR* when its result is a
scalar, e.g., with A1:B1 and A2:A3 as above, =MMULT(A1:B1,A2:A3)
returns 210 entered normally. However, you'll only get the top-left
entry from the array results returned by these functions unless they're
entered as multiple cell array formulas.

ROW and COLUMN *always* return arrays, even when their arguments are
explicit or default references to single cells, and don't on their own
require array entry. Same 'however' as the last paragraph.

A few built-in functions expect array arguments and don't require array
entry, e.g., SUMPRODUCT, LOOKUP, SUMX2MY2, SUMXMY2.

Aggregation functions, e.g., SUM, COUNT, AVERAGE, require array entry
only when some arguments are derived arrays. All other functions
require array entry when some arguments are derived arrays or single
area multiple cell ranges (which evaluate to arrays).

The type testing functions, e.g., ISNUMBER, ISBLANK, ISERROR, don't
require array entry. Same 'however' as previously.

I'm intentionally remaining silent on the functions that return range
references, INDEX, INDIRECT and OFFSET, or have specific arguments that
must be (or are supposed to be) range references, N, T, CELL, SUMIF and
COUNTIF. Their semantics are so screwy that they'd need treatises on
how the type of each argument affects them. Also, the former set of
functions can produce return values that work like arrays of range
references, and the latter set are the only functions that can use such
results.

I'm also remaining silen on the list processing functions, e.g., DSUM,
DCOUNT, because I never use them, so I don't care how they behave.

There are exceptions to these rules, but you'll have to work to find
them.

>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. . . .
....

Not entirely correct. See above. Also, e.g.,

=SUMPRODUCT(TRANSPOSE({1,2,3}),10^ROW(1:3))

returns 3210.

[Conrad Carlberg]

Hi Harlan,

I still don't have a "crisp" definition (g), although I haven't before seen
a listing such as you provide. I doubt that a crisp definition exists.

I would quibble with

>LINEST, LOGEST and FREQUENCY, don't require array entry per se.

Formally and syntactically, that's true, but as a practical matter array
entry is required -- how many times have we seen users in the ngs frustrated
because they couldn't seem to get the full results promised by the
documentation? As you subsequently point out,

>However, you'll only get the top-left
>entry from the array results returned by these functions unless they're
>entered as multiple cell array formulas.

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005

[Harlan Grove]

Conrad Carlberg wrote...
>Hi Harlan,
>
>I still don't have a "crisp" definition (g), although I haven't before seen
>a listing such as you provide. I doubt that a crisp definition exists.
>
>I would quibble with
>
>>LINEST, LOGEST and FREQUENCY, don't require array entry per se.
>
>Formally and syntactically, that's true, but as a practical matter array
>entry is required -- how many times have we seen users in the ngs frustrated
>because they couldn't seem to get the full results promised by the
>documentation? As you subsequently point out,
....

Failure to read the full documentation on array formulas is whose
fault? Microsoft's, which includes the following text in online help
for MMULT:

"Note The formula in the example must be entered as an array formula.
After copying the example to a blank worksheet, select the range A8:B9
starting with the formula cell. Press F2, and then press
CTRL+SHIFT+ENTER. If the formula is not entered as an array formula,
the single result is 2."

Or, just perhaps, the users?

As they say in other newsgroups, RTM, and when that doesn't work, RTFM.

The need for array entry *should* be obvious when an array result is
expected.

Finally, there are contexts when LINEST or LOGEST could be used in
formulas to produce scalar results. If those formulas only require
SUMPRODUCT or similar functions, then the entire formula may not
require array entry.