Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Question
This maybe a strange question, but I have a SUMPRODUCT function that
works in Excel, however, I don't understand why it does. Here is what I have: An array of task times (Duration) B3:B41 A matrix of names of people assigned to the various tasks throughout the week (WEEK)D3:H41 (The names appear one or more times) The array of individual names appears in A43:A61 Now I want to know the total time each individual spends carrying out one or more of the tasks. This is done with: SUMPRODUCT((WEEK=$A43) * Duration) This function appears beside each name with the row number incremented accordingly. IT WORKS! Here is my problem, I can't find anywhere in the documentation for this function (or another example) where a row number in a matrix will look up the corresponding row number in an array. So I don't understand why it provides the desired result. I would appreciate more examples of this use of SUMPRODUCT and technical explanation of why it works. TIA -- _______________________________ Regards, Vic Chapman |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Question
Hi!
(WEEK=$A43) will return an array of boolean TRUE or FALSE. Something like this: D3 = A43 = TRUE D4 = A43 = FALSE D5 = A43 = TRUE D6 = A43 = FALSE Then those boolean values are multiplied by the corresponding duration values from the other array, B3:B41. That would look like this: TRUE * B3 = B3 FALSE * B4 = 0 TRUE * B5 = B5 FALSE * B6 = 0 Then the values are summed together and you get your result. There's a very detailed explanation of Sumproduct he http://www.xldynamic.com/source/xld.SUMPRODUCT.html Biff "Victor Chapman" wrote in message . .. This maybe a strange question, but I have a SUMPRODUCT function that works in Excel, however, I don't understand why it does. Here is what I have: An array of task times (Duration) B3:B41 A matrix of names of people assigned to the various tasks throughout the week (WEEK)D3:H41 (The names appear one or more times) The array of individual names appears in A43:A61 Now I want to know the total time each individual spends carrying out one or more of the tasks. This is done with: SUMPRODUCT((WEEK=$A43) * Duration) This function appears beside each name with the row number incremented accordingly. IT WORKS! Here is my problem, I can't find anywhere in the documentation for this function (or another example) where a row number in a matrix will look up the corresponding row number in an array. So I don't understand why it provides the desired result. I would appreciate more examples of this use of SUMPRODUCT and technical explanation of why it works. TIA -- _______________________________ Regards, Vic Chapman |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Question
Hi Victor
Probably the best reference for information on the sumproduct function that i know about is at http://www.xldynamic.com/source/xld.SUMPRODUCT.html But basically the sumproduct function works by evaluation true statements to 1 and false statements to 0 ... For a quick overview of your sumproduct funtion look at it this way: =SUMPRODUCT((WEEK=$A43) * Duration) =SUMPRODUCT((D3:H41 =$A43)*B3:B41) (for this example i'm make the ranges smaller .... e.g.) =SUMPRODUCT((D1:F3 =$G1)*B1:B3) (and use the following data) .......A........B.........C.........D........E.... .....F.............G 1.............10...................Bill......Fred. ...Steve.......Anne 2.............15...................Anne...Bill.... ...Fred........Bill 3.............20...................Fred....Anne... .Steve......Steve In G2 the SUMPRODUCT formula would work like this =SUMPRODUCT((D1:F3 =$G1)*B1:B3) =SUMPRODUCT((Bill, Fred, Steve, Anne, Bill, Fred, Fred, Anne, Steve=Anne)*(10,10,10,15,15,15,20,20,20)) =SUMPRODUCT((False, False, False, True, False, False, False, True, False)*(10,10,10,15,15,15,20,20,20)) =SUMPROUDCT((0,0,0,1,0,0,0,1,0)*(10,10,10,15,15,15 ,20,20,20)) =SUMPRODUCT(0*10+0*10+0*10+1*15+0*15+0*15+0*20+1*2 0+0*20) =SUMPRODUCT(0+0+0+15+0+0+0+20+0) =35 Hope this helps. -- Cheers JulieD Excel MVP julied_ng at hctsReMoVeThIs dot net dot au "Victor Chapman" wrote: This maybe a strange question, but I have a SUMPRODUCT function that works in Excel, however, I don't understand why it does. Here is what I have: An array of task times (Duration) B3:B41 A matrix of names of people assigned to the various tasks throughout the week (WEEK)D3:H41 (The names appear one or more times) The array of individual names appears in A43:A61 Now I want to know the total time each individual spends carrying out one or more of the tasks. This is done with: SUMPRODUCT((WEEK=$A43) * Duration) This function appears beside each name with the row number incremented accordingly. IT WORKS! Here is my problem, I can't find anywhere in the documentation for this function (or another example) where a row number in a matrix will look up the corresponding row number in an array. So I don't understand why it provides the desired result. I would appreciate more examples of this use of SUMPRODUCT and technical explanation of why it works. TIA -- _______________________________ Regards, Vic Chapman |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Question
JulieD wrote:
Hi Victor Probably the best reference for information on the sumproduct function that i know about is at http://www.xldynamic.com/source/xld.SUMPRODUCT.html But basically the sumproduct function works by evaluation true statements to 1 and false statements to 0 ... For a quick overview of your sumproduct funtion look at it this way: =SUMPRODUCT((WEEK=$A43) * Duration) =SUMPRODUCT((D3:H41 =$A43)*B3:B41) (for this example i'm make the ranges smaller .... e.g.) =SUMPRODUCT((D1:F3 =$G1)*B1:B3) (and use the following data) ......A........B.........C.........D........E..... ....F.............G 1.............10...................Bill......Fred. ...Steve.......Anne 2.............15...................Anne...Bill.... ...Fred........Bill 3.............20...................Fred....Anne... .Steve......Steve In G2 the SUMPRODUCT formula would work like this =SUMPRODUCT((D1:F3 =$G1)*B1:B3) =SUMPRODUCT((Bill, Fred, Steve, Anne, Bill, Fred, Fred, Anne, Steve=Anne)*(10,10,10,15,15,15,20,20,20)) =SUMPRODUCT((False, False, False, True, False, False, False, True, False)*(10,10,10,15,15,15,20,20,20)) =SUMPROUDCT((0,0,0,1,0,0,0,1,0)*(10,10,10,15,15,15 ,20,20,20)) =SUMPRODUCT(0*10+0*10+0*10+1*15+0*15+0*15+0*20+1*2 0+0*20) =SUMPRODUCT(0+0+0+15+0+0+0+20+0) =35 Hope this helps. Thank you for your quick response. I agree, and understand that this is what the function is doing. What I don't understand is why this works when it it would appear to be contrary to the information provided in the Excel Help system. The following is copied from the Help system: The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value. In the example I have provided, Duration is a single column array. WEEK on the other hand is a multiple column matrix. They have different dimensions! To work from your example: =SUMPRODUCT((D1:F3 =$G1)*B1:B3) is NOT the same as =SUMPRODUCT((Bill, Fred, Steve, Anne, Bill, Fred, Fred, Anne, Steve=Anne)*(10,10,10,15,15,15,20,20,20)) It makes the assumption that for every instance of a row value in WEEK, the corresponding row value in Duration will be generated. I can't find anywhere in the documentation that I can make that assumption. I have also checked http://www.xldynamic.com/source/xld.SUMPRODUCT.html and cannot find an example similar to the one I have provide. -- _______________________________ Regards, Vic Chapman |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Question
Victor Chapman wrote: JulieD wrote: Hi Victor Probably the best reference for information on the sumproduct function that i know about is at http://www.xldynamic.com/source/xld.SUMPRODUCT.html But basically the sumproduct function works by evaluation true statements to 1 and false statements to 0 ... For a quick overview of your sumproduct funtion look at it this way: =SUMPRODUCT((WEEK=$A43) * Duration) =SUMPRODUCT((D3:H41 =$A43)*B3:B41) (for this example i'm make the ranges smaller .... e.g.) =SUMPRODUCT((D1:F3 =$G1)*B1:B3) (and use the following data) ......A........B.........C.........D........E..... ....F.............G 1.............10...................Bill......Fred. ...Steve.......Anne 2.............15...................Anne...Bill.... ...Fred........Bill 3.............20...................Fred....Anne... .Steve......Steve In G2 the SUMPRODUCT formula would work like this =SUMPRODUCT((D1:F3 =$G1)*B1:B3) =SUMPRODUCT((Bill, Fred, Steve, Anne, Bill, Fred, Fred, Anne, Steve=Anne)*(10,10,10,15,15,15,20,20,20)) =SUMPRODUCT((False, False, False, True, False, False, False, True, False)*(10,10,10,15,15,15,20,20,20)) =SUMPROUDCT((0,0,0,1,0,0,0,1,0)*(10,10,10,15,15,15 ,20,20,20)) =SUMPRODUCT(0*10+0*10+0*10+1*15+0*15+0*15+0*20+1*2 0+0*20) =SUMPRODUCT(0+0+0+15+0+0+0+20+0) =35 Hope this helps. Thank you for your quick response. I agree, and understand that this is what the function is doing. What I don't understand is why this works when it it would appear to be contrary to the information provided in the Excel Help system. The following is copied from the Help system: The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value. In the example I have provided, Duration is a single column array. WEEK on the other hand is a multiple column matrix. They have different dimensions! To work from your example: =SUMPRODUCT((D1:F3 =$G1)*B1:B3) is NOT the same as =SUMPRODUCT((Bill, Fred, Steve, Anne, Bill, Fred, Fred, Anne, Steve=Anne)*(10,10,10,15,15,15,20,20,20)) It makes the assumption that for every instance of a row value in WEEK, the corresponding row value in Duration will be generated. I can't find anywhere in the documentation that I can make that assumption. I have also checked http://www.xldynamic.com/source/xld.SUMPRODUCT.html and cannot find an example similar to the one I have provide. You are multiplying, as it were, a vector with a matrix which are equally sized in one relevant dimension. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
another sumproduct question | Excel Worksheet Functions | |||
another sumproduct question | Excel Worksheet Functions | |||
SUMPRODUCT Question... | Excel Discussion (Misc queries) | |||
sumproduct question | Excel Worksheet Functions | |||
Question about sumproduct | Excel Discussion (Misc queries) |