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

Groups > microsoft.public.excel.programming > #111051

Re: Sum product formula with conditions

Show all headers | View raw

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.

--

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web