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

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.

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.

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?

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.

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

Help- Return text based on ranking with criteria

11 Replies

mathetes
Gold Contributor
Apr 20, 2020
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.
- jordborer
  Copper Contributor
  Apr 20, 2020
  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?
  ASISA Flows Model.xlsm195 KB
  - mathetes
    Gold Contributor
    Apr 20, 2020
    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.

Forum Discussion

Help- Return text based on ranking with criteria

11 Replies

Resources