Help- Return text based on ranking with criteria

Highlighted
Occasional Contributor

Hi There, 

 

My query is related to asset management data.

 

I am trying to extract the text of the largest 10 funds based on the fund meeting a criteria eg "high equity" and the fund having the largest asset size. 

 

Inn simple, return text if fund is in specified criteria and fund is largest in segment? 

 

So if you refer to the pictures, you will see "Excel Help 2" has the raw data. I know I could just sort, but want the formula to be dynamic. 

 

Thanks- appreciate the help 

 

Jordin 

11 Replies
Highlighted

@jordborer 

 

In the absence of the actual spreadsheet (the usefulness of images is limited; gives a sense of the data, but one can't do anything with it), I can suggest that you investigate the new Array Functions, in particular FILTER, SORT, SORTBY. They may well be able to accomplish what you describe.

 

I'd suggest you upload a working example--dummy the data if you want to protect confidentiality in some way--so that folks here don't have to create the example; that's a lot of data to work with.

Highlighted

@mathetes - thank you for the reply. I have attached the data set.

 

I am looking to pull the fund in sheet 1 based on the criteria in raw data that the fund must be of a certain category( high equity) and the largest based on total assets. 

 

Can you help? 

Highlighted

@jordborer 

 

I'll not be able to get to it until later on today--have some conflicting time obligations as the day starts.

 

If you want to give it a try, I'd suggest peeling off the "high equity" label from that column by using RIGHT.

Once that's done you could use SORT and FILTER to get the high equity funds only, sorted in descending order by asset value.

 

Others may come to your aide in the meantime.

Highlighted

@mathetes thanks. I want the formulae to be dynamic, so need more than just a filter. 

Highlighted

@jordborer 

This is one way to do it. 

Highlighted

Thanks Patrick!!

Highlighted

@Patrick2788 -  Thanks for the help. Would you have any idea how to do the rank function now, as I also need to rank the funds based on the segment criteria?

-So rank if "high equity" & etc?

 

Highlighted

@jordborer 

 

If Patrick's solution satisfied you, I'll be happy for you.

 

You notice, I trust, that he did use the FILTER function which IS dynamic. You responded a little too quickly with your rejection of it as "just a filter," understandably, if you weren't aware of the FILTER function but only the Data.....Filter capability of the older Excel.

 

These new dynamic array functions are just that, dynamic, and specifically dynamic with respect to arrays.

Highlighted

Thanks @mathetes. Only thing I am stuck with now is the numerical ranking. Any idea of a formulae that could return a numeric based on the original function 

Highlighted

@jordborer 

The formula, takes those records which meet the criteria ("high equity"), sorts the Assets in descending order then INDEX (w/ SEQUENCE) pulls out only 10 of those.

 

Conditional ranking is not as easy as it may seem.  RANK cannot be array entered.  Typically, the workaround is a SUMPRODUCT.  I don't think we need to use that workaround if you've got access to dynamic arrays.

Highlighted

@jordborer 

 

I've just been learning more how to use these Array functions that Microsoft has made available over the last 12 - 18 months, and used your spreadsheet as a vehicle for that learning. You may or may not be interested in the results, but it's possible that others here in the techcommunity could learn. That's only possible. What's certain is that there are experts here who could show me how what I've done could have been done a lot more efficiently. So learning will continue no matter what.

 

My goal (which I achieved) was to end up making it possible for you to flexibly enter the criteria for the selection of funds to be displayed, and then have that list be sorted in descending order by the value of assets. You started out asking for just the "High Equity" funds. But (as an investor myself) I figured there might be other categories that could also be meaningfully culled out. Then too I noticed that although most of the funds listed here are connected with South Africa, there were other regional categories. Finally, there was a set of funds variously classified as "Multi Asset," "Interest Bearing," "Equity" and "Real Estate".... So my goal again, was to make it possible for you to identify and sort by asset value Funds in any existing combination.

 

The easiest way to do that was to break apart the one field in your raw data sheet that reads with entries like these:

mathetes_0-1587479664324.png

These kinds of fields, though useful at the output end of things, are an abomination from a raw data point of view. It wasn't too hard to meet your initial request to pull out the "High Equity" funds only because at least that designation was at the right end of the field, and (again fortunately), it even was consistent in its presentation. So one could easily just say =RIGHT([@[Sector Classification]],11)="High Equity"

Getting the left "regional" category meant first locating the " - " which  was a little more complicated, not least because that little dash isn't a hyphen, but an "m-dash" (selected no doubt by a sadistic programmer)...nevertheless, that was not all that hard to isolate.

It was the MID formula, to pull out the type of asset in the mix (Real estate or equity or whatever), because you had to find both instances of " - ", start the string at the first, end it at the last...

But I worked them all through and ended up with three nice and neat basic data elements.

mathetes_1-1587480301948.png

 

Once that was done, it was simplicity itself to use UNIQUE to create lists of, well, distinct values in each category.

mathetes_2-1587480387579.png

And those, via an intervening step, become the basis for data validation (and item selection) here:

mathetes_3-1587480694090.png

Which, by means of this formula,

=IFERROR

(SORT

(FILTER(Analysis[[Fundname]:[Total Assets]],

(Analysis[Country]=Region)*

(Analysis[Category]=Category)*

(Analysis[EquityRange]=EquityMix),

"No matches"),

2,-1),

"No valid combinations")

 

gives the desired result (this one is based on those three criteria in the screen grab above) :

mathetes_5-1587481119198.png

 

Thank you for the opportunity to do all this. Now I have to get back to work.  (I'm retired, actually, but do have things I have to do today.)