Array Functions - Two Questions
 

I'm using Excel 2000 Professional, and I'm experimenting with using the
SUMPRODUCT() function to test for multiple-criterion conditions. In the
documentation I found, it says that SUMPRODUCT is "an array function, and is
so committed by pressing Cntl-Shift-Enter".

When I do that, it adds the curly braces to either side of the function like
the documentation said it would.

However, the function seems to work OK even BEFORE I do that. So I guess my
first question is, what is thise whole "Cntl-Shift-Enter" thing all about?
Why is it needed?

My second question - is support for array functions bult into Excel? I'm
going to be giving this sheet to a bunch of people who have Excel 2000
STANDARD (I have assurances that there's no one on a version earlier than
2000). Would SUMPRODUCT still work the same way on their PCs? I intend to
test this myself, but I want to know if there are any special situations or
circumstances I should check for.

Thx.
--
Hmm...they have the Internet on COMPUTERS now!

Formulas with SUMPRODUCT do almost never require control+shift+enter for
it is built to operate on computed arrays. It shares this capability
with functions like LOOKUP and FREQUENCY.

If you compose formulas with SUM, COUNT, MAX, IF, etc. that must operate
on computed arrays, you'll need to signal Excel that intention with
control+shift+enter.

And yes, these are built-in features.

MDW wrote:
I'm using Excel 2000 Professional, and I'm experimenting with using the
SUMPRODUCT() function to test for multiple-criterion conditions. In the
documentation I found, it says that SUMPRODUCT is "an array function, and is
so committed by pressing Cntl-Shift-Enter".

When I do that, it adds the curly braces to either side of the function like
the documentation said it would.

However, the function seems to work OK even BEFORE I do that. So I guess my
first question is, what is thise whole "Cntl-Shift-Enter" thing all about?
Why is it needed?

My second question - is support for array functions bult into Excel? I'm
going to be giving this sheet to a bunch of people who have Excel 2000
STANDARD (I have assurances that there's no one on a version earlier than
2000). Would SUMPRODUCT still work the same way on their PCs? I intend to
test this myself, but I want to know if there are any special situations or
circumstances I should check for.

Thx.

Very clearly explained. Thank you.

"Aladin Akyurek" wrote:

Formulas with SUMPRODUCT do almost never require control+shift+enter for
it is built to operate on computed arrays. It shares this capability
with functions like LOOKUP and FREQUENCY.

If you compose formulas with SUM, COUNT, MAX, IF, etc. that must operate
on computed arrays, you'll need to signal Excel that intention with
control+shift+enter.

And yes, these are built-in features.

MDW wrote:
I'm using Excel 2000 Professional, and I'm experimenting with using the
SUMPRODUCT() function to test for multiple-criterion conditions. In the
documentation I found, it says that SUMPRODUCT is "an array function, and is
so committed by pressing Cntl-Shift-Enter".

When I do that, it adds the curly braces to either side of the function like
the documentation said it would.

However, the function seems to work OK even BEFORE I do that. So I guess my
first question is, what is thise whole "Cntl-Shift-Enter" thing all about?
Why is it needed?

My second question - is support for array functions bult into Excel? I'm
going to be giving this sheet to a bunch of people who have Excel 2000
STANDARD (I have assurances that there's no one on a version earlier than
2000). Would SUMPRODUCT still work the same way on their PCs? I intend to
test this myself, but I want to know if there are any special situations or
circumstances I should check for.

Thx.

MDW wrote...
I'm using Excel 2000 Professional, and I'm experimenting with using
the
SUMPRODUCT() function to test for multiple-criterion conditions. In
the
documentation I found, it says that SUMPRODUCT is "an array function,
and is
so committed by pressing Cntl-Shift-Enter".
....

Where the h*ll did you read that? Not from anyone who bothered to read
their keyboard (it's Ctrl, not Cntl). Not from any Microsoft source
since Microsoft's English language online help doesn't use the word
'commit' or any of its derivatives ANYWHERE (try searching for it), and
the sensible people writing Excel's English language documentation use
US conventions, in which 'commit' is a term used for transactional
databases, marriage councelling and psychological evaluations leading
to involuntary custody exclusively.

Anyway, SUMPRODUCT with no function calls in any of its arguments need
not be entered as an array formula (or array-entered). SUMPRODUCT with
most built-in functions in its arguments also need not be entered as an
array formula. IF is the major exception. Excel's IF function is
stupider than nearly all its other built-in functions - the only way it
handles array arguments properly is in array formulas. If you need to
use IF, there's no point using SUMPRODUCT.

My second question - is support for array functions bult into Excel?
I'm
going to be giving this sheet to a bunch of people who have Excel 2000

STANDARD (I have assurances that there's no one on a version earlier
than
2000). Would SUMPRODUCT still work the same way on their PCs? I intend
to
test this myself, but I want to know if there are any special
situations or
circumstances I should check for.

There's no Excel 2000 Professional or Standard. There's only Excel
2000. It's bundled into Office 2000 Professional and Standard (and
possibly others), but the difference between these Office versions is
whether Access is included (Professional) or not (Standard and the
others).

SUMPRODUCT works as I've described above in all Excel versions from
Excel 5/95 on. SUMPRODUCT, LOOKUP and FREQUENCY are the only built-in
functions that seem always to work with derived array arguments in
non-array formulas as long as you avoid certain built-in functions
(like IF). Also, *all* versions of Excel support array formulas.