Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A SUMPRODUCT question
On the very useful site http://www.xldynamic.com/source/xld.SUMPRODUCT.html
there is a note that the arrays in SUMPRODUCT formulas must be the same size. Sounds sensible enough. But today when look at the post with subject "Why does this formula return a #VALUE error", we have one array of 12 by 1 and another of 12 by 12. I have been using the double negative method of converting Boolean to {0,1} but if I try =SUMPRODUCT(--(A1:F1="a"),A2:F3) I get a #VALUE error (note the 1 by 1 and the 1 by 2 arrays) But =SUMPRODUCT((A1:F1="a")*(A2:F3)) works! So when is double negation (--) better than * (multiplication) ? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
A SUMPRODUCT question
Does this help at all?
http://tinyurl.com/y9u5qp -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Bernard Liengme" wrote in message ... On the very useful site http://www.xldynamic.com/source/xld.SUMPRODUCT.html there is a note that the arrays in SUMPRODUCT formulas must be the same size. Sounds sensible enough. But today when look at the post with subject "Why does this formula return a #VALUE error", we have one array of 12 by 1 and another of 12 by 12. I have been using the double negative method of converting Boolean to {0,1} but if I try =SUMPRODUCT(--(A1:F1="a"),A2:F3) I get a #VALUE error (note the 1 by 1 and the 1 by 2 arrays) But =SUMPRODUCT((A1:F1="a")*(A2:F3)) works! So when is double negation (--) better than * (multiplication) ? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
A SUMPRODUCT question
I does explain that the 'star' syntax treats Array1*Array2 in a way similar
to MMULTI (explains that it DOES but not WHY, nor why the 'double neg' does not) But I have learnt (the old fashioned spelling should tell you something!) a new thing today, so I shall enjoy my single malt tonight with a clear conscience! Many thanks, RagsDyeR best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "RagDyeR" wrote in message ... Does this help at all? http://tinyurl.com/y9u5qp -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Bernard Liengme" wrote in message ... On the very useful site http://www.xldynamic.com/source/xld.SUMPRODUCT.html there is a note that the arrays in SUMPRODUCT formulas must be the same size. Sounds sensible enough. But today when look at the post with subject "Why does this formula return a #VALUE error", we have one array of 12 by 1 and another of 12 by 12. I have been using the double negative method of converting Boolean to {0,1} but if I try =SUMPRODUCT(--(A1:F1="a"),A2:F3) I get a #VALUE error (note the 1 by 1 and the 1 by 2 arrays) But =SUMPRODUCT((A1:F1="a")*(A2:F3)) works! So when is double negation (--) better than * (multiplication) ? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
A SUMPRODUCT question
My understanding of the double negation syntax is that it converts text into
numbers (i.e., TRUE or FALSE) and that calculations/logical operations can thereby be run on that conversion. For that purpose, I can see why you would need the arrays to be a similar size. But for the purposes I was using SUMPRODUCT for in the original post, I don't see why the arrays would have to be of similar dimensions. And, as you found out, they don't.... Dave -- Brevity is the soul of wit. "Bernard Liengme" wrote: I does explain that the 'star' syntax treats Array1*Array2 in a way similar to MMULTI (explains that it DOES but not WHY, nor why the 'double neg' does not) But I have learnt (the old fashioned spelling should tell you something!) a new thing today, so I shall enjoy my single malt tonight with a clear conscience! Many thanks, RagsDyeR best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "RagDyeR" wrote in message ... Does this help at all? http://tinyurl.com/y9u5qp -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Bernard Liengme" wrote in message ... On the very useful site http://www.xldynamic.com/source/xld.SUMPRODUCT.html there is a note that the arrays in SUMPRODUCT formulas must be the same size. Sounds sensible enough. But today when look at the post with subject "Why does this formula return a #VALUE error", we have one array of 12 by 1 and another of 12 by 12. I have been using the double negative method of converting Boolean to {0,1} but if I try =SUMPRODUCT(--(A1:F1="a"),A2:F3) I get a #VALUE error (note the 1 by 1 and the 1 by 2 arrays) But =SUMPRODUCT((A1:F1="a")*(A2:F3)) works! So when is double negation (--) better than * (multiplication) ? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct Question | Excel Discussion (Misc queries) | |||
Sumproduct Question | Excel Discussion (Misc queries) | |||
Question about sumproduct | Excel Discussion (Misc queries) | |||
Question about sumproduct | Excel Discussion (Misc queries) | |||
sumproduct question | Excel Worksheet Functions |