I'm trying to do a nested IF statement in excel where it takes the age in cell C20 and checks if it's in a certain range. If it is, it gets assigned a age range. When I reach the end of my IF statement, it says "There's a problem with your formula". What am I doing wrong?
The code I have is the following:
=IF(K20<=10,"<=10",IF(K20 >10 AND K20 <= 20,"11 to 20",IF(K20 > 20 AND K20 <=30, "21 to 30", IF(K20 >30 AND K20 <= 40, "31 to 40", IF(K20 >40 AND K20 <=50, "41 to 50", "51 and above")))))
Does this work for you?
=IF(K20<=10,"<=10",IF(AND(K20 >10, K20 <= 20),"11 to 20",IF(AND(K20 > 20,K20 <=30), "21 to 30", IF(AND(K20 >30,K20 <= 40), "31 to 40", IF(AND(K20 >40, K20 <=50), "41 to 50", "51 and above")))))
your AND operation is misplaced. In excel the function is stated first and then all the criteria's.
ex: AND(critera1, critera2,....)
This is a good time to use the =IFS function in excel. It's more clean and easier to maintain with multiple IFS, so I would highly recommend to use it in this case :)!
It works like, IFS(logical test_1, value if true_1, logical test_2; value if true_2, etc...)
=IFS(K20<=10,"<=10",AND(K20 >10, K20 <= 20),"11 to 20",AND(K20 > 20,K20 <=30), "21 to 30", AND(K20 >30,K20 <= 40), "31 to 40", AND(K20 >40, K20 <=50), "41 to 50",K20 >50, "51 and above")
You could replace your formula with this this.
=LOOKUP(K20,{0,10,20,30,40,50},{"<=10","11 to 20","21 to 30","31 to 40","41 to 50","51 and above"})