IF formula with multiple conditions and outcomes-CodePudding

I try to create IF formula to give me 3 different outcomes based on number in two cells (e.g. A1 and B1).

The 3 outcomes are:

"NO CHANGE" if A1 or B1 is equal to zero,
"CHANGE BELOW THRESHOLD" if A1 or B1 value is different than zero but below 10,000 and above -10,000,
"CHANGE" if A1 or B1 value is above 10,0000 or below -10,000.

I managed to create formula =IF(OR(A1<>0,B1<>0,"CHANGE","NO CHANGE")), howeveri I cannot find logic to test more conditions and give me 3 different results. Maybe I should try IFS?

I'd be really grateful for any help.

Thank you.

CodePudding user response：

This may need to be adjusted as your current description has a lot of "OR" statements but doesn't really specify how you would treat occasions where Criteria 2 and 3 overlap (such as A1=5000 and B1=22000).

Here I'm stating that "no change" = both values must be between -10000 and 10000 (unless one of the values is 0).

Adjust the "=" as needed, depending how you want to classify 10000 and -10000.

If you have IFS:

=IFS(OR(A1=0,B1=0),"no change", AND(A1>-10000,A1<10000,B1>-10000,B1<10000),"change below threshold",OR(A1<=-10000,A1>=10000,B1<=-10000,B1>=10000),"change")

Using Nested IF:

=IF(OR(A1=0,B1=0),"no change", IF(AND(A1>-10000,A1<10000,B1>-10000,B1<10000),"change below threshold","change"))