NESTED ANDIF STATEMENTS

[Mally]

Hi everyone. I've searched this site and the internet for something that
answers my specific question but can’t 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,B3>3),"Above",IF(AND(A3<12,B3<7),"On",IF(AND(A3<12,B3>6),"Above",IF(AND(A3<16,B3<4),"Under",IF(AND(A3<16,B3<10),"On",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

 

[Daryl S]

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,B3>3),AND(A3<12,B3>6)),"Above",IF(OR(AND(A3<16,B3<4),AND(A3=21,B3<7)),"Under","IF(AND(A3=21,B3<10),"",""))))

You can build the rest based on this idea.