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

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?

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

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


"Steve" wrote:

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?

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

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

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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?

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

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

"Mike H" wrote:

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


"Steve" wrote:

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?

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

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Steve" wrote:

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?

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

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" wrote:

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

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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?

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

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.

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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?