Re: Top Ten rules for using Sumproduct ?
From: Aladin Akyurek (akyurek_at_xs4all.nl)
Date: 03/28/04
- Next message: AÑÐ¥: "Re: Help with Formula"
- Previous message: Gerry Cornell: "Re: Hide Comment"
- In reply to: twaccess: "Top Ten rules for using Sumproduct ?"
- Next in thread: Ken Wright: "Re: Top Ten rules for using Sumproduct ?"
- Reply: Ken Wright: "Re: Top Ten rules for using Sumproduct ?"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 28 Mar 2004 12:26:38 +0200
"twaccess >" <<twaccess.13tjz5@excelforum-nospam.com> wrote in message
news:twaccess.13tjz5@excelforum-nospam.com...
> I continue to be fascinated by this function and have printed off copies
> of Ken and Aladin's threads on this subject which make for interesting
> reading.
>
> However I feel a quick top ten list of simple rules would help me to
> avoid making elementary mistakes etc, and maybe of help to others too.
>
> Rule 1 All ranges within sumproduct function must be the same size.
As stated (admittedly, often posited as such), constitutes a cognitive
obstacle...
If you are inclined to use the 'native' syntax of SumProduct, the following
would not work:
=SUMPRODUCT(A1:A4,B2:C4)
although the ranges involved are equally sized in vertical dimension. That
is, SizeOf(A1:A4)=SizeOf(B2:B4).
In such cases, one is advised to drop the 'native' comma syntax and invoke
instead the star syntax:
=SUMPRODUCT(A1:A4*B2:C4)
This formula will produce a #VALUE! error, when there is text (including a
formula-blank, i.e., "") in the ranges of interest, otherwise it's OK.
The rule can be amended to:
If the refs are of the same shape (equally sized in both dimensions), invoke
SumProduct using the comma-syntax.
If the refs are equally sized in one dimension, invoke SumProduct using the
star syntax.
Note also that a ref (reference argument) can be an array object (like
{1,3,5,7}) or a range object (like F2:F6).
> Rule 2 Ranges cannot be whole columns, i.e. $A:$A, they must be
> specific, i.e. $A2:$A200.
See http://tinyurl.com/3h724 as to the why of this rule.
> Rule 3 Data cannot be read in from ranges outside of the current
> spreadsheet. (This is one I made up and may be incorrect !)
>
This is indeed incorrect.
> What other 'simple' rules do others have to add to this ?
>
> I would exclude syntax items here because they are well covered by
> others like Ken Wright.
>
[...]
You can't exclude the syntax issues if efficieny also matters, in particular
when you want a set of rules of thumb (or heuristic rules). A case in point
is how to OR...
Example:
Let A1:C10 house:
{"Jon",4,1;"Jon",7,1;"Damon",4,2;"Mete",5,1;"Carla",6,1;"Fra",7,2;"Xsa",6,2;
"Tra",8,1;0,6,2;"Qwa",3,1},
where "" stands for an empty cell.
=SUMPRODUCT((A1:A10={"Jon","Damon","Carla","Mete"})*(C1:C10=1)*B1:B10)
or equivalent
=SUMPRODUCT(--ISNUMBER(MATCH(A1:A10,{"Jon","Damon","Carla","Mete"},0)),--(C1
:C10=1),B1:B10)
where L1:O1 houses {"Jon","Damon","Carla","Mete"},
forces one to invoke the star syntax and is an expensive formula because of:
{TRUE,FALSE,FALSE,FALSE;
TRUE,FALSE,FALSE,FALSE;
FALSE,TRUE,FALSE,FALSE;
FALSE,FALSE,FALSE,TRUE;
FALSE,FALSE,TRUE,FALSE;
FALSE,FALSE,FALSE,FALSE;
FALSE,FALSE,FALSE,FALSE;
FALSE,FALSE,FALSE,FALSE;
FALSE,FALSE,FALSE,FALSE;
FALSE,FALSE,FALSE,FALSE}*
{TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE}*
{4;7;4;5;6;7;6;8;6;3}
while
=SUMPRODUCT(--ISNUMBER(MATCH(A1:A10,{"Jon","Damon","Carla","Mete"},0)),B1:B1
0)
involves a lesser amount of computations:
{1;1;1;1;1;0;0;0;0;0}*{1;1;0;1;1;0;0;1;0;1}*{4;7;4;5;6;7;6;8;6;3}
Given the foregoing, a pragmatic rule to add to your list would be:
If the number of criteria that is involved in a conditional > 2, then use
ISNUMBER/MATCH as in --ISNUMBER(MATCH(A1:A10,{"Jon","Damon","Carla","Mete"}.
Otherwise:
If one criteria, use = as in --(A1:A10="Mete"); if two criteria, use + as in
(A1:A10="Mete)+(A1:A10="Jon").
One thing to note though: Formulas with SumProduct have lots in common with
formulas that must be committed with control+shift+enter (often referred to
as array formulas).
- Next message: AÑÐ¥: "Re: Help with Formula"
- Previous message: Gerry Cornell: "Re: Hide Comment"
- In reply to: twaccess: "Top Ten rules for using Sumproduct ?"
- Next in thread: Ken Wright: "Re: Top Ten rules for using Sumproduct ?"
- Reply: Ken Wright: "Re: Top Ten rules for using Sumproduct ?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
