Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"IF" "AND" Functions
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!!!!!!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"IF" "AND" Functions
-----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(A ND (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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"IF" "AND" Functions
See
http://www.cpearson.com/excel/nested.htm -- Kind Regards, Niek Otten Microsoft MVP - Excel "Craig S." wrote in 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!!!!!!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"IF" "AND" Functions
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 Craig S. wrote: 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!!!!!!!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"IF" "AND" Functions
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:D {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:D,2) Hope this helps Paul --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"IF" "AND" Functions
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 Craig S. wrote: 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!!!!!!!! . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
"IF" "AND" Functions
Mike, the correct formula should be: =IF(AND(E3=B3,E3<C3),D3,IF(AND(E3=B4,E3<C4),D4,I F(AND(E3=B5,E3<C5),D5,IF(AND(E3=B6,E3<C6),D6,"") ))) the references a 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 | +-------------------------------------------------------------------+ -- cscorp ------------------------------------------------------------------------ cscorp's Profile: http://www.excelforum.com/member.php...o&userid=24015 View this thread: http://www.excelforum.com/showthread...hreadid=188515 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Functions for "current" & "previous" month to calculate data | Excel Worksheet Functions | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |