If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SumProduct Query
Hi! Experts What is the Difference Between these Sumproduct Function It show the Same Results What is the Difference Between "--" in First Function and "*" in Second Function 1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61) 2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61)) Thanks in Advance Hardeep Kanwar -- hardeep.kanwar ------------------------------------------------------------------------ hardeep.kanwar's Profile: http://www.thecodecage.com/forumz/member.php?userid=170 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106459 |
#2
|
|||
|
|||
SumProduct Query
"hardeep.kanwar" wrote:
What is the Difference Between "--" in First Function and "*" in Second Function 1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61) 2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61)) There is no logical difference. "--" (double-negation) is merely a way to convert boolean results (TRUE, FALSE) into a number, which SUMPRODUCT requires. But any arithmetic operation will do the same. Moreover, SUMPRODUCT(x,y,z) is logically the same as SUMPRODUCT(x*y*z). However, there is an operational difference that may or may not matter, depending on the situation. If any of B54:B62 is text, SUMPRODUCT(...*B54:B61) will fail with a #VALUE error, whereas SUMPRODUCT(...,B54:B61) will not. The reason is that SUMPRODUCT is designed to tolerate text, whereas arithmetic expressions do not. ----- original message ----- "hardeep.kanwar" wrote in message ... Hi! Experts What is the Difference Between these Sumproduct Function It show the Same Results What is the Difference Between "--" in First Function and "*" in Second Function 1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61) 2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61)) Thanks in Advance Hardeep Kanwar -- hardeep.kanwar ------------------------------------------------------------------------ hardeep.kanwar's Profile: http://www.thecodecage.com/forumz/member.php?userid=170 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106459 |
#3
|
|||
|
|||
SumProduct Query
PS....
I wrote: If any of B54:B62 is text Obvious typo; should be B54:B61. If any of B54:B62 is text, SUMPRODUCT(...*B54:B61) will fail with a #VALUE error, whereas SUMPRODUCT(...,B54:B61) will not. So I would write: SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday")*( D5461="Sam"),B54:B61) This avoids "--", which is superfluous in the case, but it separates B54:B61 to avoid any #VALUE error. ----- original message ----- "JoeU2004" wrote in message ... "hardeep.kanwar" wrote: What is the Difference Between "--" in First Function and "*" in Second Function 1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61) 2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61)) There is no logical difference. "--" (double-negation) is merely a way to convert boolean results (TRUE, FALSE) into a number, which SUMPRODUCT requires. But any arithmetic operation will do the same. Moreover, SUMPRODUCT(x,y,z) is logically the same as SUMPRODUCT(x*y*z). However, there is an operational difference that may or may not matter, depending on the situation. If any of B54:B62 is text, SUMPRODUCT(...*B54:B61) will fail with a #VALUE error, whereas SUMPRODUCT(...,B54:B61) will not. The reason is that SUMPRODUCT is designed to tolerate text, whereas arithmetic expressions do not. ----- original message ----- "hardeep.kanwar" wrote in message ... Hi! Experts What is the Difference Between these Sumproduct Function It show the Same Results What is the Difference Between "--" in First Function and "*" in Second Function 1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61) 2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61)) Thanks in Advance Hardeep Kanwar -- hardeep.kanwar ------------------------------------------------------------------------ hardeep.kanwar's Profile: http://www.thecodecage.com/forumz/member.php?userid=170 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106459 |
#4
|
|||
|
|||
SumProduct Query
JoeU2004;380596 Wrote: "hardeep.kanwar" wrote: What is the Difference Between "--" in First Function and "*" in Second Function 1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61) 2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61)) There is no logical difference. "--" (double-negation) is merely a way to convert boolean results (TRUE, FALSE) into a number, which SUMPRODUCT requires. But any arithmetic operation will do the same. Moreover, SUMPRODUCT(x,y,z) is logically the same as SUMPRODUCT(x*y*z). However, there is an operational difference that may or may not matter, depending on the situation. If any of B54:B62 is text, SUMPRODUCT(...*B54:B61) will fail with a #VALUE error, whereas SUMPRODUCT(...,B54:B61) will not. The reason is that SUMPRODUCT is designed to tolerate text, whereas arithmetic expressions do not. ----- original message ----- "hardeep.kanwar" wrote in message ... Hi! Experts What is the Difference Between these Sumproduct Function It show the Same Results What is the Difference Between "--" in First Function and "*" in Second Function 1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61) 2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61)) Thanks in Advance Hardeep Kanwar -- hardeep.kanwar ------------------------------------------------------------------------ hardeep.kanwar's Profile: 'The Code Cage Forums - View Profile: hardeep.kanwar' (http://www.thecodecage.com/forumz/member.php?userid=170) View this thread: 'SumProduct Query - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=106459) Thanks sir Its Really Help Full for me Which is the best one -- hardeep.kanwar ------------------------------------------------------------------------ hardeep.kanwar's Profile: http://www.thecodecage.com/forumz/member.php?userid=170 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106459 |
#5
|
|||
|
|||
SumProduct Query
"hardeep.kanwar" wrote:
1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61) 2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61)) [....] Which is the best one I do not measure any significant performance difference using a range of 60000 cells. As I noted previously, ",B54:B61" is better than "*B54:B61" if you expect that some cells have text (e.g. ""). The macro below demonstrates the measurement procedure. Note that the SUMPRODUCT formula is assumed to be in A1. Option Explicit Private Declare Sub Sleep Lib "kernel32" (ByVal msec As Long) Private Declare Function QueryPerformanceFrequency Lib "kernel32" (ByRef clkFreq As Currency) As Boolean Private Declare Function QueryPerformanceCounter Lib "kernel32" (ByRef clkCount As Currency) As Boolean Sub doit() Const n As Integer = 30 Dim freq As Currency, st As Currency, et As Currency, ct As Currency Dim mint As Currency, maxt As Currency Dim i As Integer, x As Long, calcSave, iterSave Dim rng As Range calcSave = Application.Calculation iterSave = Application.Iteration Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.Iteration = False Set rng = Range("a1") x = QueryPerformanceFrequency(freq) Debug.Print "-----" mint = 999999999 For i = 0 To n Call Sleep(30) 'new sched quantum for each iteration x = QueryPerformanceCounter(st) rng.Calculate x = QueryPerformanceCounter(et) If i 0 Then 'skip first time, affected by VBA overhead et = et - st 'elapased time ct = ct + et 'cumulative elapsed time for average If et maxt Then maxt = et If et mint Then mint = et End If Next i ' do ctrl-G to see results Debug.Print Format(ct / freq / n * 1000, "0.000000 msec"); Debug.Print ", min "; Format(mint / freq * 1000, "0.000000"); Debug.Print ", max "; Format(maxt / freq * 1000, "0.000000") Application.Iteration = iterSave Application.Calculation = calcSave Application.ScreenUpdating = True End Sub ----- original message ----- "hardeep.kanwar" wrote in message ... JoeU2004;380596 Wrote: "hardeep.kanwar" wrote: What is the Difference Between "--" in First Function and "*" in Second Function 1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61) 2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61)) There is no logical difference. "--" (double-negation) is merely a way to convert boolean results (TRUE, FALSE) into a number, which SUMPRODUCT requires. But any arithmetic operation will do the same. Moreover, SUMPRODUCT(x,y,z) is logically the same as SUMPRODUCT(x*y*z). However, there is an operational difference that may or may not matter, depending on the situation. If any of B54:B62 is text, SUMPRODUCT(...*B54:B61) will fail with a #VALUE error, whereas SUMPRODUCT(...,B54:B61) will not. The reason is that SUMPRODUCT is designed to tolerate text, whereas arithmetic expressions do not. ----- original message ----- "hardeep.kanwar" wrote in message ... Hi! Experts What is the Difference Between these Sumproduct Function It show the Same Results What is the Difference Between "--" in First Function and "*" in Second Function 1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61) 2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61)) Thanks in Advance Hardeep Kanwar -- hardeep.kanwar ------------------------------------------------------------------------ hardeep.kanwar's Profile: 'The Code Cage Forums - View Profile: hardeep.kanwar' (http://www.thecodecage.com/forumz/member.php?userid=170) View this thread: 'SumProduct Query - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=106459) Thanks sir Its Really Help Full for me Which is the best one -- hardeep.kanwar ------------------------------------------------------------------------ hardeep.kanwar's Profile: http://www.thecodecage.com/forumz/member.php?userid=170 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106459 |
#6
|
|||
|
|||
SumProduct Query
There are some calculation timer procedures written by Charles Williams
posted in this article: http://msdn2.microsoft.com/en-us/library/aa730921.aspx -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... "hardeep.kanwar" wrote: 1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61) 2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61)) [....] Which is the best one I do not measure any significant performance difference using a range of 60000 cells. As I noted previously, ",B54:B61" is better than "*B54:B61" if you expect that some cells have text (e.g. ""). The macro below demonstrates the measurement procedure. Note that the SUMPRODUCT formula is assumed to be in A1. Option Explicit Private Declare Sub Sleep Lib "kernel32" (ByVal msec As Long) Private Declare Function QueryPerformanceFrequency Lib "kernel32" (ByRef clkFreq As Currency) As Boolean Private Declare Function QueryPerformanceCounter Lib "kernel32" (ByRef clkCount As Currency) As Boolean Sub doit() Const n As Integer = 30 Dim freq As Currency, st As Currency, et As Currency, ct As Currency Dim mint As Currency, maxt As Currency Dim i As Integer, x As Long, calcSave, iterSave Dim rng As Range calcSave = Application.Calculation iterSave = Application.Iteration Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.Iteration = False Set rng = Range("a1") x = QueryPerformanceFrequency(freq) Debug.Print "-----" mint = 999999999 For i = 0 To n Call Sleep(30) 'new sched quantum for each iteration x = QueryPerformanceCounter(st) rng.Calculate x = QueryPerformanceCounter(et) If i 0 Then 'skip first time, affected by VBA overhead et = et - st 'elapased time ct = ct + et 'cumulative elapsed time for average If et maxt Then maxt = et If et mint Then mint = et End If Next i ' do ctrl-G to see results Debug.Print Format(ct / freq / n * 1000, "0.000000 msec"); Debug.Print ", min "; Format(mint / freq * 1000, "0.000000"); Debug.Print ", max "; Format(maxt / freq * 1000, "0.000000") Application.Iteration = iterSave Application.Calculation = calcSave Application.ScreenUpdating = True End Sub ----- original message ----- "hardeep.kanwar" wrote in message ... JoeU2004;380596 Wrote: "hardeep.kanwar" wrote: What is the Difference Between "--" in First Function and "*" in Second Function 1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61) 2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61)) There is no logical difference. "--" (double-negation) is merely a way to convert boolean results (TRUE, FALSE) into a number, which SUMPRODUCT requires. But any arithmetic operation will do the same. Moreover, SUMPRODUCT(x,y,z) is logically the same as SUMPRODUCT(x*y*z). However, there is an operational difference that may or may not matter, depending on the situation. If any of B54:B62 is text, SUMPRODUCT(...*B54:B61) will fail with a #VALUE error, whereas SUMPRODUCT(...,B54:B61) will not. The reason is that SUMPRODUCT is designed to tolerate text, whereas arithmetic expressions do not. ----- original message ----- "hardeep.kanwar" wrote in message ... Hi! Experts What is the Difference Between these Sumproduct Function It show the Same Results What is the Difference Between "--" in First Function and "*" in Second Function 1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61) 2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61)) Thanks in Advance Hardeep Kanwar -- hardeep.kanwar ------------------------------------------------------------------------ hardeep.kanwar's Profile: 'The Code Cage Forums - View Profile: hardeep.kanwar' (http://www.thecodecage.com/forumz/member.php?userid=170) View this thread: 'SumProduct Query - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=106459) Thanks sir Its Really Help Full for me Which is the best one -- hardeep.kanwar ------------------------------------------------------------------------ hardeep.kanwar's Profile: http://www.thecodecage.com/forumz/member.php?userid=170 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106459 |
#7
|
|||
|
|||
SumProduct Query
"T. Valko" wrote:
There are some calculation timer procedures written by Charles Williams posted in this article: http://msdn2.microsoft.com/en-us/library/aa730921.aspx I am aware of them. And if you look carefully, you will see that we use similar techniques. ----- original message ----- "T. Valko" wrote in message ... There are some calculation timer procedures written by Charles Williams posted in this article: http://msdn2.microsoft.com/en-us/library/aa730921.aspx -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... "hardeep.kanwar" wrote: 1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61) 2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61)) [....] Which is the best one I do not measure any significant performance difference using a range of 60000 cells. As I noted previously, ",B54:B61" is better than "*B54:B61" if you expect that some cells have text (e.g. ""). The macro below demonstrates the measurement procedure. Note that the SUMPRODUCT formula is assumed to be in A1. Option Explicit Private Declare Sub Sleep Lib "kernel32" (ByVal msec As Long) Private Declare Function QueryPerformanceFrequency Lib "kernel32" (ByRef clkFreq As Currency) As Boolean Private Declare Function QueryPerformanceCounter Lib "kernel32" (ByRef clkCount As Currency) As Boolean Sub doit() Const n As Integer = 30 Dim freq As Currency, st As Currency, et As Currency, ct As Currency Dim mint As Currency, maxt As Currency Dim i As Integer, x As Long, calcSave, iterSave Dim rng As Range calcSave = Application.Calculation iterSave = Application.Iteration Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.Iteration = False Set rng = Range("a1") x = QueryPerformanceFrequency(freq) Debug.Print "-----" mint = 999999999 For i = 0 To n Call Sleep(30) 'new sched quantum for each iteration x = QueryPerformanceCounter(st) rng.Calculate x = QueryPerformanceCounter(et) If i 0 Then 'skip first time, affected by VBA overhead et = et - st 'elapased time ct = ct + et 'cumulative elapsed time for average If et maxt Then maxt = et If et mint Then mint = et End If Next i ' do ctrl-G to see results Debug.Print Format(ct / freq / n * 1000, "0.000000 msec"); Debug.Print ", min "; Format(mint / freq * 1000, "0.000000"); Debug.Print ", max "; Format(maxt / freq * 1000, "0.000000") Application.Iteration = iterSave Application.Calculation = calcSave Application.ScreenUpdating = True End Sub ----- original message ----- "hardeep.kanwar" wrote in message ... JoeU2004;380596 Wrote: "hardeep.kanwar" wrote: What is the Difference Between "--" in First Function and "*" in Second Function 1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61) 2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61)) There is no logical difference. "--" (double-negation) is merely a way to convert boolean results (TRUE, FALSE) into a number, which SUMPRODUCT requires. But any arithmetic operation will do the same. Moreover, SUMPRODUCT(x,y,z) is logically the same as SUMPRODUCT(x*y*z). However, there is an operational difference that may or may not matter, depending on the situation. If any of B54:B62 is text, SUMPRODUCT(...*B54:B61) will fail with a #VALUE error, whereas SUMPRODUCT(...,B54:B61) will not. The reason is that SUMPRODUCT is designed to tolerate text, whereas arithmetic expressions do not. ----- original message ----- "hardeep.kanwar" wrote in message ... Hi! Experts What is the Difference Between these Sumproduct Function It show the Same Results What is the Difference Between "--" in First Function and "*" in Second Function 1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61) 2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61)) Thanks in Advance Hardeep Kanwar -- hardeep.kanwar ------------------------------------------------------------------------ hardeep.kanwar's Profile: 'The Code Cage Forums - View Profile: hardeep.kanwar' (http://www.thecodecage.com/forumz/member.php?userid=170) View this thread: 'SumProduct Query - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=106459) Thanks sir Its Really Help Full for me Which is the best one -- hardeep.kanwar ------------------------------------------------------------------------ hardeep.kanwar's Profile: http://www.thecodecage.com/forumz/member.php?userid=170 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106459 |
Thread Tools | |
Display Modes | |
|
|