Forum Discussion

lddbs's avatar
lddbs
Copper Contributor
Jun 17, 2024

sort(), filter() and IF() combined

I have a formula:
=SORT(IF(AX2 = "1+1"; FILTER(A2:J200; E2:E200 >= MAX(P:P)); 1; 1); FILTER(A2:J200; E2:E200 >= INDEX(P2:P200; X4-1)); 1; 1)
I want to sort a column by a condition statement.
For example, if the statement is true I want to filter and sort the numeric column with values only bigger and equal to the max() function result,

otherwise, I want to filter and sort the numeric column with values bigger or equal to max() - 1 (the preceding value).
Are there any workarounds for this, because the Excel pop-up with the window "can't calculate this formula" without helper columns if possible?

excel
Formulas and Functions

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 18, 2024

    lddbs 

    As a comment, using LET it could be bit easier to build the formula from a smaller logical parts

    =LET(
   bar;      IF(AX2 = "1+1"; MAX(P:P); INDEX(P2:P200; X4-1) );
   filtered; FILTER(A2:J200; E2:E200 >= bar);
   sorted;   SORT( filtered  );
 sorted )

     

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jun 18, 2024

    lddbs Can't really tell what result you expect, but the following formula will return a result. That is, it's a formula with the correct syntax and will not return an error. Up to you to decide if the result is correct or not. If not, please clarify by showing the underlying data and expected result.

    =SORT(IF(AX2 = "1+1"; FILTER(A2:J200; E2:E200 >= MAX(P:P)); FILTER(A2:J200; E2:E200 >= INDEX(P2:P200; X4-1)));1;1)

     

    The IF statement determines which filter will be applied. Then SORT will sort the outcome of the applied filter by the first column in ascending order. 

Resources