Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #111017 > unrolled thread

Sum product formula with conditions

Back to article view | Back to microsoft.public.excel.programming

Contents

  Sum product formula with conditions TIMOTHY <jey.kumar87@gmail.com> - 2019-07-14 21:15 -0700
    Sum product formula with conditions Alan Wong <alanwongwinglun.hk@gmail.com> - 2019-07-15 16:52 -0700
      Sum product formula with conditions TIMOTHY <jey.kumar87@gmail.com> - 2019-07-15 20:01 -0700
        Re: Sum product formula with conditions Roger Govier <rogergovier@gmail.com> - 2019-07-16 08:40 -0700
          Re: Sum product formula with conditions TIMOTHY <jey.kumar87@gmail.com> - 2019-07-17 07:46 -0700
            Re: Sum product formula with conditions dpb <none@none.net> - 2019-07-17 11:34 -0500
              Re: Sum product formula with conditions GS <gs@v.invalid> - 2019-07-17 16:20 -0400
                Re: Sum product formula with conditions dpb <none@none.net> - 2019-07-17 18:58 -0500
                  Re: Sum product formula with conditions GS <gs@v.invalid> - 2019-07-18 11:53 -0400
                    Re: Sum product formula with conditions dpb <none@none.net> - 2019-07-18 11:29 -0500
                      Re: Sum product formula with conditions TIMOTHY <jey.kumar87@gmail.com> - 2019-07-18 10:33 -0700
                        Re: Sum product formula with conditions dpb <none@none.net> - 2019-07-18 13:45 -0500
                          Re: Sum product formula with conditions "Peter T" <askmy@email.com> - 2019-07-19 18:52 +0100
                            Re: Sum product formula with conditions dpb <none@none.net> - 2019-07-19 16:04 -0500
                              Re: Sum product formula with conditions GS <gs@v.invalid> - 2019-07-19 19:56 -0400
                              Re: Sum product formula with conditions dpb <none@none.net> - 2019-07-19 19:08 -0500
                                Re: Sum product formula with conditions "Peter T" <askmy@email.com> - 2019-07-20 10:34 +0100
                                  Re: Sum product formula with conditions dpb <none@none.net> - 2019-07-20 07:47 -0500
                                    Re: Sum product formula with conditions dpb <none@none.net> - 2019-07-20 08:50 -0500
                                    Re: Sum product formula with conditions "Peter T" <askmy@email.com> - 2019-07-21 12:20 +0100
                                      Re: Sum product formula with conditions dpb <none@none.net> - 2019-07-21 07:56 -0500
                                        Re: Sum product formula with conditions "Peter T" <askmy@email.com> - 2019-07-21 17:18 +0100
                                          Re: Sum product formula with conditions dpb <none@none.net> - 2019-07-21 13:27 -0500
                                            Re: Sum product formula with conditions TIMOTHY <jey.kumar87@gmail.com> - 2019-07-21 20:47 -0700
                                              Re: Sum product formula with conditions "Peter T" <askmy@email.com> - 2019-07-22 12:25 +0100
                                                Re: Sum product formula with conditions dpb <none@none.net> - 2019-07-22 07:29 -0500
                                                  Re: Sum product formula with conditions TIMOTHY <jey.kumar87@gmail.com> - 2019-07-22 20:32 -0700
              Re: Sum product formula with conditions dpb <none@none.net> - 2019-07-18 17:43 -0500
                Re: Sum product formula with conditions TIMOTHY <jey.kumar87@gmail.com> - 2019-07-19 07:41 -0700

Page 2 of 2 — ← Prev page 1 [2]

#111047

