Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #21   Report Post  
Old July 20th 19, 02:50 PM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 79
Default Sum product formula with conditions

On 7/20/2019 7:47 AM, dpb wrote:
....

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.


Of course, the underlying problem is that the Excel parse engine doesn't
have sufficient intelligence in it to evaluate enclosing () so that one
is forced to write the explicit cast operation in one form or the other.

A "real" programming language doesn't need either...

--



  #22   Report Post  
Old July 21st 19, 12:20 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 5
Default Sum product formula with conditions

"dpb" wrote in message
On 7/20/2019 4:34 AM, Peter T wrote:
"dpb" 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.

Peter T




  #23   Report Post  
Old July 21st 19, 01:56 PM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 79
Default Sum product formula with conditions

On 7/21/2019 6:20 AM, Peter T wrote:
"dpb" wrote in message
On 7/20/2019 4:34 AM, Peter T wrote:
"dpb" 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.
--

  #24   Report Post  
Old July 21st 19, 05:18 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 5
Default Sum product formula with conditions

"dpb" 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


  #25   Report Post  
Old July 21st 19, 07:27 PM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 79
Default Sum product formula with conditions

On 7/21/2019 11:18 AM, Peter T wrote:
"dpb" 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.

--


  #26   Report Post  
Old July 22nd 19, 04:47 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 43
Default Sum product formula with conditions

Thank you Peter, dpb, and GS
  #27   Report Post  
Old July 22nd 19, 12:25 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 5
Default Sum product formula with conditions

"TIMOTHY" 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


  #28   Report Post  
Old July 22nd 19, 01:29 PM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 79
Default Sum product formula with conditions

On 7/22/2019 6:25 AM, Peter T wrote:
"TIMOTHY" 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...

--

  #29   Report Post  
Old July 23rd 19, 04:32 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 43
Default Sum product formula with conditions

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Product Code and Product Description setup Nastyashman Excel Worksheet Functions 4 July 6th 09 05:48 PM
I need a product key for my Trail product, 2007 Microsoft Office s sltchsyi Setting up and Configuration of Excel 0 May 4th 09 01:32 AM
Chose a product and update related product variables hupjack Excel Programming 0 April 9th 09 11:57 PM
Product - Conditions - Edited Results chelledc Excel Worksheet Functions 5 July 31st 08 02:32 PM
Vlookup code product and to copy commentary with photo of the product in vba [email protected] Excel Programming 0 October 2nd 06 03:54 AM


All times are GMT +1. The time now is 11:31 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017