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

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

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