On 7/21/2019 6:20 AM, Peter T wrote:
> "dpb" <none@none.net> wrote in message
>> On 7/20/2019 4:34 AM, Peter T wrote:
>>> "dpb" <none@none.net> wrote in message
>>>>> On 7/19/2019 12:52 PM, Peter T wrote:
>>>>> ...
>>>>>
>>>>>> I wouldn't want to discourage your use of the N function, however I
>>>>>> would
>>>>>> suggest others new this to consider using the 'double unary' as more
>>>>>> efficient.
>>>>> ...
>>>>>
>>>>> How so?
>>>>
>>>>> Double unary is more direct; - the N() function has to evaluate and so
>>>>> carries that extra overhead.
>>>>
>>>> I seriously doubt one could measure the difference in comparison to the
>>>> rest of the function--and the two negation operations are probably as
>>>> costly as the one store--in fact, good possibility the code boils down
>>>> to
>>>> the same thing in the end.
>>>
>>> In typical usage yes of course the difference is trivial, both in terms
>>> of
>>> storage and efficiency. However repeated in many 1000s of cells where a
>>> sheet recalc can take seconds, or a simulation which could take miutes if
>>> not hours the difference could be significant.
>>
>> Possible, but I seriously doubt it would be able to be shown to be the
>> bottleneck in any process.  Would take a profiler to prove it to me.
> 
> I doubt it'd be a bottleneck either and not what I meant.
> 
>>> The "--" is a pair of operators, but the N calls a function which does a
>>> whole bunch of stuff only to replicate the double -
>> ...
>>
>> Don't know how it's implemented, either.  It is a function, but all it has
>> to do is a fetch of the content.  The operators have to eventually do the
>> same thing -- the cell content is still the same logical or text or
>> whatever it is; necessarily the operator also has to correctly deal with
>> it inside its own code ("there is no free lunch").
> 
> Calling N looks up the function, 'evaluates' the expression, special
> handling for text to retun a zero (-- would error), and no doubt more. At
> it's simplest all a - operator might do is flip the first bit, though here a
> bit more as it's to coerce the boolean to it's numeric value. Not a free
> lunch but cheap one!
> 
> Sumproduct works with arrays, each element of the array is processed
> individually with N or --, so potentially there could be many N calls with
> only one apparant use of N
> 
>> If the use of a double negation is such a highly recommended
>> functionality, it's interesting it never is mentioned as being needed or
>> the manner in which one should cast the logical to numeric in the
>> documentation for SUMIF() and friends where it seemingly is most
>> prevalent.
> 
> There are many ways to coerce the booleans, apart from -- and N, but I have
> never seen any one method "highly recommended" over any other. Way back the
> preference was for -- as it was demonstrably faster and could encroach on
> the nested function limit. I agree in typical usage not an issue so go for
> personal preference, but when stretching resources why not go for the most
> efficient.
> 
> I wouldn't expect the following to take more than a second even in an old
> system but should be enough to illustrate:
> 
> Private Declare Function GetTickCount Lib "kernel32.dll" () As Long
> 
> Sub abc()
> Dim i&, t%, s$, f$
>      For i = 1 To 4
>          Select Case i
>          Case 1: f = "N"
>          Case 2: f = "1*"
>          Case 3: f = "0+"
>          Case 4: f = "--"
>          End Select
> 
>          s = Replace("=SUMPRODUCT(#(A:A=1))", "#", f)
>          t = GetTickCount
>          Range("c1").Formula = s
> 
>          Debug.Print GetTickCount - t, f
>      Next
> End Sub
> 
> In my 2016/32 the 1* and 0+ results were about 25% slower than --, but N was
> 90% slower (more than I expected from memory). Bearing in mind most of the
> work of this simple formula is comparing the contents of a million cells and
> counting the matches, the N accounts for a disportionate amount of the work.

So N() is poorly implemented... :)

How much is actually just function overhead, can you tell in any fashion?

I'm surprised(*); it really shouldn't be that bad at all...

(*) Well, w/ MS one should learn to never be surprised.
--

[toc] | [prev] | [next] | [standalone]

#111049

"dpb" <none@none.net> wrote in message

>>
>> Private Declare Function GetTickCount Lib "kernel32.dll" () As Long
>>
>> Sub abc()
>> Dim i&, t%, s$, f$
>>      For i = 1 To 4
>>          Select Case i
>>          Case 1: f = "N"
>>          Case 2: f = "1*"
>>          Case 3: f = "0+"
>>          Case 4: f = "--"
>>          End Select
>>
>>          s = Replace("=SUMPRODUCT(#(A:A=1))", "#", f)
>>          t = GetTickCount
>>          Range("c1").Formula = s
>>
>>          Debug.Print GetTickCount - t, f
>>      Next
>> End Sub
>>
>> In my 2016/32 the 1* and 0+ results were about 25% slower than --, but N 
>> was
>> 90% slower (more than I expected from memory). Bearing in mind most of 
>> the
>> work of this simple formula is comparing the contents of a million cells 
>> and
>> counting the matches, the N accounts for a disportionate amount of the 
>> work.
>
> So N() is poorly implemented... :)

