Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old July 18th 19, 06:33 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 44
Default Sum product formula with conditions

Thank you dbp & GS


  #12   Report Post  
Old July 18th 19, 07:45 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/18/2019 12:33 PM, TIMOTHY wrote:
Thank you dbp & GS


You're welcome...eye-straining syntax like writing "--" just breaks my
brain to try to look at.

When there's a function for the express purpose to cast to numeric from
other types, why wouldn't anybody use it boggles the mind--unless it is
a relatively recent introduction into Excel which I wouldn't think likely.

--



  #13   Report Post  
Old July 18th 19, 11:43 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/17/2019 11:34 AM, dpb wrote:
On 7/17/2019 9:46 AM, TIMOTHY wrote:
Thank you Alan & Rover

I have searched on google and found out the formula

=sumproduct(--(Range1,criteria1),--(Range2,criteria2),Range3,Range4)


=sumproduct(num(Range1,criteria1),num(Range2,crite ria2),Range3,Range4)

I don't know who started this idiom of a double-negation operator
instead...but it's a least confusing to read if nothing else and seems
less efficient besides.


ERRATUM: The cast-to-numeric function is N(), not NUM(), sorry...

=sumproduct(n(Range1,criteria1),n(Range2,criteria2 ),Range3,Range4)

--

  #14   Report Post  
Old July 19th 19, 03:41 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 44
Default Sum product formula with conditions

Noted,thanks
  #15   Report Post  
Old July 19th 19, 06:52 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 16
Default Sum product formula with conditions

"dpb" wrote in message
On 7/18/2019 12:33 PM, TIMOTHY wrote:
Thank you dbp & GS


You're welcome...eye-straining syntax like writing "--" just breaks my
brain to try to look at.

When there's a function for the express purpose to cast to numeric from
other types, why wouldn't anybody use it boggles the mind


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.

Just butting in

unless it is a relatively recent introduction into Excel which I wouldn't
think likely.


Indeed not recent, 97 if not earlier

Peter T




  #16   Report Post  
Old July 19th 19, 10:04 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/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?


--

  #17   Report Post  
Old July 20th 19, 12:56 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,075
Default Sum product formula with conditions

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.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #18   Report Post  
Old July 20th 19, 01:08 AM 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/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.

One more function reference is in the noise level of the spreadsheet and
(imo) the clarity of intent is far more important for maintainability of
code going forward.

MS gave you a function for the job, use it...

--
  #19   Report Post  
Old July 20th 19, 10:34 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 16
Default Sum product formula with conditions

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

The "--" is a pair of operators, but the N calls a function which does a
whole bunch of stuff only to replicate the double -

One more function reference is in the noise level of the spreadsheet and
(imo) the clarity of intent is far more important for maintainability of
code going forward.


Clarity is subjective. You happen to know what the rarely used N function
does and when you see it know why it's there, great. But most don't, so
might look it up but then it wouldn't explain its purpose in context.
'Personally' when making a similar formula and at first it fails, easier
remember it needs the -- than the name of a function.


MS gave you a function for the job, use it...


Sure, if you want, but understand why it's needed. FWIW MS 'gave us' that
function not because it was needed in Excel but for compatibility with Lotus
123 & Quatro Pro, at a time when they were both better products than Excel.

FWIW I occasionally use the N to add a comment in a formula, eg
=1+2 +N("one + two")

Peter T


  #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.

--


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 02:35 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