| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Get Past the 7 Nested IF limit
I am trying to create a mileage tracker sheet based upon a pre-set table of
distances. I would like to be able to choose 2 different locations from drop down lists in 2 different columns, then have Excel input the mileage between them in a new column and eventually autosum the column. I tried this using a series of nested IFs then realized I would run into the 7 IF limit. My problem is that I am not a developer and don't work in VB. Here are the nested IF statements I was using: rather bulky but they worked: =IF(AND(B3="ADMIN",C3="FHS"),3.5,IF(AND(B3="ADMIN" ,C3="CMS"),9,IF(AND(B3="ADMIN",C3="FMS"),0.2,IF(AN D(B3="ADMIN",C3="LOR"),0.8,IF(AND(B3="ADMIN",C3="A BR"),10,IF(AND(B3="ADMIN",C3="ARA"),1,IF(AND(B3="A DMIN",C3="PAT"),9.2))))))) My problem is that I have to add 3 more ifs in this section alone, then have the same thing for the 10 other locations. any suggestions would be appreciated. -- You want me to do what?!?! |
|
#2
|
|||
|
|||
|
|
|
#4
|
|||
|
|||
|
This may help clarify what I need:
Destination TO FROM Mileage ADMIN ABR 9 ADMIN FHS 3.5 ADMIN LOR 0.8 The above portion is from the sheet and is what I need. I choose Admin in the "To" column, ABR in the "From" column and the Mileage populated itself with 9, which is the mileage between the 2 locations. The same can be said of the next 2 entries. My problem is that I have 11 locations, which pops me above the 7 If limit. -- You want me to do what?!?! "Tech_in_the_woods" wrote: Can you explain to me what this is doing? I can be very dense. -- You want me to do what?!?! "Don Guillett" wrote: see if this idea helps =IF(AND(B3="a",OR(C3="b",C3="c",C3="d")),1,2) -- Don Guillett SalesAid Software "Tech_in_the_woods" wrote in message ... I am trying to create a mileage tracker sheet based upon a pre-set table of distances. I would like to be able to choose 2 different locations from drop down lists in 2 different columns, then have Excel input the mileage between them in a new column and eventually autosum the column. I tried this using a series of nested IFs then realized I would run into the 7 IF limit. My problem is that I am not a developer and don't work in VB. Here are the nested IF statements I was using: rather bulky but they worked: =IF(AND(B3="ADMIN",C3="FHS"),3.5,IF(AND(B3="ADMIN" ,C3="CMS"),9,IF(AND(B3="AD MIN",C3="FMS"),0.2,IF(AND(B3="ADMIN",C3="LOR"),0.8 ,IF(AND(B3="ADMIN",C3="ABR "),10,IF(AND(B3="ADMIN",C3="ARA"),1,IF(AND(B3="ADM IN",C3="PAT"),9.2))))))) My problem is that I have to add 3 more ifs in this section alone, then have the same thing for the 10 other locations. any suggestions would be appreciated. -- You want me to do what?!?! |
|
#5
|
|||
|
|||
|
I would prefer the following, which gives the same result:
=IF(AND(B3="ADMIN",C3="FHS"),3.5,0)+IF(AND(B3="ADM IN",C3="CMS"),9,0)+IF(AND(B3="ADMIN",C3="FMS"),0.2 ,0)+IF(AND(B3="ADMIN",C3="LOR"),0.8,0)+IF(AND(B3=" ADMIN",C3="ABR"),10,0)+IF(AND(B3="ADMIN",C3="ARA") ,1,0)+IF(AND(B3="ADMIN",C3="PAT"),9.2,0) "Tech_in_the_woods" wrote: I am trying to create a mileage tracker sheet based upon a pre-set table of distances. I would like to be able to choose 2 different locations from drop down lists in 2 different columns, then have Excel input the mileage between them in a new column and eventually autosum the column. I tried this using a series of nested IFs then realized I would run into the 7 IF limit. My problem is that I am not a developer and don't work in VB. Here are the nested IF statements I was using: rather bulky but they worked: =IF(AND(B3="ADMIN",C3="FHS"),3.5,IF(AND(B3="ADMIN" ,C3="CMS"),9,IF(AND(B3="ADMIN",C3="FMS"),0.2,IF(AN D(B3="ADMIN",C3="LOR"),0.8,IF(AND(B3="ADMIN",C3="A BR"),10,IF(AND(B3="ADMIN",C3="ARA"),1,IF(AND(B3="A DMIN",C3="PAT"),9.2))))))) My problem is that I have to add 3 more ifs in this section alone, then have the same thing for the 10 other locations. any suggestions would be appreciated. -- You want me to do what?!?! |
|
#6
|
|||
|
|||
|
Use a VLOOKUP formula:
Either list your destinations in a spare column with the distances in the next column to the right - say G1:H3 or make a named list: Insert Name Define Give it a name - say "To" (without the quotes) and then in the "Refers to" box enter: ={"FHS",3.5;"CMS",9;"FMS",0.2} (filled out to the full list) Note that there is a comma between the Destination and the milage and a semi colon between each set of Destination/milage data. then use: =IF(B1="Admin",VLOOKUP(A1,G1:H3,2),"") for the worksheet list or =IF(B1="Admin",VLOOKUP(A1,To,2),"") for the defined name list -- HTH Sandy Replace@mailinator with @tiscali.co.uk "Tech_in_the_woods" wrote in message ... I am trying to create a mileage tracker sheet based upon a pre-set table of distances. I would like to be able to choose 2 different locations from drop down lists in 2 different columns, then have Excel input the mileage between them in a new column and eventually autosum the column. I tried this using a series of nested IFs then realized I would run into the 7 IF limit. My problem is that I am not a developer and don't work in VB. Here are the nested IF statements I was using: rather bulky but they worked: =IF(AND(B3="ADMIN",C3="FHS"),3.5,IF(AND(B3="ADMIN" ,C3="CMS"),9,IF(AND(B3="ADMIN",C3="FMS"),0.2,IF(AN D(B3="ADMIN",C3="LOR"),0.8,IF(AND(B3="ADMIN",C3="A BR"),10,IF(AND(B3="ADMIN",C3="ARA"),1,IF(AND(B3="A DMIN",C3="PAT"),9.2))))))) My problem is that I have to add 3 more ifs in this section alone, then have the same thing for the 10 other locations. any suggestions would be appreciated. -- You want me to do what?!?! |
|
#7
|
|||
|
|||
|
This was great and worked to a point, the problem was that the formula became
too long, so I can't finish it out. -- You want me to do what?!?! "Martin P" wrote: I would prefer the following, which gives the same result: =IF(AND(B3="ADMIN",C3="FHS"),3.5,0)+IF(AND(B3="ADM IN",C3="CMS"),9,0)+IF(AND(B3="ADMIN",C3="FMS"),0.2 ,0)+IF(AND(B3="ADMIN",C3="LOR"),0.8,0)+IF(AND(B3=" ADMIN",C3="ABR"),10,0)+IF(AND(B3="ADMIN",C3="ARA") ,1,0)+IF(AND(B3="ADMIN",C3="PAT"),9.2,0) "Tech_in_the_woods" wrote: I am trying to create a mileage tracker sheet based upon a pre-set table of distances. I would like to be able to choose 2 different locations from drop down lists in 2 different columns, then have Excel input the mileage between them in a new column and eventually autosum the column. I tried this using a series of nested IFs then realized I would run into the 7 IF limit. My problem is that I am not a developer and don't work in VB. Here are the nested IF statements I was using: rather bulky but they worked: =IF(AND(B3="ADMIN",C3="FHS"),3.5,IF(AND(B3="ADMIN" ,C3="CMS"),9,IF(AND(B3="ADMIN",C3="FMS"),0.2,IF(AN D(B3="ADMIN",C3="LOR"),0.8,IF(AND(B3="ADMIN",C3="A BR"),10,IF(AND(B3="ADMIN",C3="ARA"),1,IF(AND(B3="A DMIN",C3="PAT"),9.2))))))) My problem is that I have to add 3 more ifs in this section alone, then have the same thing for the 10 other locations. any suggestions would be appreciated. -- You want me to do what?!?! |
|
#8
|
|||
|
|||
|
I am trying to wrap my mind around this-- it makes more sense everytime I
read it. I am still having some issues understanding how I could change the "From" with this method and still have it reflect the correct mileage. Would I need to make a VLOOKUP table for every possible combination of mileage? "Sandy Mann" wrote: Use a VLOOKUP formula: Either list your destinations in a spare column with the distances in the next column to the right - say G1:H3 or make a named list: Insert Name Define Give it a name - say "To" (without the quotes) and then in the "Refers to" box enter: ={"FHS",3.5;"CMS",9;"FMS",0.2} (filled out to the full list) Note that there is a comma between the Destination and the milage and a semi colon between each set of Destination/milage data. then use: =IF(B1="Admin",VLOOKUP(A1,G1:H3,2),"") for the worksheet list or =IF(B1="Admin",VLOOKUP(A1,To,2),"") for the defined name list -- HTH Sandy Replace@mailinator with @tiscali.co.uk "Tech_in_the_woods" wrote in message ... I am trying to create a mileage tracker sheet based upon a pre-set table of distances. I would like to be able to choose 2 different locations from drop down lists in 2 different columns, then have Excel input the mileage between them in a new column and eventually autosum the column. I tried this using a series of nested IFs then realized I would run into the 7 IF limit. My problem is that I am not a developer and don't work in VB. Here are the nested IF statements I was using: rather bulky but they worked: =IF(AND(B3="ADMIN",C3="FHS"),3.5,IF(AND(B3="ADMIN" ,C3="CMS"),9,IF(AND(B3="ADMIN",C3="FMS"),0.2,IF(AN D(B3="ADMIN",C3="LOR"),0.8,IF(AND(B3="ADMIN",C3="A BR"),10,IF(AND(B3="ADMIN",C3="ARA"),1,IF(AND(B3="A DMIN",C3="PAT"),9.2))))))) My problem is that I have to add 3 more ifs in this section alone, then have the same thing for the 10 other locations. any suggestions would be appreciated. -- You want me to do what?!?! |
|
#9
|
|||
|
|||
|
..... I am still having some issues understanding how I could change
the "From" with this method and still have it reflect the correct mileage. Sorry, perhaps it was my fault for not realising what you wanted but your original post specified only "Admin" as the "From" with the other name as destinations I assumed that you only wanted to lookup distances in one direction. If you construct a table as below: G H I J K L 1 One Two Three Four Five 2 One 0 10 15 20 25 3 Two 10 0 7 12 19 4 Three 15 7 0 8 10 5 Four 20 12 8 0 9 6 Five 25 19 10 9 0 (But of course use your own correct names and distances in the working version.) In B3 enter the "From" - say Three - and in C3 enter the "To" - say Five now enter the formula: =VLOOKUP(B3,G1:L6,MATCH(C3,G1:G6,FALSE)+1,FALSE) The formula will look down the list in G2:G6 until it finds a match with B3, then along that row the number of columns returned by the MATCH function plus 1 (because VLOOKUP is 1 based not zero based), and return the milage that it find in that cell. The two FALSE argumets are to make the functions find exact matched only otherwise it could give wrong results. That looks fine as long as everyting is in agreement but if B3 or C3 are empty or contain anything except correct data then the formula will return #N/A. To get around this wrap if in an IF statement: =IF(ISNA(VLOOKUP(B3,G1:L6,MATCH(C3,G1:G6,FALSE)+1, FALSE)),"Wrong Data Given",VLOOKUP(B3,G1:L6,MATCH(C3,G1:G6,FALSE)+1,FA LSE)) Of course that will now give "Wrong Data Given" when B3 and/or C3 are empty which you may not want. You could change "Wrong Data Given" to smply "" which would return an *enpty" cell but then it would do that for incorrect destination/starting points as well. You could solve both probelms with another IF as in: =IF(OR(B3="",C3=""),"",IF(ISNA(VLOOKUP(B3,G1:L6,MA TCH(C3,G1:G6,FALSE)+1,FALSE)),"Wrong data given",VLOOKUP(B3,G1:L6,MATCH(C3,G1:G6,FALSE)+1,FA LSE))) HTH Sandy wrote in message ... I am trying to wrap my mind around this-- it makes more sense everytime I read it. I am still having some issues understanding how I could change the "From" with this method and still have it reflect the correct mileage. Would I need to make a VLOOKUP table for every possible combination of mileage? |
|
#10
|
|||
|
|||
|
Mmmmm........
I must have changed something between developing the formula and posting it because as posted it does not require the " +1 " in the match so use: =IF(OR(B3="",C3=""),"",IF(ISNA(VLOOKUP(B3,G1:L6,MA TCH(C3,G1:G6,FALSE),FALSE)),"Wrong data given",VLOOKUP(B3,G1:L6,MATCH(C3,G1:G6,FALSE),FALS E))) -- HTH Sandy Replace@mailinator with @tiscali.co.uk "Sandy Mann" wrote in message ... ..... I am still having some issues understanding how I could change the "From" with this method and still have it reflect the correct mileage. Sorry, perhaps it was my fault for not realising what you wanted but your original post specified only "Admin" as the "From" with the other name as destinations I assumed that you only wanted to lookup distances in one direction. If you construct a table as below: G H I J K L 1 One Two Three Four Five 2 One 0 10 15 20 25 3 Two 10 0 7 12 19 4 Three 15 7 0 8 10 5 Four 20 12 8 0 9 6 Five 25 19 10 9 0 (But of course use your own correct names and distances in the working version.) In B3 enter the "From" - say Three - and in C3 enter the "To" - say Five now enter the formula: =VLOOKUP(B3,G1:L6,MATCH(C3,G1:G6,FALSE)+1,FALSE) The formula will look down the list in G2:G6 until it finds a match with B3, then along that row the number of columns returned by the MATCH function plus 1 (because VLOOKUP is 1 based not zero based), and return the milage that it find in that cell. The two FALSE argumets are to make the functions find exact matched only otherwise it could give wrong results. That looks fine as long as everyting is in agreement but if B3 or C3 are empty or contain anything except correct data then the formula will return #N/A. To get around this wrap if in an IF statement: =IF(ISNA(VLOOKUP(B3,G1:L6,MATCH(C3,G1:G6,FALSE)+1, FALSE)),"Wrong Data Given",VLOOKUP(B3,G1:L6,MATCH(C3,G1:G6,FALSE)+1,FA LSE)) Of course that will now give "Wrong Data Given" when B3 and/or C3 are empty which you may not want. You could change "Wrong Data Given" to smply "" which would return an *enpty" cell but then it would do that for incorrect destination/starting points as well. You could solve both probelms with another IF as in: =IF(OR(B3="",C3=""),"",IF(ISNA(VLOOKUP(B3,G1:L6,MA TCH(C3,G1:G6,FALSE)+1,FALSE)),"Wrong data given",VLOOKUP(B3,G1:L6,MATCH(C3,G1:G6,FALSE)+1,FA LSE))) HTH Sandy wrote in message ... I am trying to wrap my mind around this-- it makes more sense everytime I read it. I am still having some issues understanding how I could change the "From" with this method and still have it reflect the correct mileage. Would I need to make a VLOOKUP table for every possible combination of mileage? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Max limit of 7 nested loops | Excel Worksheet Functions | |||
| limit of 7 nested functions? | Excel Worksheet Functions | |||
| how can I exceed the nested if fuction limit | Excel Discussion (Misc queries) | |||
| how can I exceed the nested if fuction limit | Excel Discussion (Misc queries) | |||
| Nested IF limit or Open parentheses limit | Excel Discussion (Misc queries) | |||
Linear Mode
