Hi everyone,
I'm having an excel file of 400 companies with monthly returns, but each company has unique starting and ending time horizon. For example, company A data starts in Oct. 2000 and end in Sept. 2005, but company B data starts in Jun. 2001 and end in Nov. 2007. How can I tell Excel to calculate the average monthly return of firms each year given this situation?
Thanks very much in advance!
That would depend on how your data is set up. The =AVERAGE() does not include empty cells, so you could use the same formula for all companies. As long as there are now other types of data that interfere.
Sincerely
S?ren Larsen
"Give a man a fish, and you'll feed him for a day. Give a man a fishing rod, and he'll steal your yacht!"
Thanks for your reply but could you be more specific please? The problem is that each company has different time horizon so one formula can't be applied to all 400 companies.
Assume you have a row for each company and every column is a month. Then you could have the =AVERAGE() formula cover the company with longest time horizon and apply this to all the other 399 companies. But perhaps your data is set up very differently; can you upload a sample workbook where you change the company names and monthly returns to something arbitrary?
Here is the sample in my dataset. I have column A for company name, B for time horizon and C for return. I want to calculate the monthly return by year of all companies. Hope you can help me with this, thanks very much!
g33 year return
Arcolectric Holdings 10/31/2000 -0.0880
Arcolectric Holdings 11/30/2000 -0.0278
Arcolectric Holdings 12/29/2000 0.0000
Arcolectric Holdings 1/31/2001 -0.0429
Arcolectric Holdings 2/28/2001 0.0000
Arcolectric Holdings 3/30/2001 -0.0149
Arcolectric Holdings 4/30/2001 0.0000
Arcolectric Holdings 5/31/2001 -0.3008
Arcolectric Holdings 6/29/2001 0.0222
Arcolectric Holdings 7/31/2001 0.0870
Arcolectric Holdings 8/31/2001 0.0000
Arcolectric Holdings 9/28/2001 0.0000
Arcolectric Holdings 10/31/2001 0.0000
Arcolectric Holdings 11/30/2001 -0.4808
Arcolectric Holdings 12/31/2001 0.0000
Arcolectric Holdings 1/31/2002 0.0000
Arcolectric Holdings 2/28/2002 0.4000
Arcolectric Holdings 3/28/2002 0.4286
Arcolectric Holdings 4/30/2002 -0.2000
Arcolectric Holdings 5/31/2002 0.0000
Arcolectric Holdings 6/28/2002 0.0990
Arcolectric Holdings 7/31/2002 -0.4186
Arcolectric Holdings 8/30/2002 0.0000
Beauford plc 3/30/2001 0.0000
Beauford plc 4/30/2001 0.0000
Beauford plc 5/31/2001 0.0000
Beauford plc 6/29/2001 0.0000
Beauford plc 7/31/2001 0.0000
Beauford plc 8/31/2001 0.0000
Beauford plc 9/28/2001 0.0000
Beauford plc 10/31/2001 0.3043
Beauford plc 11/30/2001 0.0000
Beauford plc 12/31/2001 0.0000
Beauford plc 1/31/2002 0.0000
Beauford plc 2/28/2002 0.0000
Beauford plc 3/28/2002 0.0000
Beauford plc 4/30/2002 0.0000
Beauford plc 5/31/2002 0.0000
Beauford plc 6/28/2002 0.0000
Beauford plc 7/31/2002 0.0000
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks