IF function

[Jara2400]

I want to display 0 if the number is between 30 and 160, 1 if it is below 30 and 2 if it is above 160. How do I do this?

[#2]

@Jara2400 

=IF(AND(A1>=30,A1<=160),0,IF(A1<30,1,IF(A1>160,2,"")))

This can be done with a nested IF formula.

[#3]

@Jara2400 There's a few different functions that can be used here... IFS, SWITCH or two nested IF functions.

Option 1: IFS function

=IFS(A1<30, 1, A1>160, 2, TRUE, 0)

Option 2: SWITCH function

=SWITCH(TRUE, A1<30, 1, A1>160, 2, 0)

Option 3: nested IF function

=IF(A1<30, 1, IF(A1>160, 2, 0))

In this scenario, only two logical tests are required to generate the desired outcome... if both the first expression (A1<30) and the second expression (A1>160) return FALSE, it will return 0 because it means A1 is between 30 and 160.