How to use a range of time to assign a score?

[Steve]

I have a range of times for a 2 mile run and a and a score for each time
group but do not know how i can include this in excel. For example if a
runner finishes his run in 14 min 10 sec (14:10) and my scale is from 14:00
to 14:30 is a 90 how do I go about doing this?

 

[Mike H]

Steve,

Somewhere out of the way build yourself a table like the one below with time
thresholds and points. Note that ive include an impossible time of zero
because you need that but you can put realistic thresholds in, use the formula

=VLOOKUP(A1,G1:H8,2,TRUE)

Where G1 - H8 refers to the table

00:00 150
14:00 120
14:10 100
14:20 80
14:30 60
15:30 40
16:30 20
17:30 0

Mike

 

[T. Valko]

How many time groups are there? Tell us what all the groups are.

 

[Mike H]

i forgot to mention the first column of the table must remain sorted ascending

 

[Luke M]

Setup a 2 column table, with left column containing upper limits of preceding
line's grouping. So, if 0<=X<11 gives score of 100:

Time....Score
0............100
11............90
21............80
31............70
etc

then use a lookup formula
=LOOKUP(22,A2:B10)

In this scenario, a score of 80 is given to range 21-30, so formula returns
a 80.

 

[Steve]

This is my time scale and the grade that is represented in each range.

DC Time Scale
59:00-60:00 70
58:00-58:59 72
57:00-57:59 74
56:00-56:59 76
55:00-55:59 78
54:00-54:59 80
53:00-53:59 82
52:00-52:59 84
51:00-51:59 86
50:00-50:59 88
49:00-49:59 90
48:00-48:59 92
47:00-47:59 94
46:00-46:59 96
45:00-45:59 98
44:00-44:59 100

 

[T. Valko]

If you flip your table upside down and just use the lower time for each
interval:

...........E..........F
1....44:00.....100
2....45:00.....98
3....46:00.....96
4....47:00.....94
5....48:00.....92

A1 = 0:46:17

=LOOKUP(A1,E1:F5)

Returns 96

Note that any time in A1 <44:00 will return #N/A. Any time in A1 >48:00 will
return 92.