View Single Post
  #20   Report Post  
Old July 20th 19, 01:47 PM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 80
Default Sum product formula with conditions

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.

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").

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.

--