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 