Excel Help

[jassa]

Hi All,

Hopefully i wont confuse this matter.

I have for example a bus timetable.

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
</tbody>[/TABLE]

The top row and left column are the bus stops. The double and triple numbers are how long it takes for the bus to make it across as a maximum so should always be within this time.

Is their a formulae i can use to find out if i have the time the bus leave and arrives using the terminals as a reference to how long it should take and if it made it within that time.

Any help would be helpful.

Many thanks.

 

[wideboydixon]

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H
1		2	3	4	5		Depart Terminal	5
2	2	60	70	80	90		Depart Time	17:00
3	3	70	80	90	90		Arrive Terminal	5
4	4	80	90	90	100		Arrive Time	18:30
5	5	90	100	110	120		Target Met?	Yes
Sheet1

Cell Formulas
Range	Formula
H5	=IF($H$4<=($H$2+TIME(0,INDEX($A$1:$E$5,MATCH($H$1,$A$1:$A$5,0),MATCH($H$3,$A$1:$E$1,0)),0)),"Yes","No")

WBD

 

[wideboydixon]

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H
1		2	3	4	5		Depart Terminal	5
2	2	60	70	80	90		Depart Time	17:00
3	3	70	80	90	90		Arrive Terminal	5
4	4	80	90	90	100		Arrive Time	18:30
5	5	90	100	110	120		Target Met?	Yes
Sheet1

Cell Formulas
Range	Formula
H5	=IF($H$4<=($H$2+TIME(0,INDEX($A$1:$E$5,MATCH($H$1,$A$1:$A$5,0),MATCH($H$3,$A$1:$E$1,0)),0)),"Yes","No")

WBD

 

[jassa]

Hi Dixon,

This worked perfectly. Many Thanks

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H
Depart Terminal
Depart Time
Arrive Terminal
Arrive Time
Target Met?	Yes

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"][/TD]

[TD="align: right"]17:00[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: right"]18:30[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H5[/TH]
[TD="align: left"]=IF($H$4<=($H$2+TIME(0,INDEX($A$1:$E$5,MATCH($H$1,$A$1:$A$5,0),MATCH($H$3,$A$1:$E$1,0)),0)),"Yes","No")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



WBD

 

[RoryA]

@jassa,

In the future, please at least make an attempt at a meaningful title. "Excel help" covers every question in this section of the forum!