Get Past the 7 Nested IF limit

[Tech_in_the_woods]

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(AND(B3="ADMIN",C3="LOR"),0.8,IF(AND(B3="ADMIN",C3="ABR"),10,IF(AND(B3="ADMIN",C3="ARA"),1,IF(AND(B3="ADMIN",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.

 

[Don Guillett]

see if this idea helps
=IF(AND(B3="a",OR(C3="b",C3="c",C3="d")),1,2)

 

[Tech_in_the_woods]

Can you explain to me what this is doing? I can be very dense.

 

[Tech_in_the_woods]

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.

 

[Martin P]

I would prefer the following, which gives the same result
=IF(AND(B3="ADMIN",C3="FHS"),3.5,0)+IF(AND(B3="ADMIN",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)

 

[Sandy Mann]

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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Tech_in_the_woods]

This was great and worked to a point, the problem was that the formula became
too long, so I can't finish it out.

 

[Tech_in_the_woods]

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]

..... 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,FALSE))

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,MATCH(C3,G1:G6,FALSE)+1,FALSE)),"Wrong
data given",VLOOKUP(B3,G1:L6,MATCH(C3,G1:G6,FALSE)+1,FALSE)))

HTH

Sandy



(e-mail address removed)> wrote in
message news:[email protected]...

 

[Sandy Mann]

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,MATCH(C3,G1:G6,FALSE),FALSE)),"Wrong
data given",VLOOKUP(B3,G1:L6,MATCH(C3,G1:G6,FALSE),FALSE)))


--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk