ranking with criterias

[vp_10]

Dear Users, you helped me out so many times hopefully you can do it again one more time!
I included an example of my excel file,
The problem is that i want to rank the different stock in my example based on their returns which are showed by column H, the return J1 column. But there is a catch, i only want to rank the stocks which meet a certain criteria, that is, i only want the top, lets say 30% ranked, based on their highest market value. In short i only want to rank the stocks that meet the criteria of being in the top 30% in market value. Please help me, would be much appriacated, as always!

KR

[etaf]

does this work for you
=IF(RANK(H3,$H$3:$H$400)/COUNT($H$3:$H$400)>0.3,"Not within upper 30%",(RANK(H3,$H$3:$H$400)))

taken from this post by Ace_XL
http://www.excelforum.com/excel-form...-and-30-a.html

I did not understand the reference to both column H and column J sorry - so maybe not the formula you required

[vp_10]

Thanks for you fast reply, my comment was that the stocks needed to be ranked baswed on their highest value in comlum H, which has a header which says J1 return. The formula does not work good for my though, what i wanted is that i want to make a portfolio of stocks which has the highest return(column H) but which only is within the top 30% market value(column B) of the sample. The portfolio must consist of 5 stocks.
Thank you!

[vp_10]

Please someone help!!

[etaf]

As the site is run and contributed by volunteers it may take a little longer than 2hours to get further replies.
i would suggest if you have no reply in 24hrs , you would bump the thread

I have looked into the request further and not sure how you are grouping the portfolios of names , so not sure i understand the

The portfolio must consist of 5 stocks.

and how to group those names

i'm sure other more knowledgeable people will help soon

[vp_10]

I get it Etaf, and i will try to be more precise about my problem. I want to rank the stocks in my spreadsheet according to their historical returns, so use the =large function with the highest return(column H) at the top. The historical returns are stated in column H (named Return J-1 ((new-old)/old) ). But there is a criteria for this ranking, i only want to take the stocks in consideration who have a large market value(column B). I only want to rank the stocks who are in the highest 30% of this sample based on their market capatalization, so fill in =PERCENTILE.EXC(B3:B376,0.7) and all stocks with a market cap higher than 465.12 are eligible for ranking based on their historical return(column H)
Please help me i hope this info works!
Thanks

[newdoverman]

Here is your worksheet with the top 30% according to column B ranked. If you want to sort them in order, copy the data and paste values. This is necessary because the formulae will maintain the present order when sorting.