"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