Filter Formula Top 5

[BillyRay]

Hello,

I have this formula which will filter for the top 5 but it goes to error if there aren't at least 5 in range.

I still would like the ranked list if there are less than 5 to select from.

Is this possible?
Thank you

=FILTER(AQ3:AS20,AR3:AR20>=LARGE(AR3:AR20,5))

 

[p45cal]

Try:

=FILTER(AQ3:AS6,AR3:AR6>=LARGE(AR3:AR6,MIN(5,ROWS(AQ3:AS6))))

or to reduce the number of times you need to refer to ranges:

=LET(a,AQ3:AS6,b,AR3:AR6,FILTER(a,b>=LARGE(b,MIN(5,ROWS(a)))))

or to refer to just one range, maybe:

=LET(a,AQ3:AS6,b,CHOOSECOLS(a,2),FILTER(a,b>=LARGE(b,MIN(5,ROWS(a)))))

 

[BillyRay]

Thank you p45cal. I opted for the first formula and it works great!

 

[Peter Bartholomew]

Hello,

I have this formula which will filter for the top 5 but it goes to error if there aren't at least 5 in range.

I still would like the ranked list if there are less than 5 to select from.

Is this possible?
Thank you

=FILTER(AQ3:AS20,AR3:AR20>=LARGE(AR3:AR20,5))

Another function that is also applicable would be TAKE, applied to a sorted dataset.

= TAKE(SORT(range, 2,-1), 5)

where 'range' represents the name you have defined to apply to your dataset.

 

[p45cal]

I haven't tested but wouldn't TAKE give a different result if there were ties in the top 5 (and there were more then 5 rows in the source)?

 

[Peter Bartholomew]

SORT ranks ties in the order in which they arise and TAKE treats them as separate entities. Usually that gives the desired result but, if not, I am sure additional rules can be accommodated.

 

[BillyRay]

I have some issues and have tried all of the formulas. On the Transactions DB sheet I put the formula in Q26.

This is intended to provide data for the chart on the Money Mgr sheet. I either get no results or get errors.

Can you take a look please?

The file is too large and I do not know why but here is the link: (I think I may give up on this file. It seems to be corrupted and doesn't let me save it saying there isn't enough data. I have almost 1TB of storage in my drive.)

Dropbox

www.dropbox.com

 

[p45cal]

For @BillyRay's information, the difference:

 

[p45cal]

I think I may give up on this file

Certainly a lot of work has gone into it, it looks very difficult to manage!
In the linked-to workbook below, an alternative solution a using pivot table and chart in the vicinity of cell Q32 of the Transactions DB sheet.
I've messed with that pivot table to make it look like your original results; I've hidden the drop downs and adjusted the pivot table's style to more or less match your results' appearance.
Additionally, next to that is a pivot chart possibly to replace the chart around cell P3 of the Money Mgr sheet.

I think a lot of that workbook could be brought up to date with more pivot tables and fewer formulae, perhaps even using Power Query (already built-in) or even Power BI instead (but I'm guessing that would be a steep learning curve), but it'll be hard work - it would probably easier to start from scratch!



FYI the vba snippet I used to hide the pivot dropdowns without hiding the field headers:

Set pt = Selection.PivotTable
For Each pf In pt.PivotFields
  pf.EnableItemSelection = False 'True to bring them back.
Next pf

where before running I selected a cell in the pivot table.
Link: https://app.box.com/s/x6c8wmznl22cle48piww2up003owcvzn

 

[Peter Bartholomew]

I have some issues and have tried all of the formulas. On the Transactions DB sheet I put the formula in Q26.

This is intended to provide data for the chart on the Money Mgr sheet. I either get no results or get errors.

Can you take a look please?

The file is too large and I do not know why but here is the link: (I think I may give up on this file. It seems to be corrupted and doesn't let me save it saying there isn't enough data. I have almost 1TB of storage in my drive.)

Dropbox

www.dropbox.com

The range ChartData2 is not dynamic so the Total column contains blanks, which messes up the SORT (blanks tend to come last, but you want the sort order reversed). Some additional filtering would provide one solution to the problems

= LET(
    totals, CHOOSECOLS(ChartData2, 2),
    data,   FILTER(ChartData2, totals<>""),
    TAKE(SORT(data, 2, -1), 5)
  )

 

[BillyRay]

Thanks guys I incorporated your additions. I don't know much about pivot tables so for now I am not using that.

I also eliminated the importing macros. I find copy and paste transactions manually is better for me since I can select which ones have not been added yet.

Maybe there are ways to improve the existing codes?