Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

#unparsable error on IF/AND statement

tobrien3
tobrien3 ✭
edited 12/09/19 in Archived 2017 Posts

 

 

Can anybody see the issue with this formula? 

 

I've counted and there are 8 open/8 closed parentheses,  and two criteria for each of the IF(AND( statements

=IF(ProdScore2/HrsWorked2 >= 4.4, "Outstanding",

IF(AND(ProdScore2/HrsWorked2 <= 4.3,ProdScore2/HrsWorked2 >=3.8 , "Very Strong",

IF(AND(ProdScore2/HrsWorked2 <= 3.7, ProdScore2/HrsWorked2 >=3.2, "Effective",

IF(AND(ProdScore2/ HrsWorked2 <= 3.1, ProdScore2/HrsWorked2 >=2.5 "Needs Improvement",

IF(ProdScore2/ HrsWorked2 <=2.4 , "Unsatisfactory"))))))))

 

Any help would be much appreciated!

Tags:
· Share on FacebookShare on Twitter

Comments

  • TLisowski
    TLisowski ✭

    Try this - 

     

    =IF(ProdScore1 / HrsWorked1 >= 4.4, "Outstanding", IF(ProdScore1 / HrsWorked1 >= 3.8, "Very Strong", IF(ProdScore1 / HrsWorked1 >= 3.2, "Effective", IF(ProdScore1 / HrsWorked1 >= 2.5, "Needs Improvement", IF(ProdScore1 / HrsWorked1 >= 0, "Unsatisfactory")))))

     

    I just tested it on my sheet that I mocked up to do the same thing and it worked.  You don't have to designate the entire range because the formula knows half of it every time already, as long as you order your formula correctly.  In this case, you're saying any result over 4.4 is Outstanding, any remaining result over 3.8 is Very Strong, and so forth.

    · Share on FacebookShare on Twitter
  • tobrien3
    tobrien3 ✭

    Ah that's simpler, and makes sense. It worked! 

     

    Thank you TLisowski

    · Share on FacebookShare on Twitter
  • Peter Kirkham
    Peter Kirkham ✭

    Yes, the suggested formula using nested IFs is a very standard way of doing this and much simpler. The reason why your IF(AND( statements were not working is that you were not closing the parentheses at the end of the AND e.g.


    =IF(ProdScore2/HrsWorked2 >= 4.4, "Outstanding",

    IF(AND(ProdScore2/HrsWorked2 <= 4.3,ProdScore2/HrsWorked2 >=3.8) , "Very Strong",

    etc.

    I'd also point out that your formula implicitly assumes that ProdScore has only 1 decimal place e.g. a ProdScore of 3.75 could not exist. Under your formula, if it were working properly, I believe that would default to "Unsatisfactory" when it is in fact borderline "Effective/Very Strong".


     

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories