Formula that returns Name of Nth Value...Formula that returns Nth Value for Name

[dsciola]

Have a formula question that I'm hoping the much wiser folk on this board can figure out.

Im trying to figure out 2 formulae:

Formula that will return the name for an Nth Value

Formula that will return Nth value for Name

I dont know how better to explain my question here than via an example, please see attached spreadsheet. Any further questions, please dont hesitate to ask. Also, all the earnings numbers listed are totally hypethetical and not actual for each company.


Thanks!

Dom

[dilipandey]

Hi Dom,

I am not clear about YoY ranking part but for top 5, you can use below formula:-

Please Login or Register  to view this content.

Enter this formula using ctrl shift enter key combination.. thx

Sample.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[benishiryo]

hi Dom, welcome to the forum. try this array formula for I9:

Please Login or Register  to view this content.

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again. it helps counter situations where 2 percentages are the same too

in B16, i think it should be 9? Amazon has the 9th highest. if so, try:
=RANK(VLOOKUP(LEFT($B$14,FIND(" ",$B$14)-1),$B$3:C$12,COLUMNS($B16:B16)+1,0),C3:C12)

[dsciola]

Wow, thanks guys, works great. The formula seemed to work though without the brackets, do I just need to use ctrl-shift-enter if I paste/build it into a different spereadsheet?

Im really trying to improve my skills with Excel, especially for financial modeling / valuation purposes. Any services either of y'all recommend for improving these skills? Lemme know if I'de be better off posting a new thread on this vs in this thread.

Thanks again! Lovin site already

Dom

[dsciola]

Ugh...I guess I still have a lot to learn about Excel...

So I tried putting the formulae into my main spreadsheet and it didnt quite work. Is it possibly because the formula is pulling across multiple tabs? The data is more or
less the same as in the original sample, main difference is the Nth outputs Im trying to derive via the above formulae are on separate tabs.

To better demo this, I updated the sample spreadsheet. I kept the info the same along with the new formulae that worked, and added 2 tabs, "Company Output" and "Group Output"with a couple examples for each. Both of these tabs is more resemblant of what info Im trying to pull and how Im trying to have it displayed.

Again, feel free to ask me any clarifying questions. Thanks again!

Dom

[dilipandey]

Hi dsciola,

Your above post is directed to myself OR to benishiryo ?



Regards,
DILIPandey
<click on below * if this helps>

[dsciola]

DiliPandey,

Thank you for following up. Its for anyone who wants to help

[dilipandey]

Nice

though what I meant, you got two formulas in post #2 and #3 and not sure which one you considered as you said :-

So I tried putting the formulae into my main spreadsheet and it didnt quite work.

Off-course I can go ahead and check your workbook to figure our which formula you used there, but still I need to make my previous point clear, hence this post


Regards,
DILIPandey
<click on below * if this helps>

[dsciola]

Nice

though what I meant, you got two formulas in post #2 and #3 and not sure which one you considered as you said :-




Off-course I can go ahead and check your workbook to figure our which formula you used there, but still I need to make my previous point clear, hence this post


Regards,
DILIPandey
<click on below * if this helps>

Thanks for responding and asking for clarity. Allow me to apologize on my first spreadsheet/request. You could say Im having trouble with both formulas, "Formula that will return the correct Name for an Nth value" and also "Formula that will return Nth value for a certain Name." However, I think my first spreadsheet/request didnt fully clarify what I'm trying to do.

I think my updated spreadsheet is more applicable to what Im trying to do. Please open it if interested.

On the "Company Output" tab, I have WFM and AMZN as the example companies here. Im trying to build the right formulas that will give me the figures I have listed for each company, as opposed to having to manually hard-code them in as I did here.

For WFM, I 1st have listed the Earnings Growth, EG, for the company in 2012, 2011, 2010, and 2009. These numbers again are hypothetical and are hard-coded from the "Data Input" tab. 2nd set of figures has WFM's rank in the Comp Set for 2012, 3rd highest EG here, along with the MEAN, MEDIAN, Adj'D MEAN, and COUNT for the Comp Set itself. These statistical figures were NOT in my first sample. Finally, the 3rd set has WFM's EG rank from 2012 - 2009, 3rd highest in 2012...4th in 2009.

So Im looking for 3 sets of formulas here.

1 - A formula that will give me a company's EG value for each year, the 1st set of figures.
2 - A formula that will give me its current year, this case 2012, rank relative to the comp set and also pull some statistical comparisons, here the MEAN, MEDIAN, ADJ'D MEAN, and COUNT.
3 - A formula that will give me a company's rank for every year in the comp set, here from 2012 - 2009.

It would be great if I could add another company to this tab, e.g. AMZN here, and easily drag and drop, cut and paste, or whatever to perform the same analysis above on another company, or however more many companies I wanna analyze in this manner and add below in this tab.

Hope that makes sense so far...now the next tab, "Group Output."

On this tab, 1st formula I'm trying to build is a formula that will give me the YoY EG figures for a certain rank. Here, I have first listed the "YoY #1's", which lists the companies that had the #1 EG growth rates for 2012 - 2009. Again, hard-coded here from the "Data Input" tab, hypothetical figures. Below this set is "YoY #2's", which lists the companies that had the #2 EG growth rates for 2012 - 2009.

To the right of these 2 sets of data, I have my 2nd formula I'm trying to build for this tab, a formula that will give the EG ranks for a particular year. Here, I first have the top 4 EG growth rates for 2012, followed again by a set of statistical measures, MEDIAN, MEAN, ADJ'D MEAN, and COUNT. Below this I then have the top 4 2011 EG Growth rates.

So then for "Group Output", I have 2 formulas Im trying to build here.

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 the dead last rank, #10, if I wanted for each year.
2 - Top 4 EG growth rates for a certain year. Would like to also be able to add more years if I so choose. Here I have 2012 and 2011 listed, would like to also easily add any other year I choose, say 2009 or whatever.

So I hope that all now makes sense. I have my updated spreadsheet attached again as well. Again my apologies for initial confusion, and also under-estimating how much I have to learn here I deeply appreciate your help thus far. Please offer any further thoughts/analysis as you wish and any clarifying questions you may need.

Sincerely,
Dominick

[dsciola]

Hi Dili,

Hope things are good with you. Did you have a moment to check out that updated formula question and sample spreadsheet I posted?

I hope my nagging doesn't come off as annoying or intrusive. It seemed to me you were interested in figuring out this formula that keeps eluding me . If I am mistaken and you don't have the time, please let me know. Otherwise, below is the link to the newest Thread I started on this formula and also the accompanying 'Most Up To Date Sample' spreadsheet:

http://www.excelforum.com/excel-form...37#post3185137

Please do not hesitate to ask any questions you may have. Thanks again for your time and consideration.

Dom