Race category formula

jezzery

New Member
Previous to this Alphafrog kindly put together the formulas below to work out whether participants received a gold, silver or bronze according to their times of 2 different distances. I now have a race that includes 3 distances and cannot work out how to include another race category into the formula that works out their award based on what time they have achieved for the distance. The data for the race is as follows if anyone can help and the previous data and formula is below.

The Standard Times for 110 miles are:

Age Group 18 to 39
= 6:10 Gold, 6:30 Silver, 6:50 Bronze.
Age Group 40 to 49 = 6:25 Gold, 6:40 Silver, 7:00 Bronze.
Age Group 50 to 59 = 6:35 Gold, 6:50 Silver, 7:10 Bronze.
Age Group 60+ = 6:50 Gold, 7:10 Silver, 7:30 Bronze.

The Standard Times for 72 miles are:
Age Group 18 to 39 = 4:20 Gold, 4:40 Silver, 5:00 Bronze.
Age Group 40 to 49 = 4:35 Gold, 4:50 Silver, 5:10 Bronze.
Age Group 50 to 59 = 4:45 Gold, 5:00 Silver, 5:20 Bronze.
Age Group 60+ = 5:00 Gold, 5:20 Silver, 5:40 Bronze.

The Standard Times for 42 miles are:
Age Group 18 to 39 = 2:20 Gold, 2:40 Silver, 3:05 Bronze
Age Group 40 to 49 = 2:30 Gold, 2:50 Silver, 3:15 Bronze
Age Group 50 to 59 = 2:40 Gold, 3:05 Silver, 3:30 Bronze
Age Group 60+ = 3:00 Gold, 3:25 Silver, 3:50 Bronze.

 * A B C D E F G H I J K L M N O P Q 1 Name Age Sex Route Time Award * 80 Mile * * * * * 50 Mile * * * 2 Jim 18 M 80 5:00 Silver * Age Gold Silver Bronze --- * Gold Silver Bronze --- 3 Jane 33 F 80 6:00 Bronze * 18 0 4:50 5:10 5:40 * 0 2:50 3:10 3:25 4 John 44 M 50 3:00 Silver * 40 0 5:05 5:20 5:50 * 0 3:00 3:20 3:45 5 June 55 F 50 3:29 Gold * 50 0 5:15 5:35 6:05 * 0 3:10 3:35 4:00 6 Joan 66 F 50 4:54 --- * 60 0 5:35 6:00 6:25 * 0 3:30 3:55 4:20 7 * * * * * * * *Ladies add 20 mins to each category * * * * * * * * *

<tbody>
</tbody>

 Cell Formula F2 =INDEX(\$I\$2:\$L\$2,MATCH(E2-IF(UPPER(C2)="F",TIMEVALUE("00:20:00"),0),INDEX(IF(D2=80,\$I\$3:\$L\$6,\$N\$3:\$Q\$6),MATCH(B2,\$H\$3:\$H\$6,1),0),1)) F3 =INDEX(\$I\$2:\$L\$2,MATCH(E3-IF(UPPER(C3)="F",TIMEVALUE("00:20:00"),0),INDEX(IF(D3=80,\$I\$3:\$L\$6,\$N\$3:\$Q\$6),MATCH(B3,\$H\$3:\$H\$6,1),0),1)) F4 =INDEX(\$I\$2:\$L\$2,MATCH(E4-IF(UPPER(C4)="F",TIMEVALUE("00:20:00"),0),INDEX(IF(D4=80,\$I\$3:\$L\$6,\$N\$3:\$Q\$6),MATCH(B4,\$H\$3:\$H\$6,1),0),1)) F5 =INDEX(\$I\$2:\$L\$2,MATCH(E5-IF(UPPER(C5)="F",TIMEVALUE("00:20:00"),0),INDEX(IF(D5=80,\$I\$3:\$L\$6,\$N\$3:\$Q\$6),MATCH(B5,\$H\$3:\$H\$6,1),0),1)) F6 =INDEX(\$I\$2:\$L\$2,MATCH(E6-IF(UPPER(C6)="F",TIMEVALUE("00:20:00"),0),INDEX(IF(D6=80,\$I\$3:\$L\$6,\$N\$3:\$Q\$6),MATCH(B6,\$H\$3:\$H\$6,1),0),1))

<tbody>
</tbody>

<tbody>
</tbody>

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Replies
4
Views
344
Replies
7
Views
397
Replies
3
Views
444
Replies
0
Views
623
Replies
1
Views
2K

1,195,581
Messages
6,010,576
Members
441,557
Latest member
Jbest23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back