Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 45
Default Sum product formula with conditions

Hi all
Good day to everyone

I have a excel sheet
Column 1 is Department
Column 2 is weightage
Column 3 is rating of project
Column 4 is filled with either 65% or 35%

I want to sum product of column 2 and 3 based on conditions of column 1 and 2

Can someone share the logic how I can derive this
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Sum product formula with conditions

Simply use sumif, and / or
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 45
Default Sum product formula with conditions

Sumif is sum of selected values based on condition

Here I want to multiply column 2 respective weightage and column 3 respective ratings based on department. Some department 65% payment & remaining department 35%
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 18
Default Sum product formula with conditions

On Tuesday, 16 July 2019 04:01:30 UTC+1, TIMOTHY wrote:
Sumif is sum of selected values based on condition

Here I want to multiply column 2 respective weightage and column 3 respective ratings based on department. Some department 65% payment & remaining department 35%


Hi
Isn't it just
=B2*C2*D2
since column D contains the Percentage relevant to the Department.
Column D could be a Lookup to a table of Departments and Percentages.
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 45
Default Sum product formula with conditions

Thank you Alan & Rover

I have searched on google and found out the formula

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

In this case Range3 & 4 are column 2&3


  #6   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Sum product formula with conditions

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.

--



  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Sum product formula with conditions

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.


The double negative refs only negative values in the specified ranges. In other
uses it forces a negative value.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #8   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Sum product formula with conditions

On 7/17/2019 3:20 PM, GS 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.


The double negative refs only negative values in the specified ranges.
In other uses it forces a negative value.


Not what I observed in the earlier cases wherein I was looking at how to
do some fairly complicated similar things...extensive testing appeared
to show it is simply what it is--a double negation that leaves the
result unchanged.

It was used by Klaus in his answers apparently to force a reference that
without Excel did not return values. NUM() is much cleaner for the purpose.

I'd surely be interested in seeing any reference that explains any other
interpretation for the syntax--I was unable to find it anywhere in the
documentation.

--



  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Sum product formula with conditions

I'd surely be interested in seeing any reference that explains any other
interpretation for the syntax--I was unable to find it anywhere in the
documentation.


Yeah, you'll do better googling it. Try...

using double negative in Excel formula

I could be wrong about its usage in Excel just off using so many different
spreadsheet apps/controls!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #10   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Sum product formula with conditions

On 7/18/2019 10:53 AM, GS wrote:
*using double negative in Excel formula


"This method is mostly used in SUMPRODUCT formulas to convert the
conditional arrays that evaluates to TRUE/FALSE into 1/0."

Yes, that's exactly what N() is for...instead of the double-negative. I
had forgotten the function name is just N instead of NUM, my bad on that
part.

--



  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 45
Default Sum product formula with conditions

Thank you dbp & GS

  #12   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
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  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 45
Default Sum product formula with conditions

Noted,thanks
  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 88
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  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
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  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 88
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  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
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.

--


  #21   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Sum product formula with conditions

On 7/20/2019 7:47 AM, dpb wrote:
....

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.


Of course, the underlying problem is that the Excel parse engine doesn't
have sufficient intelligence in it to evaluate enclosing () so that one
is forced to write the explicit cast operation in one form or the other.

A "real" programming language doesn't need either...

--


  #22   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 88
Default Sum product formula with conditions

"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




  #23   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Sum product formula with conditions

On 7/21/2019 6:20 AM, Peter T wrote:
"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.


So N() is poorly implemented... :)

How much is actually just function overhead, can you tell in any fashion?

I'm surprised(*); it really shouldn't be that bad at all...

(*) Well, w/ MS one should learn to never be surprised.
--

  #24   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 88
Default Sum product formula with conditions

"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


  #25   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Sum product formula with conditions

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.

--


  #26   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 45
Default Sum product formula with conditions

Thank you Peter, dpb, and GS
  #27   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 88
Default Sum product formula with conditions

"TIMOTHY" wrote in message
Thank you Peter, dpb, and GS


Probably a bit more than you bargained for:)

dpb is of course right clarity is important, particularly when coming back 6
months later. In typical use it's unlikely you'll notice any difference
between N or -- (or similar) so go with whichever you prefer, but keep in
the back of your mind if ever dealing with heavy calculation why they are
not quite the same.

More importantly understand why it's needed, namely because Sumproduct
treats any non-numeric array elements (after resolving) as zero. That's
useful for text but we want any False/True as numeric 0/1

Peter T


  #28   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Sum product formula with conditions

On 7/22/2019 6:25 AM, Peter T wrote:
"TIMOTHY" wrote in message
Thank you Peter, dpb, and GS


Probably a bit more than you bargained for:)

dpb is of course right clarity is important, particularly when coming back 6
months later. In typical use it's unlikely you'll notice any difference
between N or -- (or similar) so go with whichever you prefer, but keep in
the back of your mind if ever dealing with heavy calculation why they are
not quite the same.

More importantly understand why it's needed, namely because Sumproduct
treats any non-numeric array elements (after resolving) as zero. That's
useful for text but we want any False/True as numeric 0/1

Peter T


And, thank you for taking the time to actually do the timings...I'd
never'uve thunk N() could be such a dog...

--

  #29   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 45
Default Sum product formula with conditions

Yes Peter
I got more than what I asked for. Got knowledge from experienced people
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 06:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"