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 🙂
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
DCL611 The formula appears to work (when modified for those column locations:
=IFERROR(INDEX(FILTER(Ranks!Y:Y,Ranks!Z:Z=AQ8),COUNTIF(AQ$8:AQ8,AQ8))," ")note you might want to use LARGE() instead of INDEX() in the above formula just in case the list isn't already sorted.
Unfortunately I am not familiar with the “Filter” function. When I type it into my formula window populates with the following:
“FILTERXML Returns specific data from the XML content using the specific XPath”
Am I doing something in correctly?
My version of Excel is the Office Home & Business 2019, is it possible my version is not adequate or am I simply missing something when trying to apply the formula?
When I pull the version up that was adjusted this is what appears in the Function window:
=IFERROR(INDEX(_xlfn._xlws.FILTER(Ranks!Y:Y,Ranks!Z:Z=AQ8),COUNTIF(AQ$8:AQ8,AQ8))," ")
Once again I truly apologize for any neophyte questions or actions but I simply am not having luck applying the formula when using “Filter” without having the “ _xlfn._xlws.” in there preceding “Filter”.
Thank you
Best
David