0

This is an IFS formula I wrote in Google Sheets:

IFS(B5=1,"Same", B5=-1,"Same", B5>1,"Narrower", B5<-1,"Narrower", B5=0,"Linear", 0>B5<1,"Wider")

Whenever I enter a value for B5 between 0 and 1, such as 0.5, it says there is "no match".

I think there is something wrong with the last section of the code. I was wondering if anyone knew how to fix this?

0

2 Answers 2

1

Instead of

0<B5<1

use

AND(B5>0,B5<1)

because the first is not a supported sintax.

0

Rubén's answer correctly identifies and corrects your error.

Your code can also be simplified by incorporating the ABS function instead of mirroring conditions for both positive and negative numbers.

For example ABS(n)=1 is equivalent to OR(n=1,n=-1) and ABS(n)>1is equivalent to OR(n>1,n<-1).

Incorporating Rubén's correction your formula could be reduced to:

IFS(ABS(B5)=1,"Same",
    ABS(B5)>1,"Narrower",
    B5=0,"Linear",
    AND(B5>0,B5<1),"Wider")) 

Based on the pattern above, I suspect your next step may have been to add AND(B5<0,B5>-1),"Wider"). If that is the case ABS can be used again:

IFS(ABS(B5)=1,"Same",
    ABS(B5)>1,"Narrower",
    B5=0,"Linear",
    AND(ABS(B5)>0,ABS(B5)<1),"Wider")) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.