| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct formula
Hi All
Is there a smarter way of doing this SUMPRODUCT formula? I'm finding different duration totals and multiplying the total by a different ratio for each duration e.g. find the 30 durations and multiple by the 30 ratio of 0.5 (cell name = 'thirty'), find the 5 durations and multiple by the 5 ratio of 0.05 (cell name = 'five') etc All named ranges are the same size i.e. cells 35:76 $D217 = the market to search for in the named range "market" = range (A35:A76) BO198 = the duration to search for in the named range "duration" = range(B35:B76) All parts are the same except for: - the "duration =$BO$198" section which needs to move one column right each time - the named ranges must change (in the order as per the current formula) "thirty" or "five" or "ten" etc I also need to be able to copy the formula across 52 columns and down 10 rows. =SUM(((SUMPRODUCT((market=$D217)*(duration=$BO$198 ),I$35:I$76)*thirty +SUMPRODUCT((market=$D217)*(duration=$BP$198),I$35 :I$76)*five +SUMPRODUCT((market=$D217)*(duration=$BQ$198),I$35 :I$76)*ten +SUMPRODUCT((market=$D217)*(duration=$BR$198),I$35 :I$76)*fifteen +SUMPRODUCT((market=$D217)*(duration=$BS$198),I$35 :I$76)*twenty +SUMPRODUCT((market=$D217)*(duration=$BT$198),I$35 :I$76)*fortyfive +SUMPRODUCT((market=$D217)*(duration=$BU$198),I$35 :I$76)*Sixty +SUMPRODUCT((market=$D217)*(duration=$BV$198),I$35 :I$76)*ninety +SUMPRODUCT((market=$D217)*(duration=$BW$198),I$35 :I$76)*onetwenty)*10))*$G217 Any advice would be greatly appreciated. -- Thank for your help BeSmart |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct formula
In addition, the sumproduct formula causes a circular reference because
within the range of rows 35:76 , row 52 is different - it total the first group of markets. (It's formula = the sum of the first 5 sumproduct formula and therefore causes a circular reference). How can I exclude row 52 from the sumproduct formula to stop the circular reference? -- Thank for your help BeSmart "BeSmart" wrote: Hi All Is there a smarter way of doing this SUMPRODUCT formula? I'm finding different duration totals and multiplying the total by a different ratio for each duration e.g. find the 30 durations and multiple by the 30 ratio of 0.5 (cell name = 'thirty'), find the 5 durations and multiple by the 5 ratio of 0.05 (cell name = 'five') etc All named ranges are the same size i.e. cells 35:76 $D217 = the market to search for in the named range "market" = range (A35:A76) BO198 = the duration to search for in the named range "duration" = range(B35:B76) All parts are the same except for: - the "duration =$BO$198" section which needs to move one column right each time - the named ranges must change (in the order as per the current formula) "thirty" or "five" or "ten" etc I also need to be able to copy the formula across 52 columns and down 10 rows. =SUM(((SUMPRODUCT((market=$D217)*(duration=$BO$198 ),I$35:I$76)*thirty +SUMPRODUCT((market=$D217)*(duration=$BP$198),I$35 :I$76)*five +SUMPRODUCT((market=$D217)*(duration=$BQ$198),I$35 :I$76)*ten +SUMPRODUCT((market=$D217)*(duration=$BR$198),I$35 :I$76)*fifteen +SUMPRODUCT((market=$D217)*(duration=$BS$198),I$35 :I$76)*twenty +SUMPRODUCT((market=$D217)*(duration=$BT$198),I$35 :I$76)*fortyfive +SUMPRODUCT((market=$D217)*(duration=$BU$198),I$35 :I$76)*Sixty +SUMPRODUCT((market=$D217)*(duration=$BV$198),I$35 :I$76)*ninety +SUMPRODUCT((market=$D217)*(duration=$BW$198),I$35 :I$76)*onetwenty)*10))*$G217 Any advice would be greatly appreciated. -- Thank for your help BeSmart |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct formula
To start with, it looks as if your SUM function isn't doing anything.
=SUM(((SUMPRODUCT((market=$D217)*(duration=$BO$198 ),I$35:I$76)*thirty +SUMPRODUCT((market=$D217)*(duration=$BP$198),I$35 :I$76)*five +SUMPRODUCT((market=$D217)*(duration=$BQ$198),I$35 :I$76)*ten +SUMPRODUCT((market=$D217)*(duration=$BR$198),I$35 :I$76)*fifteen +SUMPRODUCT((market=$D217)*(duration=$BS$198),I$35 :I$76)*twenty +SUMPRODUCT((market=$D217)*(duration=$BT$198),I$35 :I$76)*fortyfive +SUMPRODUCT((market=$D217)*(duration=$BU$198),I$35 :I$76)*Sixty +SUMPRODUCT((market=$D217)*(duration=$BV$198),I$35 :I$76)*ninety +SUMPRODUCT((market=$D217)*(duration=$BW$198),I$35 :I$76)*onetwenty)*10))*$G217 ought to be able to be changed to =(SUMPRODUCT((market=$D217)*(duration=$BO$198),I$3 5:I$76)*thirty +SUMPRODUCT((market=$D217)*(duration=$BP$198),I$35 :I$76)*five +SUMPRODUCT((market=$D217)*(duration=$BQ$198),I$35 :I$76)*ten +SUMPRODUCT((market=$D217)*(duration=$BR$198),I$35 :I$76)*fifteen +SUMPRODUCT((market=$D217)*(duration=$BS$198),I$35 :I$76)*twenty +SUMPRODUCT((market=$D217)*(duration=$BT$198),I$35 :I$76)*fortyfive +SUMPRODUCT((market=$D217)*(duration=$BU$198),I$35 :I$76)*Sixty +SUMPRODUCT((market=$D217)*(duration=$BV$198),I$35 :I$76)*ninety +SUMPRODUCT((market=$D217)*(duration=$BW$198),I$35 :I$76)*onetwenty)*10*$G217 To get rid of your circular reference the easy option to try would seem to be to cut out row 52 and paste it somewhere outside the range. -- David Biddulph BeSmart wrote: In addition, the sumproduct formula causes a circular reference because within the range of rows 35:76 , row 52 is different - it total the first group of markets. (It's formula = the sum of the first 5 sumproduct formula and therefore causes a circular reference). How can I exclude row 52 from the sumproduct formula to stop the circular reference? Hi All Is there a smarter way of doing this SUMPRODUCT formula? I'm finding different duration totals and multiplying the total by a different ratio for each duration e.g. find the 30 durations and multiple by the 30 ratio of 0.5 (cell name = 'thirty'), find the 5 durations and multiple by the 5 ratio of 0.05 (cell name = 'five') etc All named ranges are the same size i.e. cells 35:76 $D217 = the market to search for in the named range "market" = range (A35:A76) BO198 = the duration to search for in the named range "duration" = range(B35:B76) All parts are the same except for: - the "duration =$BO$198" section which needs to move one column right each time - the named ranges must change (in the order as per the current formula) "thirty" or "five" or "ten" etc I also need to be able to copy the formula across 52 columns and down 10 rows. =SUM(((SUMPRODUCT((market=$D217)*(duration=$BO$198 ),I$35:I$76)*thirty +SUMPRODUCT((market=$D217)*(duration=$BP$198),I$35 :I$76)*five +SUMPRODUCT((market=$D217)*(duration=$BQ$198),I$35 :I$76)*ten +SUMPRODUCT((market=$D217)*(duration=$BR$198),I$35 :I$76)*fifteen +SUMPRODUCT((market=$D217)*(duration=$BS$198),I$35 :I$76)*twenty +SUMPRODUCT((market=$D217)*(duration=$BT$198),I$35 :I$76)*fortyfive +SUMPRODUCT((market=$D217)*(duration=$BU$198),I$35 :I$76)*Sixty +SUMPRODUCT((market=$D217)*(duration=$BV$198),I$35 :I$76)*ninety +SUMPRODUCT((market=$D217)*(duration=$BW$198),I$35 :I$76)*onetwenty)*10))*$G217 Any advice would be greatly appreciated. -- Thank for your help BeSmart |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct formula
your formula can shorten to this:
=SUMPRODUCT((market=$D217)*(duration=$BO$198:$BW$1 98)*{30,5,10,15,20,45,60,90,120}*I$35:I$76)*$G217 "BeSmart" wrote: Hi All Is there a smarter way of doing this SUMPRODUCT formula? I'm finding different duration totals and multiplying the total by a different ratio for each duration e.g. find the 30 durations and multiple by the 30 ratio of 0.5 (cell name = 'thirty'), find the 5 durations and multiple by the 5 ratio of 0.05 (cell name = 'five') etc All named ranges are the same size i.e. cells 35:76 $D217 = the market to search for in the named range "market" = range (A35:A76) BO198 = the duration to search for in the named range "duration" = range(B35:B76) All parts are the same except for: - the "duration =$BO$198" section which needs to move one column right each time - the named ranges must change (in the order as per the current formula) "thirty" or "five" or "ten" etc I also need to be able to copy the formula across 52 columns and down 10 rows. =SUM(((SUMPRODUCT((market=$D217)*(duration=$BO$198 ),I$35:I$76)*thirty +SUMPRODUCT((market=$D217)*(duration=$BP$198),I$35 :I$76)*five +SUMPRODUCT((market=$D217)*(duration=$BQ$198),I$35 :I$76)*ten +SUMPRODUCT((market=$D217)*(duration=$BR$198),I$35 :I$76)*fifteen +SUMPRODUCT((market=$D217)*(duration=$BS$198),I$35 :I$76)*twenty +SUMPRODUCT((market=$D217)*(duration=$BT$198),I$35 :I$76)*fortyfive +SUMPRODUCT((market=$D217)*(duration=$BU$198),I$35 :I$76)*Sixty +SUMPRODUCT((market=$D217)*(duration=$BV$198),I$35 :I$76)*ninety +SUMPRODUCT((market=$D217)*(duration=$BW$198),I$35 :I$76)*onetwenty)*10))*$G217 Any advice would be greatly appreciated. -- Thank for your help BeSmart |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct formula
correction:
=SUMPRODUCT((market=$D217)*(duration=$BO$198:$BW$1 98)*{30,5,10,15,20,45,60,90,120}*I$35:I$76)*10*$G2 17 "Teethless mama" wrote: your formula can shorten to this: =SUMPRODUCT((market=$D217)*(duration=$BO$198:$BW$1 98)*{30,5,10,15,20,45,60,90,120}*I$35:I$76)*$G217 "BeSmart" wrote: Hi All Is there a smarter way of doing this SUMPRODUCT formula? I'm finding different duration totals and multiplying the total by a different ratio for each duration e.g. find the 30 durations and multiple by the 30 ratio of 0.5 (cell name = 'thirty'), find the 5 durations and multiple by the 5 ratio of 0.05 (cell name = 'five') etc All named ranges are the same size i.e. cells 35:76 $D217 = the market to search for in the named range "market" = range (A35:A76) BO198 = the duration to search for in the named range "duration" = range(B35:B76) All parts are the same except for: - the "duration =$BO$198" section which needs to move one column right each time - the named ranges must change (in the order as per the current formula) "thirty" or "five" or "ten" etc I also need to be able to copy the formula across 52 columns and down 10 rows. =SUM(((SUMPRODUCT((market=$D217)*(duration=$BO$198 ),I$35:I$76)*thirty +SUMPRODUCT((market=$D217)*(duration=$BP$198),I$35 :I$76)*five +SUMPRODUCT((market=$D217)*(duration=$BQ$198),I$35 :I$76)*ten +SUMPRODUCT((market=$D217)*(duration=$BR$198),I$35 :I$76)*fifteen +SUMPRODUCT((market=$D217)*(duration=$BS$198),I$35 :I$76)*twenty +SUMPRODUCT((market=$D217)*(duration=$BT$198),I$35 :I$76)*fortyfive +SUMPRODUCT((market=$D217)*(duration=$BU$198),I$35 :I$76)*Sixty +SUMPRODUCT((market=$D217)*(duration=$BV$198),I$35 :I$76)*ninety +SUMPRODUCT((market=$D217)*(duration=$BW$198),I$35 :I$76)*onetwenty)*10))*$G217 Any advice would be greatly appreciated. -- Thank for your help BeSmart |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct formula
Thanks Teethless Mama!!!
That worked wonderfully - and I can understand exactly what the formula is doing. I've definitely learnt a new and smarter way of doing the formula and I'll use it heaps!!! One question: The formula works if I enter numbers between the {...}, but it doesn't seem to like named ranges. Is there a way I can use named ranges (that report a cell on sheet2) in this area? i.e. instead of typing "{30,5,10...}" into the formula how do I enter the named range "{thirty,five,ten...}" which read cells on sheet2 in cell K3, K4, K5...? -- Thanks very much for your help BeSmart "Teethless mama" wrote: correction: =SUMPRODUCT((market=$D217)*(duration=$BO$198:$BW$1 98)*{30,5,10,15,20,45,60,90,120}*I$35:I$76)*10*$G2 17 "Teethless mama" wrote: your formula can shorten to this: =SUMPRODUCT((market=$D217)*(duration=$BO$198:$BW$1 98)*{30,5,10,15,20,45,60,90,120}*I$35:I$76)*$G217 "BeSmart" wrote: Hi All Is there a smarter way of doing this SUMPRODUCT formula? I'm finding different duration totals and multiplying the total by a different ratio for each duration e.g. find the 30 durations and multiple by the 30 ratio of 0.5 (cell name = 'thirty'), find the 5 durations and multiple by the 5 ratio of 0.05 (cell name = 'five') etc All named ranges are the same size i.e. cells 35:76 $D217 = the market to search for in the named range "market" = range (A35:A76) BO198 = the duration to search for in the named range "duration" = range(B35:B76) All parts are the same except for: - the "duration =$BO$198" section which needs to move one column right each time - the named ranges must change (in the order as per the current formula) "thirty" or "five" or "ten" etc I also need to be able to copy the formula across 52 columns and down 10 rows. =SUM(((SUMPRODUCT((market=$D217)*(duration=$BO$198 ),I$35:I$76)*thirty +SUMPRODUCT((market=$D217)*(duration=$BP$198),I$35 :I$76)*five +SUMPRODUCT((market=$D217)*(duration=$BQ$198),I$35 :I$76)*ten +SUMPRODUCT((market=$D217)*(duration=$BR$198),I$35 :I$76)*fifteen +SUMPRODUCT((market=$D217)*(duration=$BS$198),I$35 :I$76)*twenty +SUMPRODUCT((market=$D217)*(duration=$BT$198),I$35 :I$76)*fortyfive +SUMPRODUCT((market=$D217)*(duration=$BU$198),I$35 :I$76)*Sixty +SUMPRODUCT((market=$D217)*(duration=$BV$198),I$35 :I$76)*ninety +SUMPRODUCT((market=$D217)*(duration=$BW$198),I$35 :I$76)*onetwenty)*10))*$G217 Any advice would be greatly appreciated. -- Thank for your help BeSmart |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct formula
On Sat, 20 Feb 2010 18:39:13 -0800, BeSmart
wrote: Thanks Teethless Mama!!! That worked wonderfully - and I can understand exactly what the formula is doing. I've definitely learnt a new and smarter way of doing the formula and I'll use it heaps!!! One question: The formula works if I enter numbers between the {...}, but it doesn't seem to like named ranges. Is there a way I can use named ranges (that report a cell on sheet2) in this area? i.e. instead of typing "{30,5,10...}" into the formula how do I enter the named range "{thirty,five,ten...}" which read cells on sheet2 in cell K3, K4, K5...? If the parameters (30,5,10,...120) are all in an contigous range, i.e K3:K11 you can replace the vector {3,5,10...} in the formula with TRANSPOSE(K3:K11). You also have to confirm the formula with CTRL+SHIFT+ENTER rather than just ENTER. The range K3:K11 can be named if you want, like my_factors or something that describes what it contains. TRANSPOSE is needed to make a row vector out of the column vector K3:K11 to fit the other row vectors in the formula. Hope this helps / Lars-Åke |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct formula
Thanks Lars-Ã…ke
That works perfectly and I now know how to incorporate rows of data into formulas by using Transpose within the formula. I really appreciate your help!!! You're SMART!!! BeSmart |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help with sumproduct formula | Excel Worksheet Functions | |||
| SUMPRODUCT Formula | Excel Discussion (Misc queries) | |||
| Nesting a sumproduct formula within a sumif formula. | Excel Discussion (Misc queries) | |||
| sumproduct formula | Excel Discussion (Misc queries) | |||
| Help On SUMPRODUCT Formula | Excel Worksheet Functions | |||
Linear Mode
