IF first instance greater than . . . ??

[Ed]

One of our vehicles did a speed trial. Time was taken about every .03
seconds, and is in Col A. Col B has the speed for that time. I want to
find how long it took the vehicle to reach certain speeds. I'm thinking a
formula down Col C that says "IF this is the first instance greater than
'TargetSpeed', then subtract the time in Col A from the stat time". I don't
know how to get the "first instance greater than". Or is there a better way
I'm not thinking of?

Ed

 

[Guest]

Hi

In C2, try something like this:
=IF(B2>'targetvalue',A2-'starttime',"")
and then fill this down the column.
You could have your targetvalue and starttime in other cells and reference
them in the formula instead.

Andy.

 

[Niek Otten]

With the time you need in C1:

=INDEX(A1:A6,MATCH(C1,B1:B6,1))-A1

Format as time.

 

[Ed]

Hi, Neik. Thanks for the reply. The problem I'm running into is that I
don't have an exact match. Speeds were not obtained rounded off to the
nearest whole number - they actually go out for 5 and six decimals. That's
why I was looking for the first time the cell data became greater than the
target. Although that actually sounds more like a Boolean in VBA - maybe
I'm looking more for a macro solution.

As you can tell, I'm not real used to using Excel like this, so I'm not sure
what my options are. Your help is greatly appreciated.
Ed

 

[Niek Otten]

Hi Ed,

=INDEX(A1:A6,MATCH(C1,B1:B6,1)+IF(VLOOKUP(C1,B1:B6,1)=C1,0,1))

 

[Niek Otten]

I forgot the "-A1" at the end

 

[Ed]

Wow! Thanks, Niek. I really appreciate the help.
Ed