Excel 2007 : Pick next large/small value

Hi,

I have been working on this for ever, without figuring it out. Tried to explain my problem in a different thread a couple of weeks ago: http://www.excelforum.com/excel-2007...uent-data.html

However, I have now taken a different approach. I currently have a sheet that identifies the 30 largest and smallest changes in media coverage for a set of stocks, my problem is that some changes are equal for some stocks - ie a simple VLOOKUP or INDEX only returns the first stock, I then tried som VBA to find the nth occurence (http://www.ozgrid.com/VBA/occurrence-lookup.htm), but this formula requires that i specify what occurrence I wish to return - with the amount of data I am looking at, that's not an option...

Thus, my question is: Can I somehow tell excel to automatically find the next large occurrence (without specifying how many there are) and return the Stock name, so that I can index the stock's return in my "index-sheet"?

Thanks in advance guys, really need help on this - my bachelor-thesis is depending on it

/Borge

Hi Marcol,

Thanks for the reply! I will check out your workbooks tomorrow, both looks like good alternatives. 05/96, 06/96 ar part dates yes, May and June 1996. Does that make any difference..?

My second question is: will a maximum of, say, 30 stocks be chosen? Because I have figured out a way using and if and large(XX:XX,31) to choose the ones with better or worse performance than #31, the problem here however, especially if I want to short "media losers" is that the bottom 50 or more has a 100% negative (relative) change (i.e. gone from say 1 to 0 hits), and therefor too many stocks are picked out for my portfolio...

Haha, back to school you say?

Cheers

Thanks again Marcol.
This is a brilliant approach, one question though (don't have to much experience with drop-downs); will I be able to match the chosen stocks with their corresponding monthly returns for each month over the last 15 years, or will I manually have to change month in order to do so? The reason I am wondering is that I am planning to calculate the cumulative return (for the past 15 years) of the media strategy portfolio, which is rebalanced monthly based on the prior month's media coverage (i.e. the top/bottom 30 to go long/short)

I'm getting a "Database error" when trying to upload the .zip, so I've added the file to my Public folder on Dropbox: http://dl.dropbox.com/u/1719758/perc...folio.xlsx.zip

I'm doing different approaches to the strategy, so the one I have uploaded is the percentage-approach, where I pick the X% (in this case 5%) best/worst "media-performers" to go long/short, and use dummy-variables and SUMPRODUCT to get the cumulative return.

Hope this illustrates what I'm trying to achieve..?