Forum Discussion
Using Rank Function but with filtering through another criteria as well.
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 🙂
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.
Good Morning Mtarler,
I am having an issue implanting the formula. So without trying to describe my issue in a wordy way I will just provide the sheet i was working on so you can see that on the Portfolio Tab in Column AQ(GIC Industry) that input is coming from column AF and Column AR(Size) is coming from Column AG(Size). What I am attempting to do is simply replace the existing Symbol in the portfolio if there is another symbol in that specific "GIC Industry" that has a better "Return Potential" Column AS. The source for this list is on the "Ranks" tab. Columns X, Y, Z, and AA on that tab are populated with the entire master list of names and sorted by Return Potential. What I am trying to do is ask whether the best possible choice of a particular industry is being chosen purely on a Potential Return basis? I hope this clarifies what I am trying to accomplish, and apologize for the simplification of the original scenario layout. DCL611