Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to offset certain cells after dragging down a formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to offset certain cells after dragging down a formula
Just apply the same principle that you have already been provided:
in A2 (for company name) =Offset(Sheet1!$A$1,(ROW(C1)-1)*12+1,0,1,1) in B2 =stdev(offset(sheet1!$A$1,(ROW(A1)-1)*12+2,0,6,1)) In C2 =stdev(offset(sheet1!$A$1,(ROW(A1)-1)*12+8,0,6,1)) then select A2:C2 and drag fill down the column -- Regards, Tom Ogilvy "Bram" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to maintain a set range of cells when dragging a formula (e.gmonthlyannual data) | Excel Discussion (Misc queries) | |||
How to read offset cells from dynamic sort array formula? | Excel Discussion (Misc queries) | |||
copying/dragging a formula to adjacent cells in Excel | Excel Discussion (Misc queries) | |||
How do I keep a formula constant in dragging cells below the rows | Excel Discussion (Misc queries) | |||
Problem with Range.Cells.Offset and Range.Cells( row + offset, column) | Excel Programming |