Forum Discussion

laurenhruza's avatar
laurenhruza
Copper Contributor
Jul 26, 2018
Solved

Issue with Nested IF statement for reading %s that are both positive and negative

I am trying write a nested IF statement that based on reading the value of % in column BR, will return a statement based on the value bucket it falls into:

Greater than 5%

Between 2.51% and 5%

Between 0% and 2.5%

Between -2% and 0%

Between -2.01% and -5%

Greater than -5%

 

This is the formula I've written:

=IF(BR5>=5%),"Greater than 5%",IF(AND(BR5>=2.5%,BR5<5%),"Between 2.5% and 5%",IF(AND(BR5>0%,BR5<2.5%),"Between 0% and 2.5%",IF(BR5=0%),"No Assignment",IF(AND(BR5<0%,BR5>-2%),"Between 0% and -2%",IF(AND(BR5<=-2%,BR5>=-5%),"Between -2% and -5%",IF(AND(BR5<-5.01%),""))))))

But I continue to get the error message : "There is a problem with this formula etc... 1+1

 

Please help!! Thank you!

 

BI & Data Analytics
Need Help
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 26, 2018
    =IF(BR5>=5%,"Greater than 5%",
   IF(AND(BR5>=2.5%,BR5<5%),"Between 2.5% and 5%",
   IF(AND(BR5>0%,BR5<2.5%),"Between 0% and 2.5%",
   IF(BR5=0%,"No Assignment",
   IF(AND(BR5<0%,BR5>-2%),"Between 0% and -2%",
   IF(AND(BR5<=-2%,BR5>=-5%),"Between -2% and -5%",
   IF(AND(BR5<-5.01%),"")
))))))
  • Patricia Montgomery-Fernald's avatar
    Patricia Montgomery-Fernald
    Copper Contributor
    Jul 26, 2018

    I don't see how you ended your IF condition?  What's your criteria?  IF condition xyz is found THEN ... "Pass" else "Fail."  What's your condition?

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Jul 26, 2018
      =IF(BR5>=5%,"Greater than 5%",
   IF(AND(BR5>=2.5%,BR5<5%),"Between 2.5% and 5%",
   IF(AND(BR5>0%,BR5<2.5%),"Between 0% and 2.5%",
   IF(BR5=0%,"No Assignment",
   IF(AND(BR5<0%,BR5>-2%),"Between 0% and -2%",
   IF(AND(BR5<=-2%,BR5>=-5%),"Between -2% and -5%",
   IF(AND(BR5<-5.01%),"")
))))))

Resources