2 Columns total division

[Yaseen Al-Lawati]

hello
I have 2 columns N&O N:the Yield and O: How much I have in that security.
everytime I add a new security I add a new row and I fill it.
However, I have a cell at the end of column O which I have to add manually
the following part everytime I add a security
=((N4*O4)+(N5*O5)+(N6*O6)+(N7*O7)+(N8*O8)+(N9*O9)+(N10*O10)+(N11*O11)+(N12*O12)+(N13*O13)+(N14*O14)+(N15*O15)+(N16*O16)+(N17*O17)+(N18*O18)+(N19*O19)+(N20*O20)+(N21*O21)+(N22*O22)+(N23*O23)+(N24*O24)+(N25*O25)+(N26*O26)+(N27*O27)+(N28*O28)+(N29*O29)+(N30*O30)+(N31*O31)+(N32*O32)+(N33*O33)+(N34*O34)+(N35*O35)+(N36*O36)+(N37*O37)+(N38*O38)+(N39*O39)+(N40*O40)+(N41*O41)+(N42*O42)+(N43*O43)+(N44*O44)+(N45*O45)+(N46*O46)+(N47*O47)+(N48*O48)+(N49*O49)+(N50*O50)+(N51*O51)+(N52*O52)+(N53*O53)+(N54*O54)+(N55*O55)+(N55*O55))/O57

Multiply every security nominal amt into the Yield and then divide them over
the total.
IS THERE ANY FORMULA THAT I PUT TO DO the CALCULATION AUTOMATICALLY??

 

[Mike H]

Maybe

=SUMPRODUCT((N4:N55)*(O4:O55))/O57



Mike

 

[Mike H]

Ah,

I think I inderstand what O57 is now so this may be better
Put this in a cell somewhere, in this case M1
=sum(O4:O10000)

Then use this formula
=SUMPRODUCT((N4:N10000)*(O4:O10000))/M1


Mike

 

[macropod]

Or, to eliminate the need for an intermediate cell:
=SUMPRODUCT((N4:N100)*(O4:O100))/SUM(O4:O100)
Note that it's also best not to specify a range that's larger than you'll need - it can slow recalculations considerably and could
lead to other errors if you later decide to use the nominated range for something else.

Cheers

 

[Mike H]

good point

/SUM(O4:O100)

Mike

Or, to eliminate the need for an intermediate cell:
=SUMPRODUCT((N4:N100)*(O4:O100))/SUM(O4:O100)
Note that it's also best not to specify a range that's larger than you'll need - it can slow recalculations considerably and could
lead to other errors if you later decide to use the nominated range for something else.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Ah,

I think I inderstand what O57 is now so this may be better
Put this in a cell somewhere, in this case M1
=sum(O4:O10000)

Then use this formula
=SUMPRODUCT((N4:N10000)*(O4:O10000))/M1


Mike