SUMPRODUCT + CSE

[Epinn]

I haven't really come across any situation that I need to confirm SUMPRODUCT with CSE.

I only read about it in Bob's paper http://www.xldynamic.com/source/xld.SUMPRODUCT.html#examples

Example #3 uses it because of TRANSPOSE.

Any other examples, comments, insights ......

Thanks!

Epinn

 

[Bernie Deitrick]

Epinn,

CSE is needed with SUMPRODUCT when an interior element is an array formula: BUT, any SUMPRODUCT
formula so constructed could be shortened to just a CSE'd SUM formula.

Your example:

=SUMPRODUCT((B5:B63=TRANSPOSE(P4648))*(C5:C63))

returns the same as:

=SUM((B5:B63=TRANSPOSE(P4648))*(C5:C63))


HTH,
Bernie
MS Excel MVP


I haven't really come across any situation that I need to confirm SUMPRODUCT with CSE.

I only read about it in Bob's paper http://www.xldynamic.com/source/xld.SUMPRODUCT.html#examples

Example #3 uses it because of TRANSPOSE.

Any other examples, comments, insights ......

Thanks!

Epinn

 

[Harlan Grove]

Epinn wrote...

I haven't really come across any situation that I need to confirm SUMPRODUCT with
CSE.

It's not SUMPRODUCT that requires array entry, it's other functions
called within the SUMPRODUCT call.

I only read about it in Bob's paper
http://www.xldynamic.com/source/xld.SUMPRODUCT.html#examples

....

As you mentioned, TRANSPOSE requires array entry. Since it's result
would always be an array (worst case a degenerate 1-by-1 array),
requiring array entry makes some sense. OTOH, IF also requires array
entry, meaning formulas obviously returning scalar results like

=MIN(IF(x>0,x))

require array entry.

I don't believe anyone has come up with a comprehensive list of which
functions require array entry, but there are several that don't as long
as they don't include arguments that call other functions that do. That
is, the following return the same result entered normally or as array
formulas.

old (have been in Excel from the beginning)
-----------------------------------------------------------
FREQUENCY
LINEST
LOGEST
TREND
GROWTH

new (were added between Excel 4 and Excel 8 (97))
----------------------------------------------------------------------
SUMPRODUCT
LOOKUP
COUNTIF
SUMIF

Many of these usually return arrays, so they'd only be useful in
nonarray formulas as terms in more complex formulas.

Note that COUNTIF and SUMIF are interesting because they accept
arguments that are effectively arrays of range references.

Other functions appear not to require array entry in some contexts but
not others. Those include functions designed to work with range
references.

INDEX
OFFSET
INDIRECT
N
T

The exact semantics of these functions can only be discovered by trial
& error or reading the Excel newsgroups over a long period of time.
Microsoft has made no serious attempt to fully document these
functions.

As a rough rule-of-thumb, all old functions that have been in Excel
since the beginning except the 5 listed above require array entry to
process array or range arguments correctly when those arguments would
usually be scalars. As for add-in functions, most won't work correctly
with array arguments. Newer functions added from Excel 4 on are a mixed
bag. Some require array entry (the newer stats functions), some don't
(see the 4 above).

 

[Harlan Grove]

Bernie Deitrick wrote...

CSE is needed with SUMPRODUCT when an interior element is an array
formula: . . .

....

Care to reconsider this blanket statement in light of numerous examples
already given in the Excel newsgroups?

WTH, here's another: with E1:E4 containing {1;10;100;1000} and E1:H1
containing {1,2,3,4},

=SUMPRODUCT(E1:E4*E1:H1)

returns 11110 as expected.

SUMPRODUCT has no problem producing expected results WITHOUT array
entry when its arguments consist of single area ranges, array literals
or derived arrays AS LONG AS its arguments don't include any functions
that THEMSELVES require array entry, such as TRANSPOSE and IF, just to
name 2 such.

If you're not going to take any time to think about your response, why
respond?

 

[Bernie Deitrick]

Forgive me, Harlan, for failing to see how using the common meaning of "array formula" - a function
that requires array entry (CSE) to work - is any different that what you're saying....

Bernie

 

[Harlan Grove]

Bernie Deitrick wrote...

Forgive me, Harlan, for failing to see how using the common meaning of "array
formula" - a function that requires array entry (CSE) to work - is any different that
what you're saying....

You're confusing 'formula' and 'function'. There are array formulas
that involve NO function calls. That is, formulas don't require
functions, but functions serve no purpose outside formulas.

If you still don't understand the difference, not much more I can say.

 

[Bernie Deitrick]

Harlan,

For those who may still be interested, could you post an example of a single-cell, non-function
using, array formula that returns a useful result? There are many examples of multi-cell array
formulas that don't use functions, so you can ignore those.

Thanks,
Bernie

 

[Harlan Grove]

Bernie Deitrick wrote...

For those who may still be interested, could you post an example of a single-cell,
non-function using, array formula that returns a useful result? . . .

....

Nope. Where did I say array formulas involving no function calls were
for single cells? You're trying to obfuscate. I'll be precise: there
are no array formulas involving no function calls that would be useful
if entered into a single cell; HOWEVER, there are useful SUMPRODUCT and
LOOKUP formulas that involve no OTHER function calls but DO have array
arguments, that is, their arguments would be array formulas if array
entered as formulas on their own.

I'll remind you of your original statement: "CSE is needed with
SUMPRODUCT when an interior element is an array formula... ." Array
expression would be more precise. You seem to have meant when an
interior element is an array FUNCTION, but even that's not true, e.g.,

=SUMPRODUCT(FREQUENCY(A1:A100,{-1E-307;0}),{1;1000;1000000})

happily returns a scalar result that encodes the count of negative,
zero and positive values in the range A1:A100 with the negative count
in the lowest order 2 digits, the zero count in the middle, and the
positive count in the highest order digits, all without array entry.