Filter Function Problem Handling Negative Numbers

[Andrew100]

Looking for some help. I have a data table showing sales people with the dollar value they sold last month. Yes, some salespeople have negative values because of returned product. I wrote a formula using SORT and FILTER to try and create a list of the sales people, rank ordered by the sales value, but where we excluded salespeople with ZERO dollars in the period. The formula -- which you can see in the formula bar in the below image -- *almost* works perfectly well. HOWEVER, the sorted list the formula creates weirdly leaves out some of the sales people with large, negative values. I highlighted in yellow the salespeople whose names didn't get captured in the list the formula creates. The list includes most of the salespeople with negative values, not not all. For some reason, it is excluding certain negative values. I cannot for the life of me figure out why this formula doesn't return a sorted list of ALL of the salespeople (excluding zero dollars people) including ALL of the ones with negative numbers. Can someone possibly solve this problem for me?

 

[Fluff]

Hi & welcome to MrExcel.
You cannot filter on a sorted range like that, how about

=TAKE(SORT(FILTER(Q78:T120,T78:T120<>0),4,-1),,1)

 

[Andrew100]

Hi & welcome to MrExcel.
You cannot filter on a sorted range like that, how about

=TAKE(SORT(FILTER(Q78:T120,T78:T120<>0),4,-1),,1)

First, thanks for the quick reply. I tried your formula. It returns a #Name? error. I can confirm that the sort and filter portions work. It will return an array, sorted by value and with the ZERO dollar salespeople excluded, but at the moment it returns all four columns when I only want the first column of names returned. When I try the TAKE -- to select only the first column -- it generates that #Name? error. I feel like we're close, though. Any further thoughts? (thank you so much)

 

[Fluff]

My fault, I didn't look at your profile & 2021 doesn't have the TAKE function. Try

=LET(s,SORT(FILTER(Q78:T120,T78:T120<>0),4,-1),INDEX(s,SEQUENCE(ROWS(s)),1))

 

[Andrew100]

First, thanks for the quick reply. I tried your formula. It returns a #Name? error. I can confirm that the sort and filter portions work. It will return an array, sorted by value and with the ZERO dollar salespeople excluded, but at the moment it returns all four columns when I only want the first column of names returned. When I try the TAKE -- to select only the first column -- it generates that #Name? error. I feel like we're close, though. Any further thoughts? (thank you so much)View attachment 102429

I have Excel 2021. I think the Take function only works in Microsoft 365. Are there any downsides to trying to change my subscription to 365?

 

[Fluff]

IMO their are no downsides, but 2021 is not a subscription so you have already bought it.

 

[Andrew100]

My fault, I didn't look at your profile & 2021 doesn't have the TAKE function. Try

=LET(s,SORT(FILTER(Q78:T120,T78:T120<>0),4,-1),INDEX(s,SEQUENCE(ROWS(s)),1))

Fantastic. You solved the problem. I am going to go familiarize myself with Let and Sequence. Thank you so much for your help today.

 

[Peter_SSs]

Welcome to the MrExcel board!

Here are a couple of other options

=FILTER(SORTBY(Q78:Q120,T78:T120,-1),SORT(T78:T120,,-1)<>0)

The following one could be preferable though. Your data appears to be in a formal Excel table so this one does not require you to use row numbers and will automatically expand/contract the relevant ranges if your table rows grow or shrink. Check your table name of course.

=LET(s,SORT(Table1,4,-1),INDEX(FILTER(s,INDEX(s,0,4)<>0),0,1))