|
NESTED ANDIF STATEMENTS
Hi everyone. I've searched this site and the internet for something that
answers my specific question but cant seem to find anything. I did the following formula that worked OK but I am aware of excel 2003 being limited to 7 nested if statements. =IF(AND(A3<8,B3<4),"On",IF(AND(A3<8,B33),"Above", IF(AND(A3<12,B3<7),"On",IF(AND(A3<12,B36),"Above" ,IF(AND(A3<16,B3<4),"Under",IF(AND(A3<16,B3<10),"O n",IF(AND(A3=21,B3<7),"Under","IF(AND(A3=21,B3<10) "))))))) I found something on the net regarding named ranges but this only had if statements. What I need simply is a formula to show if age is 0-7 and score is 0-3 then the result is on if age is 0-7 and score is 4 then the result is Above if age is 8-11 and score is 1-6 then the result is on etc., etc. Thank you in advance for any help. Below is my criteria A B C 1 Age Score Result 2 0-7 0-3 on 3 0-7 4+ Above 4 8-11 1-6 on 5 8-11 7+ Above 6 12-15 1-3 under 7 12-15 4-6 on 8 12-15 7+ over 9 16-20 1-3 under 10 16-21 4-9 on 11 16-22 10+ over 12 21 1-6 under 13 21 7-9 on 14 21 10+ over 15 22-26 1-6 under 16 22-27 7-12 on 17 22-28 13+ over 18 27-29 1-9 under 19 27-29 10-12 on 20 27-29 13-16 over 21 30-39 1-9 under 22 30-40 10-15 on 23 30-41 16 over 24 40-50 1-12 under 25 40-51 13-16 on 26 51-60 1-15 under 27 51-60 16 on |
NESTED ANDIF STATEMENTS
Mally -
Since your data looks contiguous, you can combine any of the IF statements with the same results using an OR. I changed your original statement to the one below, though your statement didn't have a good final result, so mine only has pairs of double-quotes. You will need to know what the final result is for "AND(A3=21,B3<10)", and maybe it can go with the "On", "Above", or "Under" results: =IF(OR(AND(A3<8,B3<4),AND(A3<12,B3<7),AND(A3<16,B3 <10)),"On",IF(OR(AND(A3<8,B33),AND(A3<12,B36))," Above",IF(OR(AND(A3<16,B3<4),AND(A3=21,B3<7)),"Und er","IF(AND(A3=21,B3<10),"","")))) You can build the rest based on this idea. -- Daryl S "Mally" wrote: Hi everyone. I've searched this site and the internet for something that answers my specific question but cant seem to find anything. I did the following formula that worked OK but I am aware of excel 2003 being limited to 7 nested if statements. =IF(AND(A3<8,B3<4),"On",IF(AND(A3<8,B33),"Above", IF(AND(A3<12,B3<7),"On",IF(AND(A3<12,B36),"Above" ,IF(AND(A3<16,B3<4),"Under",IF(AND(A3<16,B3<10),"O n",IF(AND(A3=21,B3<7),"Under","IF(AND(A3=21,B3<10) "))))))) I found something on the net regarding named ranges but this only had if statements. What I need simply is a formula to show if age is 0-7 and score is 0-3 then the result is on if age is 0-7 and score is 4 then the result is Above if age is 8-11 and score is 1-6 then the result is on etc., etc. Thank you in advance for any help. Below is my criteria A B C 1 Age Score Result 2 0-7 0-3 on 3 0-7 4+ Above 4 8-11 1-6 on 5 8-11 7+ Above 6 12-15 1-3 under 7 12-15 4-6 on 8 12-15 7+ over 9 16-20 1-3 under 10 16-21 4-9 on 11 16-22 10+ over 12 21 1-6 under 13 21 7-9 on 14 21 10+ over 15 22-26 1-6 under 16 22-27 7-12 on 17 22-28 13+ over 18 27-29 1-9 under 19 27-29 10-12 on 20 27-29 13-16 over 21 30-39 1-9 under 22 30-40 10-15 on 23 30-41 16 over 24 40-50 1-12 under 25 40-51 13-16 on 26 51-60 1-15 under 27 51-60 16 on |
| All times are GMT +1. The time now is 08:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com