Can this be done??

[drum118]

Not sure if this can be done or what way to do it.


I want a time in a cell to lookup a time that comes close to match it
and enter that matchtime in another cell.

So what I have is:

Stop #() Schedule Time
12:23:43 ?

In another book I have the times for that stop listed:
12:00:00
12:15:00
12:22:00
12:28:00

Therefore, I want 12:22:00 to be enter into the schedule time cols as it
is the closes match.

There will be a number of these schedule time in each workbook and the
times are in another book by themselves.

Can this be done and if so how to do it?

Thanks

 

[Mike]

=VLOOKUP(A2,[drum000.xls]Sheet1!$A$1:$A$4,1)

 

[Ron Rosenfeld]

=VLOOKUP(A2,[drum000.xls]Sheet1!$A$1:$A$4,1)

Your formula does not give the "closest match".

For example, if the Stop Time is 12:21:00, your formula will give 12:15:00
where the closest match would be 12:22:00


--ron

 

[Ron Rosenfeld]

I want a time in a cell to lookup a time that comes close to match it
and enter that matchtime in another cell.

If the list of "matchtime" is named "TimeTable" and the Time under Stop# is
named StopTime, then the *array-entered* formula:


=INDEX(TimeTable,MATCH(MIN(ABS(StopTime-TimeTable)),ABS(StopTime-TimeTable),0),0)

will give the closest time. If StopTime is exactly 1/2 way between the two
times, then the formula will give the earlier time.

To *array-enter*, after typing or pasting in the formula, hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula.


--ron

 

[drum118]

If the list of "matchtime" is named "TimeTable" and the Time under Stop# is
named StopTime, then the *array-entered* formula:

=INDEX(TimeTable,MATCH(MIN(ABS(StopTime-TimeTable)),ABS(StopTime-TimeTable),0),0)

At present time I have name the cols as either MyTimeCols,MyTimeCols2,
MyTimeCols3,MyTimeCols4 for another formula. I will have to use
different names for Stop# cols as they will have different number in the
first place. Also Timetable is in another book and will have listing for
those Stop#.

It will look like this:
Book Route #20
Row 1 Col B Col D Col BB Col BE
Row 6 (Stop #1698)(Shedule Time)(Stop #1770)(Shedule Time)
Row 7 12:12:33 (formular)=12:15:00 13:33:55(formular)=13:30:00

Book Route #20 Bus Stop Schedule
Row 1 Col B Col D Col F Col K
Row 5 (Stop #1698) (Stop #1770) (Stop #1987)(Stop #3366)

Therefore in Book Route #20 Bus Stop Schedule I can name each col
TimeTable(Stop #). In Book Route #20 I can name the col StopTime(Stop #)
what will match your formula. What I am not sure is how to link the two
books for TimeTable? Also not sure about the 2 set of ABS().

So it should look like is:
=INDEX(TimeTable,MATCH(MIN(ABS(StopTime1698-TimeTable1698)),ABS(StopTime1698-TimeTable1698),0),0)
Not sure if first TimeTable needs a number?
Second schedule would be:
=INDEX(TimeTable,MATCH(MIN(ABS(StopTime1770-TimeTable1770)),ABS(StopTime1770-TimeTable1770),0),0)

will give the closest time. If StopTime is exactly 1/2 way between the two
times, then the formula will give the earlier time.

forgot about that.

To *array-enter*, after typing or pasting in the formula, hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula.

Understand that.

--ron

I lot similar that I thought it would be

Thanks

 

[Ron Rosenfeld]

In Book Route #20 I can name the col StopTime(Stop #)
what will match your formula. What I am not sure is how to link the two
books for TimeTable? Also not sure about the 2 set of ABS().

Well, the names I used were only for convenience. You can use whatever kind of
convention you wish here.

I might reference StopTime as a cell reference, since it's right next to the
Scheduled Time.

So far as the TimeTables are concerned being in different workbooks, just look
up that kind of referencing in HELP. Excel will construct a reference if you
select a cell, type "=", and then select a range in a different workbook.

Since TimeTable's are in different workbook, if you use INDIRECT to construct
the reference, that workbook must be open.

The best method of "linking" really depends on your setup. But it seems you
should be able to figure that out. I would probably have something in the
first row that I could use as a pointer to the relevant TimeTable, and
reference that in my formula for each Route/Stop.

The ABS() function is there because you wanted the "closest" time. (StopTime -
TimeTable) will sometimes return a positive number, and sometimes a negative
number depending on which is earlier. The closest time will be the smallest
number, without regard to sign.


--ron