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).