More vlookup questions (I think?)(kind of long)

[JaimeZX]

Basically my question has to do with two cars. Car 1's speed is determined in
a cell, say "C1" and can be anywhere from 1-100mph. Car 2's speed is in "D1"
and can also be from 1-100mph.

So I have three columns which are Time, and the positions for Car 1 and Car
2 in feet.

IE
Car1=50 Car2=70
0.0 | 0 | 0
0.1 | 7.3 | 10.2
0.2 | 14.6 | 20.4

etc.

So I want to figure out when car 2 would rear-end car 1. In this case it's
fairly easy, because 50mph is approx 73fps and 70mph is approx 102 fps, so
car 2 is gaining on car 1 by approx 30fps... etc. (Also in this case where
they depart simultaniously, car2 would never hit car1...)

The easy way to do this is to just graph the three columns and see where the
lines intersect.

HOWEVER, if I want to delay car 2 by X seconds, then I have to re-do the
graph by adding zeroes to the start of column C for the approprite amount of
time, right? Also, if I change the speed of car1 or car2 then I have to come
up with a whole new series of graphs.

I'm just trying to figure out if there is some formula I can generate that
would (a) automatically insert (or assume) leading zeroes and/or (b)
determine when car2 will rear-end car1 given an arbitrary speed of each.

Preferrably I'd like to just be able to have speeds for car1 and car2 in two
cells and have the output of the formula be "car2 will hit car1 after X
seconds." (Text not necessary, just "X.")

Thanks in advance for any thoughts y'all might have on this. At first I
thought it was going to be easy but it seems more complex than my original
idea. I figured I could use vlookup except that *what number am I looking
for?* I'm looking for the number where the lines cross... but I don't know
what that is except to look at the graph...

Again, thanks!

 

[Luke M]

No need for a graph, this is an algebraic equation. Assumptions: A2=Car 1's
speed (mph), B2=Car 2's speed (mph), C2 = delay (if any, in seconds)

=(A2*3600*C2)/(B2*3600-A2*3600)

 

[JaimeZX]

Luke, first of all, that's terrific! Thank you!

Second, it doesn't unfortunately answer my EXACT question because I tried to
present an over-simplified case for posting purposes. I guess being imprecise
led to an imperfect answer.

In my actual case, I have a column that is position vs time for car2,
because car2 is undergoing non-linear acceleration. Car1's velocity is
constant and in your post would be A1. Hence the table. Car1's position on
the graph is a straight line, whereas Car2's position looks more like an
elongated S, or actually like a slanted integral sign. So really I have the
linear part for car1 which is simple, but the tough part is car2.

Again, your thoughts are most welcome, sir.

Thanks again!

Jim

 

[JaimeZX]

I dunno if links work in here, but here are three images that show how the
position of car2 will vary depending on the delay. (Sorry, it's a quick
MSPaint job.)

In this first one, car2 will hit car1.
http://www.moarpics.com/images/o6hh1jf94zrxoxuzyd17.jpg

In this second one, it won't.
http://www.moarpics.com/images/5er7ob56l1r65whngvn5.jpg

In the third one, it will, just barely.
http://www.moarpics.com/images/rm0gpha80zyi3n8s3gx.jpg

My goal is to figure out based on an arbitrary speed of car1 and starting
delay for car2, will car2 hit car1 or not?

This is turning out to be more difficult than I had originally anticipated.