CODEWITHSUNDEEP
excel
Azaqi
Azaqi

Reputation: 125

Multiple nested IF statements in Excel

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

Upvotes: 2

Views: 934

Answers (2)

norie
norie

Reputation: 9857

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"})

Upvotes: 3

Wizhi
Wizhi

Reputation: 6549

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

Upvotes: 3

Related Questions