|
drag and drop formulas
I have a problem. This is what my sheet looks like:
sheet1 In column A is the name of the company. in column B the name of the company and In column c is the monthly return of the company. I'm interested in the compounded half year return. In a new worksheet (sheet2)I want the company name in column A, the compounded half year return (first six months) in column b and the compounded half year return (last six months) in column c. The formula for the compounded half year return(first six months in cell b2 is: =((1+'sheet1'!a2)*(1+'sheet1'!a3)*(1+'sheet1'!a4)* (1+'sheet1'!a5)*(1+'sheet1'!a6)*(1+'sheet1'!a7))-1 The formula for the compounded half year return(last six months in cell c2 is: =((1+Sheet1!A8)*(1+Sheet1!A9)*(1+Sheet1!A10)*(1+Sh eet1!A11)*(1+Sheet1!A12)*(1+Sheet1!A13))-1 I want to drag down this formula so that the formula for cell b3 is =((1+Sheet1!A14)*(1+Sheet1!A15)*(1+Sheet1!A16)*(1+ Sheet1!A17)*(1+Sheet1!A18)*(1+Sheet1!A19))-1 and so on. I hope my question is clear enough so that somebody can help me out. Thanx and regards Bram |
drag and drop formulas
Use a helper column C, otherwise the formula will be very very long:
In C2: =2+(CELL("row";C2)-2)*12 In B2: A2 A3 =(1+INDIRECT("Sheet1!A"&C2)*(1+INDIRECT("Sheet1!A" &C2+1))) ... and so on A7 until INDIRECT("Sheet1!A"&C2+5) and fill down! Regards, Stefi €˛Bram€¯ ezt Ć*rta: I have a problem. This is what my sheet looks like: sheet1 In column A is the name of the company. in column B the name of the company and In column c is the monthly return of the company. I'm interested in the compounded half year return. In a new worksheet (sheet2)I want the company name in column A, the compounded half year return (first six months) in column b and the compounded half year return (last six months) in column c. The formula for the compounded half year return(first six months in cell b2 is: =((1+'sheet1'!a2)*(1+'sheet1'!a3)*(1+'sheet1'!a4)* (1+'sheet1'!a5)*(1+'sheet1'!a6)*(1+'sheet1'!a7))-1 The formula for the compounded half year return(last six months in cell c2 is: =((1+Sheet1!A8)*(1+Sheet1!A9)*(1+Sheet1!A10)*(1+Sh eet1!A11)*(1+Sheet1!A12)*(1+Sheet1!A13))-1 I want to drag down this formula so that the formula for cell b3 is =((1+Sheet1!A14)*(1+Sheet1!A15)*(1+Sheet1!A16)*(1+ Sheet1!A17)*(1+Sheet1!A18)*(1+Sheet1!A19))-1 and so on. I hope my question is clear enough so that somebody can help me out. Thanx and regards Bram |
drag and drop formulas
Thank you Stefi!!!
"Stefi" wrote: Use a helper column C, otherwise the formula will be very very long: In C2: =2+(CELL("row";C2)-2)*12 In B2: A2 A3 =(1+INDIRECT("Sheet1!A"&C2)*(1+INDIRECT("Sheet1!A" &C2+1))) ... and so on A7 until INDIRECT("Sheet1!A"&C2+5) and fill down! Regards, Stefi €˛Bram€¯ ezt Ć*rta: I have a problem. This is what my sheet looks like: sheet1 In column A is the name of the company. in column B the name of the company and In column c is the monthly return of the company. I'm interested in the compounded half year return. In a new worksheet (sheet2)I want the company name in column A, the compounded half year return (first six months) in column b and the compounded half year return (last six months) in column c. The formula for the compounded half year return(first six months in cell b2 is: =((1+'sheet1'!a2)*(1+'sheet1'!a3)*(1+'sheet1'!a4)* (1+'sheet1'!a5)*(1+'sheet1'!a6)*(1+'sheet1'!a7))-1 The formula for the compounded half year return(last six months in cell c2 is: =((1+Sheet1!A8)*(1+Sheet1!A9)*(1+Sheet1!A10)*(1+Sh eet1!A11)*(1+Sheet1!A12)*(1+Sheet1!A13))-1 I want to drag down this formula so that the formula for cell b3 is =((1+Sheet1!A14)*(1+Sheet1!A15)*(1+Sheet1!A16)*(1+ Sheet1!A17)*(1+Sheet1!A18)*(1+Sheet1!A19))-1 and so on. I hope my question is clear enough so that somebody can help me out. Thanx and regards Bram |
drag and drop formulas
You're welcome! Thanks for the feedback!
Stefi €˛Bram€¯ ezt Ć*rta: Thank you Stefi!!! "Stefi" wrote: Use a helper column C, otherwise the formula will be very very long: In C2: =2+(CELL("row";C2)-2)*12 In B2: A2 A3 =(1+INDIRECT("Sheet1!A"&C2)*(1+INDIRECT("Sheet1!A" &C2+1))) ... and so on A7 until INDIRECT("Sheet1!A"&C2+5) and fill down! Regards, Stefi €˛Bram€¯ ezt Ć*rta: I have a problem. This is what my sheet looks like: sheet1 In column A is the name of the company. in column B the name of the company and In column c is the monthly return of the company. I'm interested in the compounded half year return. In a new worksheet (sheet2)I want the company name in column A, the compounded half year return (first six months) in column b and the compounded half year return (last six months) in column c. The formula for the compounded half year return(first six months in cell b2 is: =((1+'sheet1'!a2)*(1+'sheet1'!a3)*(1+'sheet1'!a4)* (1+'sheet1'!a5)*(1+'sheet1'!a6)*(1+'sheet1'!a7))-1 The formula for the compounded half year return(last six months in cell c2 is: =((1+Sheet1!A8)*(1+Sheet1!A9)*(1+Sheet1!A10)*(1+Sh eet1!A11)*(1+Sheet1!A12)*(1+Sheet1!A13))-1 I want to drag down this formula so that the formula for cell b3 is =((1+Sheet1!A14)*(1+Sheet1!A15)*(1+Sheet1!A16)*(1+ Sheet1!A17)*(1+Sheet1!A18)*(1+Sheet1!A19))-1 and so on. I hope my question is clear enough so that somebody can help me out. Thanx and regards Bram |
| All times are GMT +1. The time now is 08:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com