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

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!



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

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


    Thank you TLisowski

  • 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",


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


This discussion has been closed.