Er, depends, if you mean by MS I wouldn't say so, but if you mean used in 
cell formulas to the extent the N makes recalc noticably slower than it need 
be with -- than yes..:)

> How much is actually just function overhead, can you tell in any fashion?

If the only thing the N does is serve as a wrapper for -- we could  probably 
work it out. But pretty sure internally it does a lot more than simply that, 
in addition to any overhead of calling the function.

What we could do is compare N to change the booleans to 0s  & 1s v. -- only 
to coearce the booleans. In the example quoted above increase the loop from 
4 to 5 and add an extra Case

         Case 5: f = ""

Subtract the case-5 time from each of the case-1 and 4 times and should get 
a fair indication of the net cost of N and -- respectively.

> I'm surprised(*); it really shouldn't be that bad at all...

Why surprised and why 'that bad'?

Although end result is the same not comparing like with like.

> (*) Well, w/ MS one should learn to never be surprised.

Sometimes for sure, though I don't see anything poor or unexpected with this 
one:)


FWIW my results in a relatively modern 2016 and an old 2007 system

2016   2007
 110     938   N
   93     859   1*
   94     845   0+
   63     720   --
   46     594

   64     344   net N time
   17     126   net -- time

Peter T 

[toc] | [prev] | [next] | [standalone]

#111051

On 7/21/2019 11:18 AM, Peter T wrote:
> "dpb" <none@none.net> wrote in message
> 
>>>
>>> Private Declare Function GetTickCount Lib "kernel32.dll" () As Long
>>>
>>> Sub abc()
>>> Dim i&, t%, s$, f$
>>>       For i = 1 To 4
>>>           Select Case i
>>>           Case 1: f = "N"
>>>           Case 2: f = "1*"
>>>           Case 3: f = "0+"
>>>           Case 4: f = "--"
>>>           End Select
>>>
>>>           s = Replace("=SUMPRODUCT(#(A:A=1))", "#", f)
>>>           t = GetTickCount
>>>           Range("c1").Formula = s
>>>
>>>           Debug.Print GetTickCount - t, f
>>>       Next
>>> End Sub
>>>
>>> In my 2016/32 the 1* and 0+ results were about 25% slower than --, but N
>>> was
>>> 90% slower (more than I expected from memory). Bearing in mind most of
>>> the
>>> work of this simple formula is comparing the contents of a million cells
>>> and
>>> counting the matches, the N accounts for a disportionate amount of the
>>> work.
>>
>> So N() is poorly implemented... :)
> 
> Er, depends, if you mean by MS I wouldn't say so, but if you mean used in
> cell formulas to the extent the N makes recalc noticably slower than it need
> be with -- than yes..:)
> 
>> How much is actually just function overhead, can you tell in any fashion?
> 
> If the only thing the N does is serve as a wrapper for -- we could  probably
> work it out. But pretty sure internally it does a lot more than simply that,
> in addition to any overhead of calling the function.
> 
> What we could do is compare N to change the booleans to 0s  & 1s v. -- only
> to coearce the booleans. In the example quoted above increase the loop from
> 4 to 5 and add an extra Case
> 
>           Case 5: f = ""
> 
> Subtract the case-5 time from each of the case-1 and 4 times and should get
> a fair indication of the net cost of N and -- respectively.
> 
>> I'm surprised(*); it really shouldn't be that bad at all...
> 
> Why surprised and why 'that bad'?
> 
> Although end result is the same not comparing like with like.
> 
>> (*) Well, w/ MS one should learn to never be surprised.
> 
> Sometimes for sure, though I don't see anything poor or unexpected with this
> one:)
> 
> 
> FWIW my results in a relatively modern 2016 and an old 2007 system
> 
> 2016   2007
>   110     938   N
>     93     859   1*
>     94     845   0+
>     63     720   --
>     46     594
> 
>     64     344   net N time
>     17     126   net -- time
> 
> Peter T

Not having internals to look at, I'll retire with the comment I can't 
believe it couldn't be optimized significantly.

I'll continue to believe "--" is an ugly hack altho apparently given the 
how MS has implemented N() one that is understandable why it has ended 
up being adopted.

Clarity in code is a prime goal; this is not the route towards that; it 
_looks_ more like obfuscation.

I'm not an Excel user for the most part; only when forced and in trying 
to improve some complicated spreadsheets the organization for which was 
doing some pro bono work for needed a couple complex SUMIF() constructs 
I had trouble getting to work did I ever see the idiom.  While I've 
coded for 40+ yrs, it stumped me as to "why?" anybody would write such 
thinking at first it was as GS's first answer seems to imply there's 
some magic meaning to the double-minus rather than just being a double 
negation serving to cast the logical to numeric.  When I realized that 
was the point, seemed the next logical thing to do is to use the 
MS-supplied function for the purpose.

For case such as I've got that aren't huge in magnitude; just overly 
complicated and very inefficient for their end purpose I'll continue 
with N() because I can remember what it does; I may or may not when come 
back to -- a year from now.  And certainly no one in the organization 
would have a klew what it would mean and they too can look up the 
function in the function help list...

In summary, your point is taken; I'm still surprised by the result and 
would consider it a poor quality of implementation issue on several levels.

--

[toc] | [prev] | [next] | [standalone]

#111052

Thank you Peter, dpb, and GS

[toc] | [prev] | [next] | [standalone]

#111053

"TIMOTHY" <jey.kumar87@gmail.com> wrote in message
> Thank you Peter, dpb, and GS

Probably a bit more than you bargained for:)

dpb is of course right clarity is important, particularly when coming back 6 
months later. In typical use it's unlikely you'll notice any difference 
between N or -- (or similar) so go with whichever you prefer, but keep in 
the back of your mind if ever dealing with heavy calculation why they are 
not quite the same.

More importantly understand why it's needed, namely because Sumproduct 
treats any non-numeric array elements (after resolving) as zero. That's 
useful for text but we want any False/True as numeric 0/1

Peter T 

[toc] | [prev] | [next] | [standalone]

#111054

On 7/22/2019 6:25 AM, Peter T wrote:
> "TIMOTHY" <jey.kumar87@gmail.com> wrote in message
>> Thank you Peter, dpb, and GS
> 
> Probably a bit more than you bargained for:)
> 
> dpb is of course right clarity is important, particularly when coming back 6
> months later. In typical use it's unlikely you'll notice any difference
> between N or -- (or similar) so go with whichever you prefer, but keep in
> the back of your mind if ever dealing with heavy calculation why they are
> not quite the same.
> 
> More importantly understand why it's needed, namely because Sumproduct
> treats any non-numeric array elements (after resolving) as zero. That's
> useful for text but we want any False/True as numeric 0/1
> 
> Peter T

And, thank you for taking the time to actually do the timings...I'd 
never'uve thunk N() could be such a dog...

--

[toc] | [prev] | [next] | [standalone]

#111055

Yes Peter 
I got more than what I asked for. Got knowledge from experienced people 

[toc] | [prev] | [next] | [standalone]

#111030

On 7/17/2019 11:34 AM, dpb wrote:
> On 7/17/2019 9:46 AM, TIMOTHY wrote:
>> Thank you Alan & Rover
>>
>> I have searched on google and found out the formula
>>
>> =sumproduct(--(Range1,criteria1),--(Range2,criteria2),Range3,Range4)
> 
> =sumproduct(num(Range1,criteria1),num(Range2,criteria2),Range3,Range4)
> 
> I don't know who started this idiom of a double-negation operator 
> instead...but it's a least confusing to read if nothing else and seems 
> less efficient besides.

ERRATUM:  The cast-to-numeric function is N(), not NUM(), sorry...

=sumproduct(n(Range1,criteria1),n(Range2,criteria2),Range3,Range4)

--

[toc] | [prev] | [next] | [standalone]

#111031

Noted,thanks

[toc] | [prev] | [standalone]

Page 2 of 2 — ← Prev page 1 [2]

Back to top | Article view | microsoft.public.excel.programming

csiph-web