| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
2 Columns total division
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?? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
2 Columns total division
Maybe
=SUMPRODUCT((N4:N55)*(O4:O55))/O57 Mike "Yaseen Al-Lawati" wrote: 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?? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
2 Columns total division
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 "Yaseen Al-Lawati" wrote: 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?? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
2 Columns total division
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] ------------------------- "Mike H" wrote in message ... 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 "Yaseen Al-Lawati" wrote: 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?? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
2 Columns total division
good point
/SUM(O4:O100) Mike "macropod" wrote: 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] ------------------------- "Mike H" wrote in message ... 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 "Yaseen Al-Lawati" wrote: 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?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Total column changes colors when total equals sum of other columns | New Users to Excel | |||
| Need to average division of two columns based on criteria | Excel Discussion (Misc queries) | |||
| Average the total of 3 columns | Excel Discussion (Misc queries) | |||
| what formula to get total from two different columns | Excel Worksheet Functions | |||
| how do i add up 3 columns to get a total | New Users to Excel | |||
Linear Mode
