time measures and vlookup
Posted by brian o'farrell on December 14, 2000 2:34 PM
I am developing a spreadsheet for an athletics club that enables them to easily determine the points relevant to a particular performance. The times are measured to 100ths of a second and may involve minutes (or even hours for the adult events). I have arranged the data in two columns - TIMES and POINTS - and proposed to use a vlookup based upon TIME. The times are entered as mm:ss.ss format e.g. 2:40.35. Is there an easy way to achieve this without converting the data to seconds for the lookup?
thanks
Brian
Melbourne, Australia
Posted by Tim Francis-Wright on December 14, 2000 9:12 PM
You should be able to do this; here's a toy example:
A1: 0:00.00
A2: 0:30.00
A3: 0:35.00
A4: 0:42.00
A5: 59:59.99
B1: 100
B2: 80
B3: 60
B4: 0
B5: 0
(In each case, Bn is the number of points
for not going over A[n+1] in terms of time.)
As long as you format the cells correctly,
excel will treat each one as a number. To do
the VLOOKUP without requiring exact matches,
Excel needs the times in ascending order.
So, if C1 = 0:38, then
C2 = Vlookup(C1,$a$5:$b$5,2,TRUE) = 60
Hope this helps!
