#unparsable error on IF/AND statement

tobrien3

 

 

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!

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.

tobrien3

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

 

Thank you TLisowski

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