"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