# Excel 2007 : Pick "n" largest values and return subsequent data

1. ## Pick "n" largest values and return subsequent data

Hi,

I have a workbook containing numerous stocks' monthly returns, and their monthly media coverage. What I am trying to do is picking "media winners" and adding these to a portfolio to evaluate the strategy's return against the market.

In the attached workbook I am having trouble to build a formula that will pick out the "n" (I want this to be a changeable number, it could also be a percentile if that is easier?) largest values (in "∆media") and return the subsequent data (from "returns iferror") to the sheet "portfolio". It has to pick stocks on a monthly basis, as the portfolio is to be rebalanced every month, based on the numerous stock's media coverage in the prior month.

I am clueless as of now all help would be greatly appreciated, thanks in advance

2. ## Re: Pick "n" largest values and return subsequent data

What I've got so far is that since the portfolio is unweighted (i.e. all picked stocks are wighted equally) the portfolio return is the average return of the picked stocks, my problem, however, is after identifying the "n" largest (currently I've worked with LARGE('∆media'!B2:AI2;{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}) as I can't figure how to change the number of stocks automatically, thought I could just enter something like {1;\$A\$1} where A1 contains the number of stocks I am interested in but that doesn't work..?) how do I get the corresponding monthly returns (from the 'returns iferror' sheet) for the chosen "media winners" (i.e. the one's with the largest change in coverage)?

Anyone? I'm having a bit of a time constraint on this one, and would appreciate any advice!

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1