Forum Discussion
Using Rank Function but with filtering through another criteria as well.
Hello to anyone who can help and thank you in advance,
I would like to populate a table that is based on the Rank of 1 criteria, "Potential Return", but then also specifically for another criteria which it must match, "GIC Industry". This criteria includes a list of over 60 "GIC Industry" labels. Examples being "Software", "Chemicals", or "IT Services". My issue is that I do not know how to create the correct formula so that Excel skips the top ranked name after having already returned it once and move on to the 2nd, 3rd, or 4th-depending on how many times that "GIC Industry" appears and then populate correctly. I have attached a sheet which has the raw data attached, I have multiple tabs so for purposes of this specific issue I thought I would just attached the view I am trying to adjust and the source from where I am pulling it from. In column B the "Symbol", which is a stock symbol, will populate based on the the "GIC Industry"-Column C- and then the top ranked "Return Potential" for all symbols in that "GIC Industry". As you can see in "Column J" I have all symbols from a master list populated and Ranked based entirely on their "Return Potential". My issue is that after the top ranked return potential for "Software"-cellC4- populates into cellB4-"BOX US Equity"-how do I create the logic so that if "Software" appears again below-which it does 5 more times in cells C5, C6, C10, C14, C15- Excel returns the 2nd, 3rd, 4th, 5th, and 6th ranked names in the "Software" Industry name-Column I- into the corresponding cells in Column B? As is shown, the logic I used kept returning the top ranked return potential which was BOX US Equity. If you scroll down to cells H48 and I48 you can see that "NET US Equity" is the 2nd ranked "Software" name by "Return Potential". I would like to know how to create the logic that would make "NET US Equity" populate into cell B8 because it would be the 2nd top 'Return potential" symbol in the specific Industry of Software. Thank you in advance and I really hope I explained this somewhat coherently.
DCL611 You are not doing anything wrong, your version of Excel doesn't have dynamic arrays (which means you also don't have some of the new functions that come with it like FILTER(), UNIQUE() and some others). So going back to old school tricks I think this should work for you:
=IFERROR(OFFSET(Ranks!$Y$1,AGGREGATE(15,7, ROW(Ranks!$Z:$Z)/(Ranks!$Z:$Z=$AQ8),COUNTIF($AQ$1:$AQ8,$AQ8))-1,0)," ")see attached. If it works for you I hope you like and mark this as best answer 🙂
9 Replies
Forgot to add sheet....
DCL611 I used the following formula to get what you want but I'm sure there are others that might work also. This does assume you have array formulas in your version of excel:
=INDEX(FILTER(H:H,I:I=C4),COUNTIF(C$1:C4,C4))I did a similar formula for column E (Return Potential) because I assume you will need that too. I don't know where column D (Size) comes from. I have attached the sheet for you to review. I hope that helps.
