Formula Too Long

[saffo5]

Hi,

I am quite new on Excel and I am trying to create a Formula which is some kind of a database. I want to create an autofill of time between 2 locations that I will pre-set... In the first line for ex. IST is city code and BKK is city code, if I insert these 2 city codes the value of 2 hours and 10 minutes will automatically be filled.
My formula contains around 300 lines such as below, and when I finally tried to insert it, it says that formula is too long.

Do you have any idea to achieve this or bypass the formula is too long problem.

Many thanks.

=IF(AND(C2="IST",E2="BKK"),TIME(2,10,0),
IF(AND(C2="BKK",E2="IST"),TIME(5,35,0),
IF(AND(C2="FRA",E2="AYT"),TIME(3,35,0),
IF(AND(C2="MNS",E2="FRA"),TIME(2,40,0),
IF(AND(C2="BRU",E2="PAR"),TIME(2,30,0),
IF(AND(C2="JFK",E2="DCD"),TIME(3,45,0),
IF(AND(C2="SXD",E2="DKI"),TIME(3,25,0),
IF(AND(C2="ERD",E2="FDD"),TIME(2,35,0)

 

[Tom.Jones]

Hi,

I do not know your data structure but ...
In columns A and B put the names of the airports and in column C put the related times.

In F1 put a name, from column A, in G1 put a name, from column B (in the same row as the one from column A), and in
H1 put the formula:
= SUMPRODUCT ((A1: A8 = F1) * (B1: B8 = G1) * (C1: C8)), obviously increases the range of data as needed (A1: A300, B1: B300 ...)

 

[sykes]

Hi, and welcome to Mr. Excel!
Likewise, I wouldn't try to use a huge formula for this; I was thinking of making a table on a separate worksheet, with the city codes along the top row, and down the first column, then populating all the associated journey times at each intersection. It'll take a but of effort, but no more than typing out your Loooong formula!
Then, on your main worksheet where you want the result to appear, have two cells - one for each city, and a formula in a 3rd cell to find the intersecting journey time.

 

[sykes]

I was just playing around with this.
I started a new sheet, and put this mini table into A1:G7:

CCT BBA TYR YTP GVW ABQ
CCT - 65 12 3 5 4
BBA 800 - 24 31 0 5
TYR 1 2 - 4 5 6
YTP 25 2698 4523 - 125 389
GVW 3 213 5 658 - 8
ABQ 4 78 6 66 200 -

In K7:L8 I put this (where L7 holds a data validation list populated from B1:G1, and similarly in L8 - populated from A2:A7):
City BBA
City YTP

Then in a "Result" cell, formula:
=INDIRECT(ADDRESS(MATCH(L8,A2:A10,0)+1,MATCH(L7,B1:G1,0)+1))

Works well!
The data validation list just enables a drop-down list of allowed values, but isn't necessary.

 

[Fluff]

Another option without volatile functions.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		IST	BKK	FRA	AYT	MNS	BRU	PAR	JFK	DCD	SXD	DKI	ERD	FDD		BKK
2	IST		05:35	06:35	07:35	08:35	09:35	10:35	11:35	12:35	13:35	14:35	15:35	16:35		IST	05:35:00
3	BKK	02:10		07:35	08:35	09:35	10:35	11:35	12:35	13:35	14:35	15:35	16:35	17:35
4	FRA	03:10	07:35		09:35	10:35	11:35	12:35	13:35	14:35	15:35	16:35	17:35	18:35
5	AYT	04:10	08:35	09:35		11:35	12:35	13:35	14:35	15:35	16:35	17:35	18:35	19:35
6	MNS	05:10	09:35	10:35	11:35		13:35	14:35	15:35	16:35	17:35	18:35	19:35	20:35
7	BRU	06:10	10:35	11:35	12:35	13:35		15:35	16:35	17:35	18:35	19:35	20:35	21:35
8	PAR	07:10	11:35	12:35	13:35	14:35	15:35		17:35	18:35	19:35	20:35	21:35	22:35
9	JFK	08:10	12:35	13:35	14:35	15:35	16:35	17:35		19:35	20:35	21:35	22:35	23:35
10	DCD	09:10	13:35	14:35	15:35	16:35	17:35	18:35	19:35		21:35	22:35	23:35	00:35
11	SXD	10:10	14:35	15:35	16:35	17:35	18:35	19:35	20:35	21:35		23:35	00:35	01:35
12	DKI	11:10	15:35	16:35	17:35	18:35	19:35	20:35	21:35	22:35	23:35		01:35	02:35
13	ERD	12:10	16:35	17:35	18:35	19:35	20:35	21:35	22:35	23:35	00:35	01:35		03:35
14	FDD	13:10	17:35	18:35	19:35	20:35	21:35	22:35	23:35	00:35	01:35	02:35	03:35
Log

Cell Formulas
Range	Formula
Q2	=INDEX(B2:N14,MATCH(P2,A2:A14,0),MATCH(P1,B1:N1,0))

 

[sykes]

Having (finally) just got 'round to trying out the HTML maker, I thought I'd put it to its first bit of good use, so here's a better representation of my solution:

Book1
	A	B	C	D	E	F	G
1		CCT	BBA	TYR	YTP	GVW	ABQ
2	CCT	-	01:53	02:48	00:52	02:54	00:17
3	BBA	01:53	-	03:02	01:01	02:58	01:49
4	TYR	02:48	03:02	-	02:20	03:02	01:15
5	YTP	00:52	01:01	02:20	-	02:02	00:38
6	GVW	02:54	02:58	03:02	02:02	-	02:25
7	ABQ	00:17	01:49	01:15	00:38	02:25	-
8
9		From:	GVW
10		To:	TYR
11		Result:	3:02
Sheet2

Cell Formulas
Range	Formula
C11	=INDIRECT(ADDRESS(MATCH(C10,A2:A10,0)+1,MATCH(C9,B1:G1,0)+1))

 

[sykes]

@saffo5
Did any of these suggestions help you?