Hi,
My formulas in columns O,Q and 3 currently compound the monthly returns in row 5 to get the 3 month 12 month and year to date performance. How do I make the formulas move cell refferences when I insert a new column. For example
when I insert a column to the right of column N the formula will still point to Novembers, Decembers and Jans returns rather than December, January and Februarys returns when working out the 3 month return.
James
Maybe is just me but I got error when opening your file.
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
returns.xls
I did not have any problems. I attach again
3 months
=(PRODUCT((OFFSET($A$4,1,MATCH($P$4,$A$4:$Z$4,0)-5,1,3)%)+1)-1)*100
12 months
=(PRODUCT((OFFSET($A$4,1,MATCH($R$4,$A$4:$Z$4,0)-17,1,12)%)+1)-1)*100
For YTD you will need to chage the Text date to normal Dates and then we can work out a formula.
Thanks for your reply
I've tried the formulas and cant get them to work. Please see attached. I've also changed the dates from text to normal dates for YTD.
returns.xlsx
Any Help much appreciated
James
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks