Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 11:00 AM (PST)
Microsoft Tech Community

IF function

Copper Contributor

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 Replies




This can be done with a nested IF formula.

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