Sumproduct formula
in [Functions]
|
From: BeSmart on 20 Feb 2010 04:15 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
From: BeSmart on 20 Feb 2010 05:58 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
From: "David Biddulph" groups [at] on 20 Feb 2010 08:38 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$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 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
From: Teethless mama on 20 Feb 2010 11:04 your formula can shorten to this: =SUMPRODUCT((market=$D217)*(duration=$BO$198:$BW$198)*{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
From: Teethless mama on 20 Feb 2010 11:06 correction: =SUMPRODUCT((market=$D217)*(duration=$BO$198:$BW$198)*{30,5,10,15,20,45,60,90,120}*I$35:I$76)*10*$G217 "Teethless mama" wrote: > your formula can shorten to this: > > =SUMPRODUCT((market=$D217)*(duration=$BO$198:$BW$198)*{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
|
Next
|
Last
Pages: 1 2 Prev: Entering a Value & Updating the Next Empty Cell in a Range Next: bahttext |