Forum Discussion

Jara2400's avatar
Jara2400
Copper Contributor
Jan 23, 2024

IF function

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?

Excel
  • djclements's avatar
    djclements
    Bronze Contributor
    Jan 23, 2024

    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.

Resources