Sumproduct formula
in [Functions]
|
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 <BeSmart(a)discussions.microsoft.com> 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 Range Next: bahttext |