Dec 14 2019 04:51 AM
Hi,
This function behaves strange. I can not undestand why the result is what is:
=FILTER(SORT.BY(Tabell1[Club];Tabell1[Goals per match];-1);Tabell1[Goals per match]>=$P$10)
Goals per match for Viking is 1,498 for Vålerenga 1,496. So why do they show up in the list? And Odd, who should been in the list is left out.
This function return the righet list:
=FILTER(Tabell1[Club]&" - "&Tabell1[Goals per match];Tabell1[Goals per match]>=P10)
I have nticed it change, if I reorder the source Table. Sort it different. Should tha make any differnce for the Dynamic array functions?
Dec 14 2019 06:17 AM - edited Dec 14 2019 06:19 AM
SolutionThat's since to sorted array is applied criteria from unsorted array, they don't match. As variant that could be
=FILTER(SORTBY(Tabell1[Club],Tabell1[Goals per match],-1),SORTBY(Tabell1[Goals per match],Tabell1[Goals per match],-1)>=$P$10)
for the club and
=FILTER(SORTBY(Tabell1[Goals per match],Tabell1[Goals per match],-1),SORTBY(Tabell1[Goals per match],Tabell1[Goals per match],-1)>=$P$10)
for the goals.
Dec 14 2019 10:47 AM
Geir, you are welcome. In addition, I tried to play with modern Q&A in Ideas.
Started from simple question, but was not able to make condition as "is not less than"
Make question more formal, and Ideas returns desired table
Interesting...
Dec 14 2019 11:17 AM
In Excel one is never restricted to a single solution. Since your output is two columns one could always start by combining the columns you are interested into a two column array by using INDEX or CHOOSE
= IF( {1,0}, Tabell1[Club], Tabell1[Goals per match] )
The array can then be filtered
= FILTER( IF( {1,0}, Tabell1[Club], Tabell1[Goals per match] ),
Tabell1[Goals per match]>=G\M )
and sorted descending by the second column
= SORT( FILTER( IF( {1,0}, Tabell1[Club], Tabell1[Goals per match] ), Tabell1[Goals per match] >= G\M ), 2, -1 )
Dec 16 2019 12:46 AM
Hi, @Sergei Baklan first time I have seen Idea in use. Interesting and cool. I do not have that in my Exce-version yet. I have the insider version, but in Norwegian.
Geir
Dec 16 2019 12:47 AM
Dec 14 2019 06:17 AM - edited Dec 14 2019 06:19 AM
SolutionThat's since to sorted array is applied criteria from unsorted array, they don't match. As variant that could be
=FILTER(SORTBY(Tabell1[Club],Tabell1[Goals per match],-1),SORTBY(Tabell1[Goals per match],Tabell1[Goals per match],-1)>=$P$10)
for the club and
=FILTER(SORTBY(Tabell1[Goals per match],Tabell1[Goals per match],-1),SORTBY(Tabell1[Goals per match],Tabell1[Goals per match],-1)>=$P$10)
for the goals.