I have stock data starting in A1 and continuing down to A7. I am calculating return A1/A7-1. However, each month, a new piece of data will appear. So once August starts, I will then have a piece of data in A8. When Sept starts, I will have a new piece of data in A9. Is there a way to update my formula each month automatically so that it will then calculate A1/A8-1 in August and A1/A9-1 in Sept??
Thank You
You can create a dynamic named range
See here http://www.contextures.com/xlNames01.html
Alternately
=A1/INDEX($A:$A, MATCH(9E99, $A:$A))
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Thanks for the response. What does 9e99 mean in your formula?
9 times 10 to the 99th power. When excel doesn't find that number, it returns the last number in column A
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks