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.

--