Excel Wizards,
Posted this formula question and accompanying Excel file in a previous Thread, here...
http://www.excelforum.com/excel-form...85#post3171885
Still having trouble figuring this out, figured I'de post again in new Thread with updated file to hopefully get some help. Please open spreadsheet if you have time to offer some always priceless help
I am trying to pull across multiple tabs financial figures for various companies for equity/stock analysis. In the attached Excel file on the "Data Input" tab, I have a group of companies and their Year over Year (YoY) Earnings Growth (EG) rates from 2009 - 2012. These numbers are randomly made up and may or may not be correct, mainly here just as an example. I also have below the group of companies, aka comps set, some statistical figures, e.g. "Max"..."Median"..."Count".
The next 2 tabs are what I need help with.
On the "Company Output" tab, I have listed a company's individual YoY EG % for each year, its 2012 YoY EG rank in the set vs the stat's for the year, and finally its rank in the set for each year. The 2 examples are WFM and AMZN. The figures for both AMZN and WFM are typed in from the 'Data Input" tab. I would love to figure out a formula that can pull that data instead of having to type it in...makes it very tedious with multiple companies. Basically, I need 3 formulas here, per below, would be great if I could easily drap n drop or wtvr to add more companies as needed from comps set.
1 - A formula that will give me a company's EG value for each year, the 1st set of figures, columns B - F.
2 - A formula that will give me a company's current year, this case 2012, rank relative to the comp set and also pull some statistical comparisons, here MEAN...COUNT, columns G - L
3 - A formula that will give me a company's rank for every year in the comp set, here from 2012 - 2009, columns M - Q
On the "Group Output" tab, I am trying to pull figures that correspond to the entire comps set. Here, I would like a formula that can pull the #N YoY EG company names/tickers for each year in the data. I would also like to pull the top 4 companies for the most recent year as well as the accompanying stat's for that year. As examples, columns B - F show the #1 YoY EG figures for each year, and below that shows the #2 YoY EG's for each year. Columns G - O show the 2012 top 4 and accompanying stats, below that the 2011 top 4 and accompanying stats...so 2 formulas total for this tab...
1 - YoY EG growth rates for a certain rank, e.g. #1 or #2, or any other rank I choose. Would like to add other ranks if I so choose, e.g. throw in dead last rank, #10, if desired for each year.
2 - Top 4 EG growth rates for a certain year, also be able to add more years if I so choose, say 2009 or whatever, and year's accompanying stats.
I hope that all makes sense, please ask any clarifying questions you may have. Thank you for reading this far and in advance for offering ur thoughts, any help is much appreciated. Thank you for your time.
Dom
DISCLAIMER - I provide no investment advice nor do I intend to.
Bookmarks