From: BeSmart on 20 Feb 2010 21:39 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\$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 From: Lars-�ke Aspelin on 21 Feb 2010 00:59 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 From: BeSmart on 21 Feb 2010 03:29 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 First  |  Prev  |  Pages: 1 2 Prev: Entering a Value & Updating the Next Empty Cell in a RangeNext: bahttext