"IF" "AND" Functions

[Craig S.]

I am using the "IF" & "AND" statments to have a certain
number look into a table and pick a ceratin cell.

The formula I used will not accept the last AND
statement. My question is, does excel limit the number
of "AND" statements you can use in a formula?

My next question is, is there another function I can use.

I have a listing as follows:

1-10 = a
10-20 =b
20-30 =c
30-40 =d

I have a cell the contains the value of 33. I want the
next cell to contain the formula that will return the
value of "d" to this cell. I know I can use IF & AND
functions, but the formula below is giving me an error.




=IF(AND(C222>=$B$232,C222<=$C$232),$AA$234,IF(AND
(C222>=$B$235,C222<=$C$235),$AA$237,IF(AND
(C222>=$B$238,C222<=$C$238),$AA$240,IF(AND
(C222>=$B$241,C222<=$C$241),$AA$243,IF(AND
(C222>=$B$244,C222<=$C$244),$AA$246,IF(AND
(C222>=$B$247,C222<=$C$247),$AA$249,IF(AND
(C222>=$B$250,C222<=$C$250),$AA$252,IF(AND
(C222<=$C$253,C222>=B253),$AA$255,0))))))))


Thanks for any help!!!!!!!!

 

[Mike]

-----Original Message-----
I am using the "IF" & "AND" statments to have a certain
number look into a table and pick a ceratin cell.

The formula I used will not accept the last AND
statement. My question is, does excel limit the number
of "AND" statements you can use in a formula?

My next question is, is there another function I can use.

I have a listing as follows:

1-10 = a
10-20 =b
20-30 =c
30-40 =d

I have a cell the contains the value of 33. I want the
next cell to contain the formula that will return the
value of "d" to this cell. I know I can use IF & AND
functions, but the formula below is giving me an error.




=IF(AND(C222>=$B$232,C222<=$C$232),$AA$234,IF(AND
(C222>=$B$235,C222<=$C$235),$AA$237,IF(AND
(C222>=$B$238,C222<=$C$238),$AA$240,IF(AND
(C222>=$B$241,C222<=$C$241),$AA$243,IF(AND
(C222>=$B$244,C222<=$C$244),$AA$246,IF(AND
(C222>=$B$247,C222<=$C$247),$AA$249,IF(AND
(C222>=$B$250,C222<=$C$250),$AA$252,IF(AND
(C222<=$C$253,C222>=B253),$AA$255,0))))))))


Thanks for any help!!!!!!!!
.
If you can send me your file...or send me a

file/spreadsheet with bogus numbers in it, I may be able
to play with it. I have used if/ands before... but not to
this extent.. but you never know.

Mike

 

[Niek Otten]

See

http://www.cpearson.com/excel/nested.htm

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Frank Kabel]

hi Craig,
Excel is limited to 7 nested staements. For your issue I would use
VLOOKUP. Change your listing as follows (split the range numbers):
A B C
1 10 a <- you have to decide where the 10 belongs
11 20 b
....

Now if your cell D1 contains the number 33 use the following formula to
get the character:
=VLOOKUP(D1,A1:C20,3,1)

HTH
Frank

 

[Paul_Russell]

Hi Craig,
The problem with this function is not the number of "AND"s but the
number of "IF"s, excel only allows 7 nested "IF" functions, and
unfortunately yours has 8.

If you set up a table along the lines of:

C D <= Column heading
---- -------
1 a
10 b
20 c
30 d
40 Out of Range

And then run a Vlookup without entering False in the range box then
Excel will return a for values 1-9, b for values 10-19 etc..

Enter this formula =VLOOKUP(A1 {is the cell where your number is
entered} ,C {are the colums where your range is} ,2 {denotes that you
want excel to lookup the value in column 2})

Without the explanation it would look something look like this:

=VLOOKUO(A1,C,2)

Hope this helps

Paul

 

[Guest]

Thanks for your help, I will use VLOOKUP!

-----Original Message-----
hi Craig,
Excel is limited to 7 nested staements. For your issue I would use
VLOOKUP. Change your listing as follows (split the range numbers):
A B C
1 10 a <- you have to decide where the 10 belongs
11 20 b
....

Now if your cell D1 contains the number 33 use the following formula to
get the character:
=VLOOKUP(D1,A1:C20,3,1)

HTH
Frank




.

 

[cscorp]

Mike, the correct formula should be:

=IF(AND(E3>=B3,E3<C3),D3,IF(AND(E3>=B4,E3<C4),D4,IF(AND(E3>=B5,E3<C5),D5,IF(AND(E3>=B6,E3<C6),D6,""))))

the references are:

E3=Value to check
B3=1
C3=10
D3=A

B4=10
C4=20
E4=B

B5=20
C5=30
D5=C

B6=30
C6=40
D6=D

Formula is placed in cell F3. It is always comparing that the value to
be verified must be grater than or equal than the first value and less
than the second value.
Let me know if that solves the problem.

best regards.

Juan Carlos


+-------------------------------------------------------------------+
|Filename: SampleSpreadsheet.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3455 |
+-------------------------------------------------------------------+