Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bram
 
Posts: n/a
Default 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

  #2   Report Post  
Stefi
 
Posts: n/a
Default 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

  #3   Report Post  
Bram
 
Posts: n/a
Default 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

  #4   Report Post  
Stefi
 
Posts: n/a
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Disabling Drag And Drop Referencing camerons New Users to Excel 1 August 22nd 05 03:29 PM
Prevent Drag & Drop DaveyC4S Excel Discussion (Misc queries) 2 August 22nd 05 02:31 PM
Help please simple drop and drag I can't make work Anthony Excel Worksheet Functions 2 July 10th 05 06:28 AM
cell drop and drag D Snider Excel Worksheet Functions 1 May 1st 05 04:27 AM
drag and drop to get a lot of wordarts? Anderson Lee Excel Discussion (Misc queries) 0 December 27th 04 06:15 PM


All times are GMT +1. The time now is 03:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"