Home > database >  Filter column that other columns don't have only positive values and don't have only negat
Filter column that other columns don't have only positive values and don't have only negat

Time:12-20

Currently to make this filter I use this formula:

=FILTER(AC2:AC,
NOT((S2:S>0)*(T2:T>0)*(U2:U>0)*(V2:V>0)*(W2:W>0))*
NOT((S2:S<0)*(T2:T<0)*(U2:U<0)*(V2:V<0)*(W2:W<0))
)

I would like to know if there is a correct and more direct method for this same result.

CodePudding user response:

Your title is a bit confusing, but from your formula my interpretation is that you are filtering out rows from column AC where the corresponding row in columns S:W are all either positive or negative. You could try:

=filter(AC2:AC,byrow(S2:W,lambda(row,abs(sum(sign(row)))<>5)))

SIGN returns 1 for ve numbers and -1 for -ves. We can get the ABS value of the SUM of SIGNs per row with BYROW, so it's simply a case of FILTERing by those rows not equal to 5 (only observed for all ves or all -ves in a row).

  • Related